database_postgres.py

"""
🗄️ DATABASE MODULE - PostgreSQL Support
Extended version of database.py with PostgreSQL support for production deployment
"""

import os
import uuid
from datetime import datetime
from typing import Dict, List, Optional, Tuple
import json

# =============================================================================
# DATABASE CONFIGURATION
# =============================================================================

# Get database URL from environment
DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///carpet_analysis.db")

# Determine database type
if DATABASE_URL.startswith("postgresql"):
    DATABASE_TYPE = "postgresql"
    import psycopg2
    from psycopg2.extras import RealDictCursor
    print(f"🗄️  Using PostgreSQL database")
    SCHEMA_PATH = "database_schema_postgres.sql"  # Use PostgreSQL schema
elif DATABASE_URL.startswith("sqlite"):
    DATABASE_TYPE = "sqlite"
    import sqlite3
    DATABASE_PATH = DATABASE_URL.replace("sqlite:///", "")
    print(f"🗄️  Using SQLite database: {DATABASE_PATH}")
    SCHEMA_PATH = "database_schema.sql"  # Use SQLite schema
else:
    # Default to SQLite
    DATABASE_TYPE = "sqlite"
    import sqlite3
    DATABASE_PATH = "carpet_analysis.db"
    print(f"🗄️  Using SQLite database (default): {DATABASE_PATH}")
    SCHEMA_PATH = "database_schema.sql"  # Use SQLite schema

# =============================================================================
# CONNECTION HELPERS
# =============================================================================

def get_connection():
    """Get database connection based on type"""
    if DATABASE_TYPE == "postgresql":
        return psycopg2.connect(DATABASE_URL)
    else:
        return sqlite3.connect(DATABASE_PATH)

def get_cursor(conn):
    """Get cursor with appropriate row factory"""
    if DATABASE_TYPE == "postgresql":
        return conn.cursor(cursor_factory=RealDictCursor)
    else:
        conn.row_factory = sqlite3.Row
        return conn.cursor()

# =============================================================================
# DATABASE INITIALIZATION
# =============================================================================

def init_database():
    """Initialize database dengan schema dari SQL file"""
    print("🗄️  Initializing database...")
    
    conn = get_connection()
    cursor = get_cursor(conn)
    
    # Load dan execute schema
    if os.path.exists(SCHEMA_PATH):
        with open(SCHEMA_PATH, 'r', encoding='utf-8') as f:
            schema = f.read()
            
            if DATABASE_TYPE == "postgresql":
                cursor.execute(schema)
            else:
                cursor.executescript(schema)
        
        print(f"✅ Database schema loaded from {SCHEMA_PATH}")
    else:
        print(f"⚠️  Schema file not found: {SCHEMA_PATH}")
        print("   Creating basic schema...")
        create_basic_schema(cursor)
    
    conn.commit()
    conn.close()
    print(f"✅ Database initialized: {DATABASE_TYPE}")


