#!/usr/bin/env python3
"""
MSSQL Database Dump Script
Exports schema and data from the LRS43 database
"""

import sys
import os
sys.path.insert(0, '/home/plagis/workspace/plagis_aumentum')

from aumentum_browser_service import DEFAULT_DB_CONFIG
import pyodbc
from datetime import datetime

OUTPUT_DIR = "/home/plagis/workspace/plagis_aumentum/database_dump"
os.makedirs(OUTPUT_DIR, exist_ok=True)

def get_connection():
    """Get database connection"""
    driver = DEFAULT_DB_CONFIG.get("driver", "FreeTDS")
    server = DEFAULT_DB_CONFIG.get("server")
    database = DEFAULT_DB_CONFIG.get("database")
    username = DEFAULT_DB_CONFIG.get("username")
    password = DEFAULT_DB_CONFIG.get("password", "")
    
    if driver == "FreeTDS":
        conn_str = (
            f"DRIVER={{{driver}}};"
            f"SERVERNAME={server};"
            f"DATABASE={database};"
            f"UID={username};"
            f"PWD={password};"
        )
    else:
        conn_str = (
            f"DRIVER={{{driver}}};"
            f"SERVER={server},1433;"
            f"DATABASE={database};"
            f"UID={username};"
            f"PWD={password};"
            "Encrypt=no;"
            "TrustServerCertificate=yes;"
        )
    
    print(f"🔌 Connecting to {database}...")
    return pyodbc.connect(conn_str, timeout=30)

