"""
Database Manager for Real Estate Bot
Author: RSK World (https://rskworld.in)
Founded by: Molla Samser
Designer & Tester: Rima Khatun
Contact: info@rskworld.com, +91 93305 39277
Year: 2026
"""

import os
import sqlite3
import json
from datetime import datetime
from typing import List, Dict, Any, Optional

class DatabaseManager:
    def __init__(self, db_path: str = None):
        """
        Initialize Database Manager
        
        Args:
            db_path: Path to SQLite database file
        """
        if db_path is None:
            # Use data directory for database
            data_dir = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'data')
            os.makedirs(data_dir, exist_ok=True)
            db_path = os.path.join(data_dir, "real_estate_bot.db")
        
        self.db_path = db_path
        self.initialize_database()
    
    def initialize_database(self):
        """Initialize database tables"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # Properties table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS properties (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    description TEXT,
                    price REAL,
                    location TEXT,
                    property_type TEXT,
                    bedrooms INTEGER,
                    bathrooms INTEGER,
                    area_sqft REAL,
                    year_built INTEGER,
                    amenities TEXT,  -- JSON string
                    images TEXT,    -- JSON string
                    owner_id INTEGER,
                    is_available BOOLEAN DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Users table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT,
                    email TEXT UNIQUE,
                    phone TEXT,
                    preferences TEXT,  -- JSON string
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Appointments table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS appointments (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER,
                    property_id INTEGER,
                    date_time TIMESTAMP,
                    status TEXT DEFAULT 'scheduled',  -- scheduled, completed, cancelled
                    notes TEXT,
                    contact_info TEXT,  -- JSON string
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id),
                    FOREIGN KEY (property_id) REFERENCES properties (id)
                )
            ''')
            
            # Inquiries table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS inquiries (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER,
                    property_id INTEGER,
                    message TEXT,
                    status TEXT DEFAULT 'open',  -- open, answered, closed
                    contact_info TEXT,  -- JSON string
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users (id),
                    FOREIGN KEY (property_id) REFERENCES properties (id)
                )
            ''')
            
            # Chat history table
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS chat_history (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id TEXT,
                    message TEXT,
                    response TEXT,
                    intent TEXT,
                    entities TEXT,  -- JSON string
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # Insert sample properties if table is empty
            cursor.execute("SELECT COUNT(*) FROM properties")
            if cursor.fetchone()[0] == 0:
                self._insert_sample_properties(cursor)
            
            conn.commit()
            conn.close()
            
        except Exception as e:
            print(f"Error initializing database: {e}")
            raise
    
    def _insert_sample_properties(self, cursor):
        """Insert sample properties for demonstration"""
        sample_properties = [
            {
                'title': 'Modern 2BHK Apartment in Mumbai',
                'description': 'Spacious 2BHK apartment with modern amenities, located in the heart of Mumbai with excellent connectivity.',
                'price': 8500000,
                'location': 'Mumbai, Maharashtra',
                'property_type': 'apartment',
                'bedrooms': 2,
                'bathrooms': 2,
                'area_sqft': 1200,
                'year_built': 2020,
                'amenities': json.dumps(['parking', 'gym', 'swimming_pool', 'security', 'power_backup']),
                'images': json.dumps(['apartment1_1.jpg', 'apartment1_2.jpg'])
            },
            {
                'title': 'Luxury 3BHK Villa in Bangalore',
                'description': 'Beautiful 3BHK villa with garden and private parking. Perfect for families.',
                'price': 15000000,
                'location': 'Bangalore, Karnataka',
                'property_type': 'house',
                'bedrooms': 3,
                'bathrooms': 3,
                'area_sqft': 2000,
                'year_built': 2019,
                'amenities': json.dumps(['garden', 'parking', 'security', 'solar_panels']),
                'images': json.dumps(['villa1_1.jpg', 'villa1_2.jpg'])
            },
            {
                'title': 'Cozy 1BHK Flat in Delhi',
                'description': 'Affordable 1BHK flat ideal for singles or couples. Close to metro station.',
                'price': 4500000,
                'location': 'Delhi NCR',
                'property_type': 'apartment',
                'bedrooms': 1,
                'bathrooms': 1,
                'area_sqft': 650,
                'year_built': 2021,
                'amenities': json.dumps(['parking', 'security', 'water_supply']),
                'images': json.dumps(['flat1_1.jpg', 'flat1_2.jpg'])
            },
            {
                'title': 'Spacious 4BHK Penthouse in Pune',
                'description': 'Luxurious penthouse with panoramic city views and premium amenities.',
                'price': 25000000,
                'location': 'Pune, Maharashtra',
                'property_type': 'penthouse',
                'bedrooms': 4,
                'bathrooms': 4,
                'area_sqft': 3200,
                'year_built': 2022,
                'amenities': json.dumps(['terrace', 'jacuzzi', 'gym', 'concierge', 'parking']),
                'images': json.dumps(['penthouse1_1.jpg', 'penthouse1_2.jpg'])
            },
            {
                'title': 'Affordable 2BHK in Chennai',
                'description': 'Budget-friendly 2BHK apartment in a peaceful neighborhood.',
                'price': 6500000,
                'location': 'Chennai, Tamil Nadu',
                'property_type': 'apartment',
                'bedrooms': 2,
                'bathrooms': 2,
                'area_sqft': 950,
                'year_built': 2020,
                'amenities': json.dumps(['parking', 'security', 'community_hall']),
                'images': json.dumps(['chennai1_1.jpg', 'chennai1_2.jpg'])
            }
        ]
        
        for prop in sample_properties:
            cursor.execute('''
                INSERT INTO properties (title, description, price, location, property_type, 
                                     bedrooms, bathrooms, area_sqft, year_built, amenities, images)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                prop['title'], prop['description'], prop['price'], prop['location'],
                prop['property_type'], prop['bedrooms'], prop['bathrooms'],
                prop['area_sqft'], prop['year_built'], prop['amenities'], prop['images']
            ))
    
    def search_properties(self, criteria: Dict[str, Any]) -> List[Dict[str, Any]]:
        """
        Search properties based on criteria
        
        Args:
            criteria: Search criteria dictionary
            
        Returns:
            List of matching properties
        """
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            query = "SELECT * FROM properties WHERE is_available = 1"
            params = []
            
            if criteria.get('location'):
                query += " AND location LIKE ?"
                params.append(f"%{criteria['location']}%")
            
            if criteria.get('property_type'):
                query += " AND property_type = ?"
                params.append(criteria['property_type'])
            
            if criteria.get('bedrooms'):
                query += " AND bedrooms = ?"
                params.append(criteria['bedrooms'])
            
            if criteria.get('price_min'):
                query += " AND price >= ?"
                params.append(criteria['price_min'])
            
            if criteria.get('price_max'):
                query += " AND price <= ?"
                params.append(criteria['price_max'])
            
            query += " ORDER BY created_at DESC"
            
            cursor.execute(query, params)
            rows = cursor.fetchall()
            
            # Convert to dictionary format
            properties = []
            for row in rows:
                prop = {
                    'id': row[0],
                    'title': row[1],
                    'description': row[2],
                    'price': row[3],
                    'location': row[4],
                    'property_type': row[5],
                    'bedrooms': row[6],
                    'bathrooms': row[7],
                    'area_sqft': row[8],
                    'year_built': row[9],
                    'amenities': json.loads(row[10]) if row[10] else [],
                    'images': json.loads(row[11]) if row[11] else [],
                    'owner_id': row[12] if len(row) > 12 else None,
                    'is_available': bool(row[13]) if len(row) > 13 else True,
                    'created_at': row[14] if len(row) > 14 else None,
                    'updated_at': row[15] if len(row) > 15 else None
                }
                properties.append(prop)
            
            conn.close()
            return properties
            
        except Exception as e:
            print(f"Error searching properties: {e}")
            return []
    
    def get_property_by_id(self, property_id: int) -> Optional[Dict[str, Any]]:
        """
        Get property details by ID
        
        Args:
            property_id: Property ID
            
        Returns:
            Property details or None if not found
        """
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute("SELECT * FROM properties WHERE id = ?", (property_id,))
            row = cursor.fetchone()
            
            if row:
                prop = {
                    'id': row[0],
                    'title': row[1],
                    'description': row[2],
                    'price': row[3],
                    'location': row[4],
                    'property_type': row[5],
                    'bedrooms': row[6],
                    'bathrooms': row[7],
                    'area_sqft': row[8],
                    'year_built': row[9],
                    'amenities': json.loads(row[10]) if row[10] else [],
                    'images': json.loads(row[11]) if row[11] else [],
                    'owner_id': row[12] if len(row) > 12 else None,
                    'is_available': bool(row[13]) if len(row) > 13 else True,
                    'created_at': row[14] if len(row) > 14 else None,
                    'updated_at': row[15] if len(row) > 15 else None
                }
                conn.close()
                return prop
            
            conn.close()
            return None
            
        except Exception as e:
            print(f"Error getting property by ID: {e}")
            return None
    
    def schedule_appointment(self, user_id: str, property_id: int, date_time: str, contact_info: Dict[str, Any]) -> int:
        """
        Schedule a property viewing appointment
        
        Args:
            user_id: User identifier (string)
            property_id: Property ID (integer)
            date_time: Preferred date and time (string)
            contact_info: Contact information dictionary
            
        Returns:
            Appointment ID
        """
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # Convert user_id to int if it's stored as integer in database, otherwise keep as string
            # Since appointments table uses INTEGER for user_id, we need to handle this
            # For now, we'll use the string directly and let SQLite handle conversion
            # If user doesn't exist, we might need to create one or use NULL
            
            # Handle user_id - if it's a string like 'anonymous', convert to integer user ID
            # For now, we'll try to convert or use a default value
            try:
                user_id_int = int(user_id) if user_id.isdigit() else None
            except (ValueError, AttributeError):
                user_id_int = None
            
            cursor.execute('''
                INSERT INTO appointments (user_id, property_id, date_time, contact_info)
                VALUES (?, ?, ?, ?)
            ''', (user_id_int, int(property_id) if property_id else None, date_time, json.dumps(contact_info)))
            
            appointment_id = cursor.lastrowid
            conn.commit()
            conn.close()
            
            return appointment_id
            
        except Exception as e:
            print(f"Error scheduling appointment: {e}")
            raise
    
    def get_user_appointments(self, user_id: str) -> List[Dict[str, Any]]:
        """
        Get user's scheduled appointments
        
        Args:
            user_id: User identifier
            
        Returns:
            List of appointments
        """
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT a.*, p.title, p.location 
                FROM appointments a
                JOIN properties p ON a.property_id = p.id
                WHERE a.user_id = ?
                ORDER BY a.date_time DESC
            ''', (user_id,))
            
            rows = cursor.fetchall()
            
            appointments = []
            for row in rows:
                appointment = {
                    'id': row[0],
                    'user_id': row[1],
                    'property_id': row[2],
                    'date_time': row[3],
                    'status': row[4],
                    'notes': row[5],
                    'contact_info': json.loads(row[6]) if row[6] else {},
                    'created_at': row[7],
                    'updated_at': row[8],
                    'property_title': row[9],
                    'property_location': row[10]
                }
                appointments.append(appointment)
            
            conn.close()
            return appointments
            
        except Exception as e:
            print(f"Error getting user appointments: {e}")
            return []
    
    def create_inquiry(self, user_id: str, property_id: int, message: str, contact_info: Dict[str, Any]) -> int:
        """
        Create a property inquiry
        
        Args:
            user_id: User identifier
            property_id: Property ID
            message: Inquiry message
            contact_info: Contact information dictionary
            
        Returns:
            Inquiry ID
        """
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO inquiries (user_id, property_id, message, contact_info)
                VALUES (?, ?, ?, ?)
            ''', (user_id, property_id, message, json.dumps(contact_info)))
            
            inquiry_id = cursor.lastrowid
            conn.commit()
            conn.close()
            
            return inquiry_id
            
        except Exception as e:
            print(f"Error creating inquiry: {e}")
            raise
    
    def save_chat_message(self, user_id: str, message: str, response: str, intent: str, entities: Dict[str, Any]):
        """
        Save chat message to history
        
        Args:
            user_id: User identifier
            message: User message
            response: Bot response
            intent: Detected intent
            entities: Extracted entities
        """
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            cursor.execute('''
                INSERT INTO chat_history (user_id, message, response, intent, entities)
                VALUES (?, ?, ?, ?, ?)
            ''', (user_id, message, response, intent, json.dumps(entities)))
            
            conn.commit()
            conn.close()
            
        except Exception as e:
            print(f"Error saving chat message: {e}")