def create_basic_schema(cursor):
    """Create basic schema jika SQL file tidak ada"""
    if DATABASE_TYPE == "postgresql":
        # PostgreSQL version
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS analysis_history (
                id SERIAL PRIMARY KEY,
                analysis_id TEXT UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                material TEXT NOT NULL,
                material_confidence REAL NOT NULL,
                carpet_type TEXT NOT NULL,
                type_confidence REAL NOT NULL,
                carpet_coverage REAL NOT NULL,
                is_authentic BOOLEAN NOT NULL,
                confidence_level TEXT,
                price_min INTEGER,
                price_max INTEGER,
                total_processing_time REAL NOT NULL
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS carpet_products (
                id SERIAL PRIMARY KEY,
                product_name TEXT UNIQUE NOT NULL,
                carpet_type TEXT NOT NULL,
                material TEXT NOT NULL,
                price_min INTEGER NOT NULL,
                price_max INTEGER NOT NULL,
                stock_status TEXT DEFAULT 'available'
            )
        """)
    else:
        # SQLite version (original)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS analysis_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                analysis_id TEXT UNIQUE NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                material TEXT NOT NULL,
                material_confidence REAL NOT NULL,
                carpet_type TEXT NOT NULL,
                type_confidence REAL NOT NULL,
                carpet_coverage REAL NOT NULL,
                is_authentic BOOLEAN NOT NULL,
                confidence_level TEXT,
                price_min INTEGER,
                price_max INTEGER,
                total_processing_time REAL NOT NULL
            )
        """)
        
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS carpet_products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                product_name TEXT UNIQUE NOT NULL,
                carpet_type TEXT NOT NULL,
                material TEXT NOT NULL,
                price_min INTEGER NOT NULL,
                price_max INTEGER NOT NULL,
                stock_status TEXT DEFAULT 'available'
            )
        """)

# =============================================================================
# PRODUCT OPERATIONS
# =============================================================================

def get_all_products() -> List[Dict]:
    """Get semua produk karpet dari database"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    cursor.execute("""
        SELECT * FROM carpet_products 
        WHERE stock_status = 'available'
        ORDER BY carpet_type
    """)
    
    products = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return products


def get_product_by_combination(material: str, carpet_type: str) -> Optional[Dict]:
    """Get produk berdasarkan kombinasi material + type"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    if DATABASE_TYPE == "postgresql":
        cursor.execute("""
            SELECT * FROM carpet_products 
            WHERE material = %s AND carpet_type = %s
            AND stock_status = 'available'
        """, (material, carpet_type))
    else:
        cursor.execute("""
            SELECT * FROM carpet_products 
            WHERE material = ? AND carpet_type = ?
            AND stock_status = 'available'
        """, (material, carpet_type))
    
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None


def get_price_range(material: str, carpet_type: str) -> Optional[Tuple[int, int]]:
    """Get price range untuk kombinasi material + type"""
    product = get_product_by_combination(material, carpet_type)
    if product:
        return (product['price_min'], product['price_max'])
    return None

# =============================================================================
# ANALYSIS HISTORY OPERATIONS
# =============================================================================

def save_analysis_result(result: Dict) -> str:
    """
    Simpan hasil analisis ke database
    
    Returns:
        analysis_id: UUID untuk tracking
    """
    conn = get_connection()
    cursor = get_cursor(conn)
    
    analysis_id = str(uuid.uuid4())
    
    # Prepare query based on database type
    if DATABASE_TYPE == "postgresql":
        cursor.execute("""
            INSERT INTO analysis_history (
                analysis_id,
                mikro_image_path,
                makro_image_path,
                material,
                material_confidence,
                material_processing_time,
                carpet_coverage,
                segmentation_processing_time,
                carpet_type,
                type_confidence,
                type_processing_time,
                is_authentic,
                confidence_level,
                warning,
                reason,
                price_min,
                price_max,
                price_note,
                total_processing_time
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            analysis_id,
            result.get('mikro_image_path'),
            result.get('makro_image_path'),
            result['material'],
            result['material_confidence'],
            result.get('material_processing_time'),
            result['carpet_coverage'],
            result.get('segmentation_processing_time'),
            result['carpet_type'],
            result['type_confidence'],
            result.get('type_processing_time'),
            result['is_authentic'],
            result.get('confidence_level'),
            result.get('warning'),
            result.get('reason'),
            result.get('price_min'),
            result.get('price_max'),
            result.get('price_note'),
            result['total_processing_time']
        ))
    else:
        cursor.execute("""
            INSERT INTO analysis_history (
                analysis_id,
                mikro_image_path,
                makro_image_path,
                material,
                material_confidence,
                material_processing_time,
                carpet_coverage,
                segmentation_processing_time,
                carpet_type,
                type_confidence,
                type_processing_time,
                is_authentic,
                confidence_level,
                warning,
                reason,
                price_min,
                price_max,
                price_note,
                total_processing_time
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            analysis_id,
            result.get('mikro_image_path'),
            result.get('makro_image_path'),
            result['material'],
            result['material_confidence'],
            result.get('material_processing_time'),
            result['carpet_coverage'],
            result.get('segmentation_processing_time'),
            result['carpet_type'],
            result['type_confidence'],
            result.get('type_processing_time'),
            result['is_authentic'],
            result.get('confidence_level'),
            result.get('warning'),
            result.get('reason'),
            result.get('price_min'),
            result.get('price_max'),
            result.get('price_note'),
            result['total_processing_time']
        ))
    
    conn.commit()
    conn.close()
    
    print(f"💾 Analysis saved to database: {analysis_id}")
    return analysis_id


