#!/usr/bin/env python3
"""
Database Manager - Unified access layer for Paradisomatch database
"""

import sqlite3
import json
from datetime import datetime
from pathlib import Path
from typing import List, Dict, Optional, Tuple
from database_schema import DATABASE_PATH, get_connection

class DatabaseManager:
    """Unified database access layer"""

    def __init__(self, db_path=DATABASE_PATH):
        self.db_path = db_path

    def get_connection(self):
        """Get a database connection"""
        return get_connection(self.db_path)

    # ===== PROPERTY OPERATIONS =====

    def get_all_properties(self, status='Active') -> List[Dict]:
        """Get all properties with optional status filter"""
        conn = self.get_connection()
        cursor = conn.cursor()

        if status:
            cursor.execute("SELECT * FROM properties WHERE status = ?", (status,))
        else:
            cursor.execute("SELECT * FROM properties")

        properties = [dict(row) for row in cursor.fetchall()]
        conn.close()
        return properties

    def get_property_by_url(self, url: str) -> Optional[Dict]:
        """Get a single property by URL"""
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM properties WHERE url = ?", (url,))
        row = cursor.fetchone()
        conn.close()
        return dict(row) if row else None

    def get_property_by_id(self, property_id: int) -> Optional[Dict]:
        """Get a single property by ID"""
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM properties WHERE id = ?", (property_id,))
        row = cursor.fetchone()
        conn.close()
        return dict(row) if row else None

    def add_property(self, **kwargs) -> int:
        """Add a new property, returns property_id"""
        conn = self.get_connection()
        cursor = conn.cursor()

        fields = list(kwargs.keys())
        placeholders = ','.join(['?'] * len(fields))
        field_names = ','.join(fields)

        cursor.execute(f"""
            INSERT INTO properties ({field_names})
            VALUES ({placeholders})
        """, tuple(kwargs.values()))

        property_id = cursor.lastrowid
        conn.commit()
        conn.close()
        return property_id

    def update_property(self, url: str, **kwargs) -> bool:
        """Update a property by URL"""
        conn = self.get_connection()
        cursor = conn.cursor()

        # Add last_updated
        kwargs['last_updated'] = datetime.now().isoformat()

        set_clause = ', '.join([f"{k} = ?" for k in kwargs.keys()])
        cursor.execute(f"""
            UPDATE properties SET {set_clause}
            WHERE url = ?
        """, tuple(list(kwargs.values()) + [url]))

        success = cursor.rowcount > 0
        conn.commit()
        conn.close()
        return success

    def delete_property(self, url: str) -> bool:
        """Delete a property (and related data via CASCADE)"""
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM properties WHERE url = ?", (url,))
        success = cursor.rowcount > 0
        conn.commit()
        conn.close()
        return success

    # ===== GEOCODING OPERATIONS =====

    def get_missing_geocoded_properties(self) -> List[Dict]:
        """Get active properties without coordinates"""
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT * FROM properties
            WHERE status = 'Active'
            AND (lat IS NULL OR lon IS NULL)
        """)
        properties = [dict(row) for row in cursor.fetchall()]
        conn.close()
        return properties

    def update_geocoding(self, url: str, lat: float, lon: float,
                        confidence: str = 'medium', source: str = 'manual') -> bool:
        """Update geocoding for a property"""
        conn = self.get_connection()
        cursor = conn.cursor()

        # Get property_id
        cursor.execute("SELECT id FROM properties WHERE url = ?", (url,))
        row = cursor.fetchone()
        if not row:
            conn.close()
            return False

        property_id = row[0]

        # Update property
        cursor.execute("""
            UPDATE properties
            SET lat = ?, lon = ?, geocoding_confidence = ?,
                geocoded_from = ?, geocoded_at = ?, last_updated = ?
            WHERE id = ?
        """, (lat, lon, confidence, source,
              datetime.now().isoformat(), datetime.now().isoformat(), property_id))

        # Add to history
        cursor.execute("""
            INSERT INTO geocoding_history (property_id, lat, lon, confidence, source)
            VALUES (?, ?, ?, ?, ?)
        """, (property_id, lat, lon, confidence, source))

        conn.commit()
        conn.close()
        return True

    def get_geocoding_stats(self) -> Dict:
        """Get geocoding coverage statistics"""
        conn = self.get_connection()
        cursor = conn.cursor()

        cursor.execute("SELECT COUNT(*) FROM properties WHERE status = 'Active'")
        total = cursor.fetchone()[0]

        cursor.execute("""
            SELECT COUNT(*) FROM properties
            WHERE status = 'Active' AND lat IS NOT NULL AND lon IS NOT NULL
        """)
        geocoded = cursor.fetchone()[0]

        conn.close()

        coverage = (geocoded / total * 100) if total > 0 else 0
        return {
            'total': total,
            'geocoded': geocoded,
            'missing': total - geocoded,
            'coverage_percent': round(coverage, 1)
        }

    # ===== EXPORT OPERATIONS =====

    def export_to_json(self, output_path: str = 'enriched_data.json') -> bool:
        """Export all properties to JSON format (backward compatibility)"""
        properties = self.get_all_properties(status=None)  # Get all, including removed

        # Convert datetime strings back to original format if needed
        for prop in properties:
            # Remove id field (not in original JSON)
            if 'id' in prop:
                del prop['id']

        with open(output_path, 'w') as f:
            json.dump(properties, f, indent=2)

        return True

    def import_from_json(self, input_path: str = 'enriched_data.json') -> Tuple[int, int]:
        """Import properties from JSON (for backward compatibility)"""
        with open(input_path, 'r') as f:
            properties = json.load(f)

        conn = self.get_connection()
        cursor = conn.cursor()

        added = 0
        updated = 0

        for prop in properties:
            url = prop.get('url')
            if not url:
                continue

            # Check if exists
            cursor.execute("SELECT id FROM properties WHERE url = ?", (url,))
            existing = cursor.fetchone()

            if existing:
                # Update
                property_id = existing[0]
                set_fields = {k: v for k, v in prop.items() if k != 'url'}
                if set_fields:
                    set_clause = ', '.join([f"{k} = ?" for k in set_fields.keys()])
                    cursor.execute(f"""
                        UPDATE properties SET {set_clause}, last_updated = ?
                        WHERE id = ?
                    """, tuple(list(set_fields.values()) + [datetime.now().isoformat(), property_id]))
                    updated += 1
            else:
                # Insert
                fields = list(prop.keys())
                placeholders = ','.join(['?'] * len(fields))
                field_names = ','.join(fields)
                cursor.execute(f"""
                    INSERT INTO properties ({field_names})
                    VALUES ({placeholders})
                """, tuple(prop.values()))
                added += 1

        conn.commit()
        conn.close()

        return added, updated

    # ===== STATISTICS =====

    def get_statistics(self) -> Dict:
        """Get comprehensive system statistics"""
        conn = self.get_connection()
        cursor = conn.cursor()

        stats = {}

        # Properties
        cursor.execute("SELECT COUNT(*) FROM properties")
        stats['total_properties'] = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM properties WHERE status = 'Active'")
        stats['active_properties'] = cursor.fetchone()[0]

        # Geocoding
        cursor.execute("""
            SELECT COUNT(*) FROM properties
            WHERE status = 'Active' AND lat IS NOT NULL AND lon IS NOT NULL
        """)
        stats['geocoded_properties'] = cursor.fetchone()[0]

        # Scores
        cursor.execute("""
            SELECT COUNT(*) FROM properties
            WHERE status = 'Active' AND gpt_score IS NOT NULL
        """)
        stats['analyzed_properties'] = cursor.fetchone()[0]

        # History
        cursor.execute("SELECT COUNT(*) FROM geocoding_history")
        stats['geocoding_operations'] = cursor.fetchone()[0]

        cursor.execute("SELECT COUNT(*) FROM analysis_history")
        stats['analysis_operations'] = cursor.fetchone()[0]

        # Database size
        stats['database_size_kb'] = round(Path(self.db_path).stat().st_size / 1024, 1)

        conn.close()

        return stats


if __name__ == '__main__':
    # Demo/test
    db = DatabaseManager()

    print("="*70)
    print("DATABASE MANAGER - DEMO")
    print("="*70)

    # Get statistics
    stats = db.get_statistics()
    print("\n📊 Statistics:")
    for key, value in stats.items():
        print(f"  {key}: {value}")

    # Get geocoding stats
    geo_stats = db.get_geocoding_stats()
    print("\n📍 Geocoding:")
    print(f"  Coverage: {geo_stats['coverage_percent']}%")
    print(f"  Geocoded: {geo_stats['geocoded']}/{geo_stats['total']}")
    print(f"  Missing: {geo_stats['missing']}")

    # Get a sample property
    properties = db.get_all_properties()
    if properties:
        print(f"\n🏠 Sample property:")
        prop = properties[0]
        print(f"  Title: {prop.get('title', 'N/A')[:60]}")
        print(f"  URL: {prop.get('url', 'N/A')}")
        print(f"  Location: ({prop.get('lat')}, {prop.get('lon')})")
        print(f"  GPT Score: {prop.get('gpt_score')}")
        print(f"  Custom Score: {prop.get('custom_score')}")