database.py

import pandas as pd
import sqlite3
import os
from sklearn.preprocessing import StandardScaler
from sklearn.metrics.pairwise import cosine_similarity
import numpy as np
from flask_login import UserMixin
from flask_bcrypt import Bcrypt
import json

bcrypt = Bcrypt()

class User(UserMixin):
    def __init__(self, id, email, name, user_type='buyer'):
        self.id = id
        self.email = email
        self.name = name
        self.user_type = user_type

class EnhancedPropertyDatabase:
    def __init__(self):
        self.df = pd.read_csv('data/housing.csv')
        self.setup_database()
        self.prepare_recommendation_engine()
    
    def setup_database(self):
        """Convert CSV to SQLite database with users"""
        self.conn = sqlite3.connect('properties.db', check_same_thread=False)
        
        # Convert yes/no to 1/0 for ML
        ml_df = self.df.copy()
        binary_cols = ['mainroad', 'guestroom', 'basement', 'hotwaterheating', 'airconditioning', 'prefarea']
        for col in binary_cols:
            ml_df[col] = ml_df[col].map({'yes': 1, 'no': 0})
        
        ml_df['furnishingstatus'] = ml_df['furnishingstatus'].map({
            'unfurnished': 0, 'semi-furnished': 1, 'furnished': 2
        })
        
        # Add image URLs based on property type
        ml_df['image_url'] = ml_df.apply(self.generate_property_image, axis=1)
        
        ml_df.to_sql('properties', self.conn, if_exists='replace', index=False)
        
        # Create users table
        self.conn.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email VARCHAR(100) UNIQUE NOT NULL,
                password VARCHAR(100) NOT NULL,
                name VARCHAR(100) NOT NULL,
                user_type VARCHAR(20) DEFAULT 'buyer',
                favorites TEXT DEFAULT '[]',
                search_history TEXT DEFAULT '[]',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Create demo user
        try:
            hashed_password = bcrypt.generate_password_hash('password123').decode('utf-8')
            self.conn.execute(
                'INSERT INTO users (email, password, name, user_type) VALUES (?, ?, ?, ?)',
                ('demo@homematch.ai', hashed_password, 'Demo User', 'buyer')
            )
            self.conn.commit()
        except:
            pass  # User already exists
    
    def generate_property_image(self, row):
        """Generate realistic property images from Unsplash"""
        base_url = "https://images.unsplash.com/photo-"
        property_types = {
            (1, 5000): "1564013799911-ab67b1b7a30f",  # Small apartment
            (2, 7000): "1568605114967-8130f3a36994",  # Medium apartment
            (3, 9000): "1570129477492-45afee8db7f2",  # Large apartment
            (4, 12000): "1513584684385-0fda4a6f9d38", # Villa
            (5, 20000): "1518780664697-55feff46f6ee"  # Luxury villa
        }
        
        # Find the best matching image
        for (beds, max_area), photo_id in property_types.items():
            if row['bedrooms'] <= beds and row['area'] <= max_area:
                return f"{base_url}{photo_id}?w=600&h=400&fit=crop"
        
        return f"{base_url}1564013799911-ab67b1b7a30f?w=600&h=400&fit=crop"
    
    def prepare_recommendation_engine(self):
        """Prepare ML model for recommendations"""
        # Prepare features for similarity
        features = ['area', 'bedrooms', 'bathrooms', 'stories', 'parking', 
                   'mainroad', 'guestroom', 'basement', 'airconditioning', 'prefarea']
        
        self.ml_df = self.df.copy()
        for col in ['mainroad', 'guestroom', 'basement', 'airconditioning', 'prefarea']:
            self.ml_df[col] = self.ml_df[col].map({'yes': 1, 'no': 0})
        
        self.feature_matrix = self.ml_df[features]
        
        # Standardize features
        self.scaler = StandardScaler()
        self.scaled_features = self.scaler.fit_transform(self.feature_matrix)
        
        # Precompute similarity matrix
        self.similarity_matrix = cosine_similarity(self.scaled_features)
    
    def get_user_by_email(self, email):
        """Get user by email"""
        cursor = self.conn.execute('SELECT * FROM users WHERE email = ?', (email,))
        user = cursor.fetchone()
        if user:
            return User(user[0], user[1], user[3], user[4])
        return None
    
    def verify_password(self, email, password):
        """Verify user password"""
        cursor = self.conn.execute('SELECT password FROM users WHERE email = ?', (email,))
        user = cursor.fetchone()
        if user and bcrypt.check_password_hash(user[0], password):
            return True
        return False
    
    def add_user_favorite(self, user_id, property_id):
        """Add property to user favorites"""
        cursor = self.conn.execute('SELECT favorites FROM users WHERE id = ?', (user_id,))
        user = cursor.fetchone()
        if user:
            favorites = json.loads(user[0] or '[]')
            if property_id not in favorites:
                favorites.append(property_id)
                self.conn.execute(
                    'UPDATE users SET favorites = ? WHERE id = ?',
                    (json.dumps(favorites), user_id)
                )
                self.conn.commit()
                return True
        return False
    
    def get_user_favorites(self, user_id):
        """Get user's favorite properties"""
        cursor = self.conn.execute('SELECT favorites FROM users WHERE id = ?', (user_id,))
        user = cursor.fetchone()
        if user and user[0]:
            favorite_ids = json.loads(user[0])
            return [self.get_property_by_id(pid) for pid in favorite_ids if self.get_property_by_id(pid)]
        return []
    
    def get_all_properties(self):
        """Get all properties from database"""
        return pd.read_sql('SELECT * FROM properties', self.conn).to_dict('records')
    
    def get_property_by_id(self, property_id):
        """Get specific property by ID"""
        query = f'SELECT * FROM properties WHERE rowid = {property_id + 1}'
        result = pd.read_sql(query, self.conn)
        return result.to_dict('records')[0] if not result.empty else None
    
    def get_similar_properties(self, property_id, num_recommendations=6):
        """Get similar properties using cosine similarity"""
        if property_id >= len(self.similarity_matrix):
            return []
        
        similar_indices = self.similarity_matrix[property_id].argsort()[::-1][1:num_recommendations+1]
        similar_properties = []
        
        for idx in similar_indices:
            property_data = self.get_property_by_id(idx)
            if property_data:
                similar_properties.append(property_data)
        
        return similar_properties
    
    def search_properties(self, filters):
        """Search properties based on filters"""
        query = 'SELECT * FROM properties WHERE 1=1'
        params = []
        
        if filters.get('min_price'):
            query += ' AND price >= ?'
            params.append(filters['min_price'])
        
        if filters.get('max_price'):
            query += ' AND price <= ?'
            params.append(filters['max_price'])
        
        if filters.get('bedrooms'):
            query += ' AND bedrooms = ?'
            params.append(filters['bedrooms'])
        
        if filters.get('bathrooms'):
            query += ' AND bathrooms = ?'
            params.append(filters['bathrooms'])
        
        return pd.read_sql(query, self.conn, params=params).to_dict('records')

# Global database instance
property_db = EnhancedPropertyDatabase()