def dump_schema(conn, output_file):
    """Dump database schema (tables, columns, types)"""
    cursor = conn.cursor()
    
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(f"-- Database Schema Dump\n")
        f.write(f"-- Database: {DEFAULT_DB_CONFIG.get('database')}\n")
        f.write(f"-- Generated: {datetime.now().isoformat()}\n")
        f.write(f"-- ==========================================\n\n")
        
        # Get all tables
        cursor.execute("""
            SELECT 
                s.name AS schema_name,
                t.name AS table_name,
                t.create_date,
                t.modify_date
            FROM sys.tables t
            JOIN sys.schemas s ON t.schema_id = s.schema_id
            WHERE s.name = 'LRSAdmin'
            ORDER BY t.name
        """)
        
        tables = cursor.fetchall()
        print(f"📊 Found {len(tables)} tables")
        
        f.write(f"-- Total Tables: {len(tables)}\n\n")
        
        for schema_name, table_name, create_date, modify_date in tables:
            full_table_name = f"{schema_name}.{table_name}"
            f.write(f"-- ==========================================\n")
            f.write(f"-- Table: {full_table_name}\n")
            f.write(f"-- Created: {create_date}\n")
            f.write(f"-- Modified: {modify_date}\n")
            f.write(f"-- ==========================================\n\n")
            
            # Get table columns
            cursor.execute("""
                SELECT 
                    c.name AS column_name,
                    t.name AS data_type,
                    c.max_length,
                    c.precision,
                    c.scale,
                    c.is_nullable,
                    c.is_identity,
                    ISNULL(dc.definition, '') AS default_value
                FROM sys.columns c
                JOIN sys.types t ON c.user_type_id = t.user_type_id
                LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
                WHERE c.object_id = OBJECT_ID(?)
                ORDER BY c.column_id
            """, (full_table_name,))
            
            columns = cursor.fetchall()
            
            f.write(f"CREATE TABLE {full_table_name} (\n")
            
            col_defs = []
            for col in columns:
                col_name, data_type, max_len, precision, scale, nullable, is_identity, default = col
                
                # Build column definition
                col_def = f"    [{col_name}] {data_type}"
                
                # Add length/precision
                if data_type in ('varchar', 'nvarchar', 'char', 'nchar'):
                    if max_len == -1:
                        col_def += "(MAX)"
                    else:
                        actual_len = max_len // 2 if data_type.startswith('n') else max_len
                        col_def += f"({actual_len})"
                elif data_type in ('decimal', 'numeric'):
                    col_def += f"({precision},{scale})"
                
                # Add identity
                if is_identity:
                    col_def += " IDENTITY(1,1)"
                
                # Add nullable
                if not nullable:
                    col_def += " NOT NULL"
                else:
                    col_def += " NULL"
                
                # Add default
                if default:
                    col_def += f" DEFAULT {default}"
                
                col_defs.append(col_def)
            
            f.write(",\n".join(col_defs))
            f.write("\n);\n\n")
            
            # Get primary key
            cursor.execute("""
                SELECT 
                    i.name AS index_name,
                    c.name AS column_name,
                    ic.key_ordinal
                FROM sys.indexes i
                JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                WHERE i.object_id = OBJECT_ID(?)
                AND i.is_primary_key = 1
                ORDER BY ic.key_ordinal
            """, (full_table_name,))
            
            pk_rows = cursor.fetchall()
            if pk_rows:
                pk_name = pk_rows[0][0]
                pk_cols = ", ".join([row[1] for row in pk_rows])
                f.write(f"ALTER TABLE {full_table_name}\n")
                f.write(f"    ADD CONSTRAINT [{pk_name}] PRIMARY KEY ({pk_cols});\n\n")
            
            # Get indexes
            cursor.execute("""
                SELECT 
                    i.name AS index_name,
                    i.is_unique,
                    c.name AS column_name,
                    ic.key_ordinal
                FROM sys.indexes i
                JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                WHERE i.object_id = OBJECT_ID(?)
                AND i.is_primary_key = 0
                AND i.type > 0
                ORDER BY i.name, ic.key_ordinal
            """, (full_table_name,))
            
            idx_rows = cursor.fetchall()
            if idx_rows:
                # Group by index name
                current_idx = None
                idx_cols = []
                is_unique = False
                
                for idx_name, idx_is_unique, col_name, ordinal in idx_rows:
                    if current_idx != idx_name:
                        # Write previous index
                        if current_idx:
                            unique_str = "UNIQUE " if is_unique else ""
                            f.write(f"CREATE {unique_str}INDEX [{current_idx}] ON {full_table_name} ({', '.join(idx_cols)});\n")
                        # Start new index
                        current_idx = idx_name
                        is_unique = idx_is_unique
                        idx_cols = [col_name]
                    else:
                        idx_cols.append(col_name)
                
                # Write last index
                if current_idx:
                    unique_str = "UNIQUE " if is_unique else ""
                    f.write(f"CREATE {unique_str}INDEX [{current_idx}] ON {full_table_name} ({', '.join(idx_cols)});\n")
                
                f.write("\n")
            
            f.write("\n")
    
    cursor.close()
    print(f"✅ Schema dumped to: {output_file}")

def dump_table_data(conn, table_name, output_file, limit=1000):
    """Dump data from a specific table"""
    cursor = conn.cursor()
    
    full_table_name = f"LRSAdmin.{table_name}"
    
    # Get row count
    cursor.execute(f"SELECT COUNT(*) FROM {full_table_name}")
    total_rows = cursor.fetchone()[0]
    
    if total_rows == 0:
        print(f"   ⚠️  {table_name}: No data")
        return
    
    # Get data
    query = f"SELECT TOP {limit} * FROM {full_table_name}"
    cursor.execute(query)
    
    columns = [desc[0] for desc in cursor.description]
    rows = cursor.fetchall()
    
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(f"-- Table: {full_table_name}\n")
        f.write(f"-- Total Rows: {total_rows:,}\n")
        f.write(f"-- Dumped Rows: {len(rows):,}\n")
        f.write(f"-- Generated: {datetime.now().isoformat()}\n")
        f.write(f"-- ==========================================\n\n")
        
        for row in rows:
            values = []
            for val in row:
                if val is None:
                    values.append("NULL")
                elif isinstance(val, str):
                    # Escape single quotes
                    escaped = val.replace("'", "''")
                    values.append(f"'{escaped}'")
                elif isinstance(val, (int, float)):
                    values.append(str(val))
                elif isinstance(val, datetime):
                    values.append(f"'{val.isoformat()}'")
                else:
                    values.append(f"'{str(val)}'")
            
            col_list = ", ".join(f"[{col}]" for col in columns)
            val_list = ", ".join(values)
            f.write(f"INSERT INTO {full_table_name} ({col_list}) VALUES ({val_list});\n")
        
        f.write("\n")
    
    cursor.close()
    print(f"   ✅ {table_name}: {len(rows):,} rows dumped (out of {total_rows:,} total)")