def get_analysis_by_id(analysis_id: str) -> Optional[Dict]:
    """Get hasil analisis berdasarkan ID"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    if DATABASE_TYPE == "postgresql":
        cursor.execute("""
            SELECT * FROM analysis_history 
            WHERE analysis_id = %s
        """, (analysis_id,))
    else:
        cursor.execute("""
            SELECT * FROM analysis_history 
            WHERE analysis_id = ?
        """, (analysis_id,))
    
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None


def get_recent_analyses(limit: int = 20) -> List[Dict]:
    """Get analisis terbaru"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    # Try to use view first, fallback to direct query
    try:
        if DATABASE_TYPE == "postgresql":
            cursor.execute("""
                SELECT * FROM recent_analyses
                LIMIT %s
            """, (limit,))
        else:
            cursor.execute("""
                SELECT * FROM recent_analyses
                LIMIT ?
            """, (limit,))
    except:
        # Fallback if view doesn't exist
        if DATABASE_TYPE == "postgresql":
            cursor.execute("""
                SELECT * FROM analysis_history
                ORDER BY created_at DESC
                LIMIT %s
            """, (limit,))
        else:
            cursor.execute("""
                SELECT * FROM analysis_history
                ORDER BY created_at DESC
                LIMIT ?
            """, (limit,))
    
    analyses = [dict(row) for row in cursor.fetchall()]
    conn.close()
    return analyses

# =============================================================================
# STATISTICS & REPORTING
# =============================================================================

def get_material_statistics() -> List[Dict]:
    """Get statistik per material"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    try:
        cursor.execute("SELECT * FROM material_statistics")
        stats = [dict(row) for row in cursor.fetchall()]
    except:
        # Fallback if view doesn't exist
        cursor.execute("""
            SELECT 
                material,
                COUNT(*) as total_analyses,
                SUM(CASE WHEN is_authentic THEN 1 ELSE 0 END) as authentic_count,
                SUM(CASE WHEN NOT is_authentic THEN 1 ELSE 0 END) as fake_count,
                ROUND(AVG(material_confidence) * 100, 2) as avg_confidence_pct,
                ROUND(AVG(total_processing_time), 3) as avg_processing_time
            FROM analysis_history
            GROUP BY material
        """)
        stats = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return stats


def get_carpet_type_statistics() -> List[Dict]:
    """Get statistik per jenis karpet"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    try:
        cursor.execute("SELECT * FROM carpet_type_statistics")
        stats = [dict(row) for row in cursor.fetchall()]
    except:
        # Fallback if view doesn't exist
        cursor.execute("""
            SELECT 
                carpet_type,
                COUNT(*) as total_analyses,
                SUM(CASE WHEN is_authentic THEN 1 ELSE 0 END) as authentic_count,
                SUM(CASE WHEN NOT is_authentic THEN 1 ELSE 0 END) as fake_count,
                ROUND(AVG(type_confidence) * 100, 2) as avg_confidence_pct,
                ROUND(AVG(total_processing_time), 3) as avg_processing_time
            FROM analysis_history
            GROUP BY carpet_type
        """)
        stats = [dict(row) for row in cursor.fetchall()]
    
    conn.close()
    return stats


