#!/usr/bin/env python3
"""
SQLite Migration Script for Autocolant Product Search

This script migrates from products.json to SQLite with Full-Text Search (FTS5)
for improved search performance and relevance scoring.

Usage:
    python migrate_to_sqlite.py

Creates:
    - products.db (SQLite database with FTS)
    - Backup of original products.json
"""

import json
import sqlite3
import shutil
from datetime import datetime

def migrate_json_to_sqlite():
    """Migrate products.json to SQLite with FTS5"""

    print("🚀 Starting migration from JSON to SQLite...")

    # Backup original JSON file
    backup_file = f"products_backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}.json"
    shutil.copy2('products.json', backup_file)
    print(f"✅ Backed up products.json to {backup_file}")

    # Load JSON data
    with open('products.json', 'r', encoding='utf-8') as f:
        products = json.load(f)
    print(f"📊 Loaded {len(products)} products from JSON")

    # Create SQLite database
    conn = sqlite3.connect('products.db')
    cursor = conn.cursor()

    # Create regular products table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY,
            nume TEXT NOT NULL,
            url TEXT NOT NULL,
            descriere_ro TEXT,
            descriere_en TEXT,
            instructiuni_ro TEXT,
            instructiuni_en TEXT,
            subsubcategorie INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')

    # Create FTS5 virtual table for search
    cursor.execute('''
        CREATE VIRTUAL TABLE IF NOT EXISTS products_fts USING fts5(
            id UNINDEXED,
            nume,
            descriere_ro,
            url UNINDEXED,
            tokenize='porter unicode61'  -- Porter stemming + Unicode support
        )
    ''')

    print("🏗️  Created database tables")

    # Insert products
    inserted_count = 0
    for product in products:
        # Insert into main table
        cursor.execute('''
            INSERT OR REPLACE INTO products
            (id, nume, url, descriere_ro, descriere_en, instructiuni_ro, instructiuni_en, subsubcategorie)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            product.get('id'),
            product.get('nume', ''),
            product.get('url', ''),
            product.get('descriere_ro', ''),
            product.get('descriere_en', ''),
            product.get('instructiuni_ro', ''),
            product.get('instructiuni_en', ''),
            product.get('subsubcategorie')
        ))

        # Insert into FTS table
        cursor.execute('''
            INSERT OR REPLACE INTO products_fts
            (id, nume, descriere_ro, url)
            VALUES (?, ?, ?, ?)
        ''', (
            product.get('id'),
            product.get('nume', ''),
            product.get('descriere_ro', ''),
            product.get('url', '')
        ))

        inserted_count += 1

    # Create indexes for performance
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_products_id ON products(id)')
    cursor.execute('CREATE INDEX IF NOT EXISTS idx_products_subsubcategorie ON products(subsubcategorie)')

    conn.commit()
    print(f"✅ Inserted {inserted_count} products into SQLite")

    # Test the FTS search
    print("\n🔍 Testing FTS search...")

    test_queries = ['negru', 'gri lucios', 'oracal', 'autocolant']
    for query in test_queries:
        cursor.execute('''
            SELECT nume, rank FROM products_fts
            WHERE products_fts MATCH ?
            ORDER BY rank
            LIMIT 3
        ''', (query,))

        results = cursor.fetchall()
        print(f"  '{query}': {len(results)} results")
        for result in results:
            print(f"    - {result[0][:50]}...")

    conn.close()
    print(f"\n🎉 Migration completed! Database saved as products.db")
    print(f"💾 Original JSON backed up as {backup_file}")


def create_sqlite_search_function():
    """Create the new search function that uses SQLite FTS"""

    search_function = '''
def search_products_sqlite(query):
    """
    SQLite FTS5 search function - replacement for JSON search
    """
    import sqlite3

    max_results = 5
    print("INFO: Searching SQLite FTS for query: " + query)

    # Connect to SQLite database
    conn = sqlite3.connect('products.db')
    cursor = conn.cursor()

    try:
        # Use FTS5 MATCH for intelligent search
        # FTS5 handles multi-word queries, stemming, and relevance automatically
        cursor.execute('''
            SELECT p.nume, p.url, rank
            FROM products_fts fts
            JOIN products p ON p.id = fts.id
            WHERE products_fts MATCH ?
            ORDER BY rank
            LIMIT ?
        ''', (query, max_results))

        results = cursor.fetchall()

        # Format results
        products = []
        for row in results:
            products.append({
                'nume': row[0],
                'url': row[1]
            })

        print(f"INFO: Found {len(products)} products matching query: {query}")
        if products:
            print(f"INFO: Top match relevance score available")

        return {
            'products': products,
            'total_found': len(products),
            'search_query': query
        }

    except sqlite3.OperationalError as e:
        print(f"ERROR: SQLite search failed: {e}")
        # Fallback to simple LIKE search
        cursor.execute('''
            SELECT nume, url FROM products
            WHERE nume LIKE ? OR descriere_ro LIKE ?
            ORDER BY
                CASE WHEN nume LIKE ? THEN 1 ELSE 2 END,
                nume
            LIMIT ?
        ''', (f'%{query}%', f'%{query}%', f'%{query}%', max_results))

        results = cursor.fetchall()
        products = [{'nume': row[0], 'url': row[1]} for row in results]

        print(f"INFO: Fallback search found {len(products)} products")

        return {
            'products': products,
            'total_found': len(products),
            'search_query': query
        }

    finally:
        conn.close()
'''

    return search_function


if __name__ == "__main__":
    migrate_json_to_sqlite()

    print("\n" + "="*60)
    print("NEXT STEPS:")
    print("1. Test the new database: python -c 'import sqlite3; print(\"✅ SQLite working\")'")
    print("2. Replace search_products() in functions.py with SQLite version")
    print("3. Update requirements: pip install sqlite3 (usually built-in)")
    print("4. Test with ManyChat integration")
    print("5. Monitor performance improvements")
    print("="*60)