def main():
    """Main export function"""
    print("="*80)
    print("📦 MSSQL Database Export")
    print("="*80)
    print()
    
    try:
        conn = get_connection()
        print(f"✅ Connected to database\n")
        
        # 1. Export schema
        print("1️⃣ Exporting database schema...")
        schema_file = os.path.join(OUTPUT_DIR, f"schema_{datetime.now().strftime('%Y%m%d_%H%M%S')}.sql")
        dump_schema(conn, schema_file)
        print()
        
        # 2. Export key table data (limited rows)
        print("2️⃣ Exporting table data (limited to 1000 rows per table)...")
        
        key_tables = [
            'lr_source_document',
            'lr_transaction',
            'lr_transaction_document',
            'lr_property',
            'lr_party',
            'lr_dictionary',
            'alf_node',
            'alf_node_properties',
            'alf_content_url',
            'alf_content_data',
            'alf_qname'
        ]
        
        data_file = os.path.join(OUTPUT_DIR, f"data_{datetime.now().strftime('%Y%m%d_%H%M%S')}.sql")
        
        with open(data_file, 'w', encoding='utf-8') as f:
            f.write(f"-- Database Data Export\n")
            f.write(f"-- Database: {DEFAULT_DB_CONFIG.get('database')}\n")
            f.write(f"-- Generated: {datetime.now().isoformat()}\n")
            f.write(f"-- ==========================================\n\n")
        
        for table in key_tables:
            table_data_file = os.path.join(OUTPUT_DIR, f"data_{table}.sql")
            try:
                dump_table_data(conn, table, table_data_file, limit=1000)
            except Exception as e:
                print(f"   ❌ {table}: Error - {e}")
        
        print()
        
        # 3. Export statistics
        print("3️⃣ Exporting database statistics...")
        stats_file = os.path.join(OUTPUT_DIR, f"statistics_{datetime.now().strftime('%Y%m%d_%H%M%S')}.txt")
        
        cursor = conn.cursor()
        
        with open(stats_file, 'w', encoding='utf-8') as f:
            f.write(f"Database Statistics\n")
            f.write(f"==================\n")
            f.write(f"Database: {DEFAULT_DB_CONFIG.get('database')}\n")
            f.write(f"Generated: {datetime.now().isoformat()}\n\n")
            
            # Table row counts
            cursor.execute("""
                SELECT 
                    t.name AS table_name,
                    SUM(p.rows) AS row_count
                FROM sys.tables t
                JOIN sys.schemas s ON t.schema_id = s.schema_id
                JOIN sys.partitions p ON t.object_id = p.object_id
                WHERE s.name = 'LRSAdmin'
                AND p.index_id IN (0, 1)
                GROUP BY t.name
                ORDER BY SUM(p.rows) DESC
            """)
            
            f.write("Table Row Counts:\n")
            f.write("-" * 60 + "\n")
            
            for table_name, row_count in cursor.fetchall():
                f.write(f"{table_name:<50} {row_count:>10,}\n")
        
        cursor.close()
        print(f"✅ Statistics exported to: {stats_file}")
        print()
        
        conn.close()
        
        print("="*80)
        print("✅ Export Complete!")
        print("="*80)
        print()
        print(f"📁 Output directory: {OUTPUT_DIR}")
        print(f"   - Schema: {schema_file}")
        print(f"   - Data: {OUTPUT_DIR}/data_*.sql")
        print(f"   - Statistics: {stats_file}")
        print()
        
    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback
        traceback.print_exc()
        return 1
    
    return 0

if __name__ == "__main__":
    sys.exit(main())