def get_overall_statistics() -> Dict:
    """Get statistik keseluruhan"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    # Total analyses
    cursor.execute("SELECT COUNT(*) as total FROM analysis_history")
    result = cursor.fetchone()
    total = result['total'] if DATABASE_TYPE == "postgresql" else result[0]
    
    # Authentic vs fake
    cursor.execute("""
        SELECT 
            COALESCE(SUM(CASE WHEN is_authentic THEN 1 ELSE 0 END), 0) as authentic,
            COALESCE(SUM(CASE WHEN NOT is_authentic THEN 1 ELSE 0 END), 0) as fake
        FROM analysis_history
    """)
    result = cursor.fetchone()
    auth_fake = (result['authentic'], result['fake']) if DATABASE_TYPE == "postgresql" else (result[0], result[1])
    
    # Average processing time
    cursor.execute("SELECT AVG(total_processing_time) as avg_time FROM analysis_history")
    result = cursor.fetchone()
    avg_time = result['avg_time'] if DATABASE_TYPE == "postgresql" else result[0]
    avg_time = avg_time or 0
    
    # Today's analyses
    if DATABASE_TYPE == "postgresql":
        cursor.execute("""
            SELECT COUNT(*) as today FROM analysis_history 
            WHERE DATE(created_at) = CURRENT_DATE
        """)
    else:
        cursor.execute("""
            SELECT COUNT(*) as today FROM analysis_history 
            WHERE DATE(created_at) = DATE('now')
        """)
    result = cursor.fetchone()
    today = result['today'] if DATABASE_TYPE == "postgresql" else result[0]
    
    conn.close()
    
    return {
        'total_analyses': total,
        'authentic_count': auth_fake[0] or 0,
        'fake_count': auth_fake[1] or 0,
        'avg_processing_time': round(avg_time, 3),
        'today_count': today
    }


def log_suspicious_pattern(analysis_id: str, pattern_type: str, 
                          severity: str, details: str):
    """Log pola mencurigakan untuk fraud detection"""
    conn = get_connection()
    cursor = get_cursor(conn)
    
    if DATABASE_TYPE == "postgresql":
        cursor.execute("""
            INSERT INTO suspicious_patterns (
                analysis_id, pattern_type, severity, details
            ) VALUES (%s, %s, %s, %s)
        """, (analysis_id, pattern_type, severity, details))
    else:
        cursor.execute("""
            INSERT INTO suspicious_patterns (
                analysis_id, pattern_type, severity, details
            ) VALUES (?, ?, ?, ?)
        """, (analysis_id, pattern_type, severity, details))
    
    conn.commit()
    conn.close()
    
    print(f"⚠️  Suspicious pattern logged: {pattern_type} ({severity})")

# =============================================================================
# EXPORT FUNCTIONS
# =============================================================================

def export_to_json(analysis_id: str, output_path: str):
    """Export hasil analisis ke JSON file"""
    result = get_analysis_by_id(analysis_id)
    if result:
        # Convert datetime to string
        if 'created_at' in result:
            result['created_at'] = str(result['created_at'])
        
        with open(output_path, 'w', encoding='utf-8') as f:
            json.dump(result, f, indent=2, ensure_ascii=False)
        print(f"📄 Exported to {output_path}")
    else:
        print(f"❌ Analysis not found: {analysis_id}")

# =============================================================================
# MAIN - TEST DATABASE
# =============================================================================

if __name__ == "__main__":
    print()
    print("="*70)
    print("🗄️  TESTING DATABASE MODULE (PostgreSQL Support)")
    print("="*70)
    print(f"Database Type: {DATABASE_TYPE}")
    print(f"Database URL: {DATABASE_URL[:50]}...")
    print("="*70)
    print()
    
    # Initialize database
    init_database()
    print()
    
    # Test: Get all products
    print("📦 Products in database:")
    products = get_all_products()
    for p in products:
        print(f"   • {p['product_name']}: Rp {p['price_min']:,} - Rp {p['price_max']:,}")
    print()
    
    # Test: Get price for specific combination
    print("💰 Price check:")
    price = get_price_range('Polypropylene', 'Asia')
    if price:
        print(f"   Asia + Polypropylene: Rp {price[0]:,} - Rp {price[1]:,}")
    print()
    
    # Test: Get statistics
    print("📊 Overall Statistics:")
    stats = get_overall_statistics()
    print(f"   Total: {stats['total_analyses']}")
    print(f"   Authentic: {stats['authentic_count']}")
    print(f"   Fake: {stats['fake_count']}")
    print(f"   Avg time: {stats['avg_processing_time']}s")
    print()
    
    print("="*70)
    print("✅ Database test complete!")
    print("="*70)
    print()