#!/usr/bin/env python3
"""
Database Schema Generator - vzug-e-hinge
=========================================
Creates and initializes the SQLite database schema.

Includes:
- Schema creation
- Initial data population
- Database connection management
- Size monitoring

Author: Kynsight
Version: 1.0.0
"""

import sqlite3
import os
import sys
from pathlib import Path
from datetime import datetime


# =============================================================================
# Configuration
# =============================================================================

DEFAULT_DB_PATH = "./database/ehinge.db"
MAX_DB_SIZE_BYTES = 2 * 1024 * 1024 * 1024  # 2 GB limit


# =============================================================================
# Database Schema
# =============================================================================

SCHEMA_SQL = """
-- =============================================================================
-- 1. EXISTING COMMAND TABLES
-- =============================================================================

CREATE TABLE IF NOT EXISTS "uart_commands" (
    "command_id" INTEGER,
    "command_name" TEXT NOT NULL UNIQUE,
    "description" TEXT,
    "category" TEXT,
    "hex_string" TEXT NOT NULL,
    "expected_response" TEXT,
    "timeout_ms" INTEGER,
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "is_active" BOOLEAN DEFAULT 1,
    PRIMARY KEY("command_id" AUTOINCREMENT)
);

CREATE TABLE IF NOT EXISTS "i2c_commands" (
    "command_id" INTEGER,
    "command_name" TEXT NOT NULL UNIQUE,
    "description" TEXT,
    "category" TEXT,
    "operation" TEXT NOT NULL,
    "register" TEXT NOT NULL,
    "hex_string" TEXT NOT NULL,
    "device_address" TEXT NOT NULL,
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "is_active" BOOLEAN DEFAULT 1,
    PRIMARY KEY("command_id" AUTOINCREMENT)
);

-- =============================================================================
-- 2. INTERFACE PROFILES
-- =============================================================================

CREATE TABLE IF NOT EXISTS "interface_profiles" (
    "profile_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "profile_name" TEXT UNIQUE NOT NULL,
    "description" TEXT,
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "last_modified" TIMESTAMP,
    
    -- UART Command Interface
    "uart_command_mode" TEXT,
    "uart_command_port" TEXT,
    "uart_command_baud" INTEGER,
    "uart_command_data_bits" INTEGER,
    "uart_command_stop_bits" INTEGER,
    "uart_command_parity" TEXT,
    "uart_command_timeout_ms" INTEGER,
    
    -- UART Logger Interface
    "uart_logger_enable" BOOLEAN DEFAULT 1,
    "uart_logger_mode" TEXT,
    "uart_logger_port" TEXT,
    "uart_logger_baud" INTEGER,
    "uart_logger_data_bits" INTEGER,
    "uart_logger_stop_bits" INTEGER,
    "uart_logger_parity" TEXT,
    "uart_logger_timeout_ms" INTEGER,
    "uart_logger_grace_ms" INTEGER,
    
    -- UART Logger Packet Detection
    "uart_logger_packet_detect_enable" BOOLEAN DEFAULT 0,
    "uart_logger_packet_detect_start" TEXT,
    "uart_logger_packet_detect_length" INTEGER,
    "uart_logger_packet_detect_end" TEXT,
    
    -- I2C Interface
    "i2c_port" TEXT,
    "i2c_slave_address" TEXT,
    "i2c_slave_read_register" TEXT,
    "i2c_slave_read_length" INTEGER
);

-- =============================================================================
-- 3. SESSION PROFILES
-- =============================================================================

CREATE TABLE IF NOT EXISTS "session_profiles" (
    "profile_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "profile_name" TEXT UNIQUE NOT NULL,
    "description" TEXT,
    "print_command_rx BOOLEAN DEFAULT 0;
    "command_sequence" TEXT,
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "last_modified" TIMESTAMP
);

-- =============================================================================
-- 4. SESSIONS
-- =============================================================================

CREATE TABLE IF NOT EXISTS "sessions" (
    "session_id" TEXT PRIMARY KEY,
    "session_name" TEXT NOT NULL,
    "session_date" TEXT NOT NULL,
    "description" TEXT,
    
    "interface_profile_id" INTEGER,
    "session_profile_id" INTEGER,
    
    "status" TEXT DEFAULT 'active',
    "total_runs" INTEGER DEFAULT 0,
    
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "ended_at" TIMESTAMP,
    
    FOREIGN KEY ("interface_profile_id") REFERENCES "interface_profiles"("profile_id"),
    FOREIGN KEY ("session_profile_id") REFERENCES "session_profiles"("profile_id")
);

-- =============================================================================
-- 5. TELEMETRY_RAW
-- =============================================================================

CREATE TABLE IF NOT EXISTS "telemetry_raw" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,

    "session_id" TEXT NOT NULL,
    "session_name" TEXT NOT NULL,
    "run_no" INTEGER NOT NULL,
    "run_command_id" INTEGER NOT NULL,

    "t_ns" INTEGER NOT NULL,
    "time_ms" REAL,

    "uart_raw_packet" BLOB,
    "i2c_raw_bytes" BLOB,
    "i2c_zero_ref" INTEGER DEFAULT 0,

    FOREIGN KEY ("session_id") REFERENCES "sessions"("session_id"),
    FOREIGN KEY ("run_command_id") REFERENCES "uart_commands"("command_id")
);

-- =============================================================================
-- 6. TELEMETRY_DECODED
-- =============================================================================

CREATE TABLE IF NOT EXISTS "telemetry_decoded" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,

    "session_id" TEXT NOT NULL,
    "session_name" TEXT NOT NULL,
    "run_no" INTEGER NOT NULL,
    "run_command_id" INTEGER NOT NULL,

    "t_ns" INTEGER NOT NULL,
    "time_ms" REAL,

    -- UART decoded
    "motor_current" INTEGER,
    "encoder_value" INTEGER,
    "relative_encoder_value" INTEGER,
    "v24_pec_diff" INTEGER,
    "pwm" INTEGER,

    -- I2C decoded
    "i2c_raw14" INTEGER,
    "i2c_zero_raw14" INTEGER,
    "i2c_delta_raw14" INTEGER,
    "i2c_angle_deg" REAL,
    "i2c_zero_angle_deg" REAL,
    "i2c_zero_ref" INTEGER DEFAULT 0,

    -- Derived
    "angular_velocity" REAL,
    "angular_acceleration" REAL,

    FOREIGN KEY ("session_id") REFERENCES "sessions"("session_id"),
    FOREIGN KEY ("run_command_id") REFERENCES "uart_commands"("command_id")
);

-- =============================================================================
-- 7. INDEXES
-- =============================================================================

CREATE INDEX IF NOT EXISTS "idx_telemetry_decoded_session" 
ON "telemetry_decoded" ("session_id", "run_no");

CREATE INDEX IF NOT EXISTS "idx_telemetry_raw_session" 
ON "telemetry_raw" ("session_id", "run_no");

CREATE INDEX IF NOT EXISTS "idx_telemetry_decoded_session_name" 
ON "telemetry_decoded" ("session_name", "run_no");

CREATE INDEX IF NOT EXISTS "idx_telemetry_decoded_time" 
ON "telemetry_decoded" ("t_ns");


-- ============================================
-- 8. GUI PROFILES
-- ============================================

CREATE TABLE IF NOT EXISTS gui_profiles (
    profile_id INTEGER PRIMARY KEY AUTOINCREMENT,
    profile_name TEXT UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Theme
    theme_name TEXT DEFAULT 'dark',
    primary_color TEXT DEFAULT '#1f77b4',
    background_color TEXT DEFAULT '#2e2e2e',
    text_color TEXT DEFAULT '#ffffff',
    
    -- Font
    font_family TEXT DEFAULT 'Arial',
    font_size INTEGER DEFAULT 10,
    
    -- Window
    window_width INTEGER DEFAULT 1280,
    window_height INTEGER DEFAULT 720
);

-- ============================================
-- 9. DATABASE METADATA
-- ============================================

CREATE TABLE IF NOT EXISTS database_metadata (
    key TEXT PRIMARY KEY,
    value TEXT
);
"""



# =============================================================================
# Initial Data
# =============================================================================

INITIAL_DATA_SQL = """
-- ============================================
-- initial data population
-- ============================================

insert or ignore into database_metadata (key, value) values 
    ('max_size_bytes', '2147483648'),
    ('created_at', datetime('now')),
    ('version', '1.0'),
    ('schema_version', '1.0.0');

-- default gui profile (dark theme)
insert or ignore into gui_profiles (
    profile_name, is_active, theme_name, 
    primary_color, background_color, text_color,
    font_family, font_size,
    window_width, window_height
) values (
    'dark theme', 1, 'dark',
    '#1f77b4', '#2e2e2e', '#ffffff',
    'arial', 10,
    1280, 720
);

-- uart commands (from aurt_commands.csv)
insert or ignore into uart_commands (command_name, description, category, hex_string) values
    ('query', 'query current action and feedback status', 'action', 'dd 22 50 48 02 41 52 09'),
    ('reset', 'reset current action state', 'action', 'dd 22 50 48 02 41 43 18'),
    ('open', 'open door fully', 'door', 'dd 22 50 48 02 43 4f 16'),
    ('close', 'close door fully', 'door', 'dd 22 50 48 02 43 43 1a'),
    ('partopen', 'open door partially', 'door', 'dd 22 50 48 02 43 47 1e'),
    ('ref drive', 'perform reference drive', 'door', 'dd 22 50 48 02 43 52 0b'),
    ('status', 'query basic error flags', 'error', 'dd 22 50 48 01 45 5c'),
    ('software version', 'get firmware/software version', 'info', 'dd 22 50 48 01 44 5d'),
    ('read', 'read extended error state', 'error', 'dd 22 50 48 02 45 52 0d'),
    ('clear', 'clear all active error flags', 'error', 'dd 22 50 48 02 45 43 1c'),
    ('init status', 'check initialization complete', 'status', 'dd 22 50 48 01 49 50'),
    ('motor status: query', 'get motor state (ready, driving, etc.)', 'status', 'dd 22 50 48 01 4d 54'),
    ('off', 'power down system in safe state', 'power', 'dd 22 50 48 01 4f 56'),
    ('power reset', 'power off and reset controller', 'power', 'dd 22 50 48 02 4f 72 27'),
    ('parameter start', 'start motor parameter upload process', 'motor', 'dd 22 50 48 04 50 73 74 61 2a'),
    ('parameter version: set', 'store current parameter version id', 'motor', 'dd 22 50 48 0c 51 31 32 33 34 35 36 37 2d 41 41 20 78'),
    ('parameter version: query', 'get stored parameter version', 'motor', 'dd 22 50 48 01 51 48'),
    ('temperature', 'get device internal temperature', 'sensor', 'dd 22 50 48 01 54 4d'),
    ('door switch', 'check if door is open/closed via sensor', 'sensor', 'dd 22 50 48 01 53 4a'),
    ('hardware version', 'get hardware revision info', 'info', 'dd 22 50 48 01 68 71'),
    ('manufacturer', 'get manufacturer identification', 'info', 'dd 22 50 48 01 69 70'),
    ('send id 02', 'write model identifier 02', 'identification', 'dd 22 50 48 03 6d 30 32 74'),
    ('send id 10', 'write model identifier 10', 'identification', 'dd 22 50 48 03 6d 31 30 77'),
    ('check', 'check if in programming/normal state', 'programming', 'dd 22 50 48 02 70 67 0d'),
    ('exit', 'exit programming state', 'programming', 'dd 22 50 48 04 70 73 30 30 1f'),
    ('production', 'enter production mode', 'mode', 'dd 22 50 48 02 74 73 1d'),
    ('mode reset', 'reset production mode settings', 'mode', 'dd 22 50 48 02 74 72 1c'),
    ('mode query', 'check if in production mode', 'mode', 'dd 22 50 48 01 74 6d'),
    ('logging mode', 'direct command for logging', 'test', 'dd 22 50 48 03 50 53 50 48'),
    ('read paramter set 01', 'reads motors paramter set 1', 'motor', 'dd 22 50 48 04 50 72 30 31 3f'),
    ('read paramter set 25', 'reads motors paramter set 25', 'motor', 'dd 22 50 48 04 50 72 32 35 39');

-- i2c commands (from i2c_commands.csv)
insert or ignore into i2c_commands (command_name, description, category, operation, register, hex_string, device_address) values
    ('read angle', '14-bit angle (msb at 0xfe, lsb at 0xff), 2 bytes', 'sensor', 'read', '0xfe', '02', '0x40'),
    ('read magnitude', 'cordic magnitude, 2 bytes (0xfc/0xfd)', 'sensor', 'read', '0xfc', '02', '0x40'),
    ('read diagnostics', 'diagnostic flags: ocf, cof, comp high/low (0xfb)', 'sensor', 'read', '0xfb', '01', '0x40'),
    ('read agc', 'automatic gain control value (0xfa)', 'sensor', 'read', '0xfa', '01', '0x40'),
    ('read prog control', 'programming control register (pe/verify/burn at 0x03)', 'system', 'read', '0x03', '01', '0x40'),
    ('read i2c address', 'i2c slave address bits (volatile reg 0x15)', 'system', 'read', '0x15', '01', '0x40'),
    ('read zeropos high', 'zero position high byte (reg 0x16)', 'sensor', 'read', '0x16', '01', '0x40'),
    ('read zeropos low', 'zero position low 6 bits (reg 0x17)', 'sensor', 'read', '0x17', '01', '0x40'),
    ('read angle msb', 'angle msb only (0xfe)', 'sensor', 'read', '0xfe', '01', '0x40'),
    ('read angle lsb', 'angle lsb only (0xff)', 'sensor', 'read', '0xff', '01', '0x40'),
    ('progctrl: pe=1', 'enable programming (bit0=1) — volatile', 'system', 'write', '0x03', '01', '0x40'),
    ('progctrl: pe=0', 'disable programming (bit0=0)', 'system', 'write', '0x03', '00', '0x40'),
    ('progctrl: verify=1', 'set verify bit (bit6=1) to reload otp to internal regs', 'system', 'write', '0x03', '40', '0x40'),
    ('progctrl: verify=0', 'clear verify bit (bit6=0)', 'system', 'write', '0x03', '00', '0x40'),
    ('zeropos: clear high', 'set zero position high byte to 0x00 (no burn)', 'sensor', 'write', '0x16', '00', '0x40'),
    ('zeropos: clear low', 'set zero position low 6 bits to 0x00 (no burn)', 'sensor', 'write', '0x17', '00', '0x40'),
    ('I2C Addr: Set Bits=0', 'Set I2C address bits in 0x15 to 0x00 (A1/A2 pins still apply)', 'System', 'write', '0x15', '00', '0x40');
"""



# =============================================================================
# Helper Functions
# =============================================================================

def create_database(db_path: str, overwrite: bool = False) -> bool:
    """
    Create and initialize database.
    
    Args:
        db_path: Path to database file
        overwrite: If True, delete existing database first
    
    Returns:
        True on success, False on failure
    """
    try:
        # Create directory if needed
        db_dir = os.path.dirname(db_path)
        if db_dir:
            os.makedirs(db_dir, exist_ok=True)
            print(f"[✓] Created directory: {db_dir}")
        
        # Overwrite if requested
        if overwrite and os.path.exists(db_path):
            os.remove(db_path)
            print(f"[✓] Removed existing database: {db_path}")
        
        # Connect to database
        conn = sqlite3.connect(db_path)
        conn.row_factory = sqlite3.Row
        print(f"[✓] Connected to database: {db_path}")
        
        # Enable foreign keys
        conn.execute("PRAGMA foreign_keys = ON")
        
        # Create schema
        print("[...] Creating schema...")
        conn.executescript(SCHEMA_SQL)
        
        conn.commit()
        print("[✓] Schema created successfully!")
        
        # Initalt Data
        print("[...] Intial Data Upload...")
        conn.executescript(INITIAL_DATA_SQL)
        
        print("[✓] Inital Data uploaded successfully!")

        # Get database info
        size_bytes = os.path.getsize(db_path)
        size_mb = size_bytes / (1024 * 1024)
        
        # Get table counts
        cursor = conn.cursor()
        tables = [
            'uart_commands',
            'i2c_commands',
            'interface_profiles',
            'session_profiles',
            'sessions',
            'telemetry_raw',
            'telemetry_decoded'
        ]
        
        print("\n" + "=" * 60)
        print("DATABASE INFORMATION")
        print("=" * 60)
        print(f"Path:     {db_path}")
        print(f"Size:     {size_mb:.2f} MB")
        print(f"\nTABLES:")
        
        for table in tables:
            try:
                cursor.execute(f"SELECT COUNT(*) FROM {table}")
                count = cursor.fetchone()[0]
                print(f"  {table:25s} {count:6d} rows")
            except:
                print(f"  {table:25s} ERROR")
        
        print("=" * 60)
        
        conn.close()
        return True
    
    except Exception as e:
        print(f"[✗] ERROR: {e}")
        return False


def check_database_health(db_path: str):
    """
    Check database health and integrity.
    
    Args:
        db_path: Path to database file
    """
    if not os.path.exists(db_path):
        print(f"[✗] Database not found: {db_path}")
        return
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check integrity
        cursor.execute("PRAGMA integrity_check")
        result = cursor.fetchone()[0]
        
        if result == "ok":
            print("[✓] Database integrity: OK")
        else:
            print(f"[✗] Database integrity: {result}")
        
        # Check foreign keys
        cursor.execute("PRAGMA foreign_key_check")
        fk_errors = cursor.fetchall()
        
        if not fk_errors:
            print("[✓] Foreign keys: OK")
        else:
            print(f"[✗] Foreign key errors found: {len(fk_errors)}")
        
        conn.close()
    
    except Exception as e:
        print(f"[✗] Health check failed: {e}")


# =============================================================================
# Database Manager Class
# =============================================================================

class DatabaseManager:
    """
    Database manager for vzug-e-hinge project.
    
    Provides a high-level interface for database operations:
    - Connection management
    - Schema initialization
    - Size monitoring
    - Health checking
    
    Usage:
        db = DatabaseManager("database/ehinge.db")
        db.initialize()
        conn = db.get_connection()
        # Use connection...
        db.close()
    """
    
    def __init__(self, db_path: str = DEFAULT_DB_PATH):
        """
        Initialize database manager.
        
        Args:
            db_path: Path to SQLite database file
        """
        self.db_path = db_path
        self.connection = None
    
    def initialize(self, overwrite: bool = False) -> bool:
        """
        Initialize database (create schema and tables).
        
        Args:
            overwrite: If True, delete and recreate database
        
        Returns:
            True if successful, False otherwise
        """
        success = create_database(self.db_path, overwrite)
        
        if success:
            # Open connection after initialization
            self.connect()
        
        return success
    
    def connect(self) -> bool:
        """
        Connect to database.
        
        Returns:
            True if successful, False otherwise
        """
        try:
            if self.connection:
                self.connection.close()
            
            self.connection = sqlite3.connect(self.db_path)
            
            # Enable foreign keys
            self.connection.execute("PRAGMA foreign_keys = ON")
            
            return True
            
        except Exception as e:
            print(f"Error connecting to database: {e}")
            return False
    
    def get_connection(self) -> sqlite3.Connection:
        """
        Get database connection.
        
        Opens connection if not already open.
        
        Returns:
            sqlite3.Connection object
        """
        if not self.connection:
            self.connect()
        
        return self.connection
    
    def close(self):
        """Close database connection."""
        if self.connection:
            self.connection.close()
            self.connection = None
    
    def check_size(self) -> tuple:
        """
        Check database size.
        
        Returns:
            Tuple of (size_bytes, percentage, status)
            - size_bytes: Database size in bytes
            - percentage: Percentage of MAX_DB_SIZE_BYTES
            - status: 'ok', 'warning', or 'critical'
        """
        try:
            size_bytes = os.path.getsize(self.db_path)
            percentage = (size_bytes / MAX_DB_SIZE_BYTES) * 100
            
            if percentage < 50:
                status = 'ok'
            elif percentage < 80:
                status = 'warning'
            else:
                status = 'critical'
            
            return (size_bytes, percentage, status)
            
        except Exception as e:
            print(f"Error checking database size: {e}")
            return (0, 0, 'error')
    
    def vacuum(self):
        """
        Vacuum database (reclaim unused space).
        
        This can significantly reduce database size after deletions.
        """
        try:
            conn = self.get_connection()
            conn.execute("VACUUM")
            conn.commit()
            print("Database vacuumed successfully")
        except Exception as e:
            print(f"Error vacuuming database: {e}")
    
    def check_health(self):
        """Run database health check."""
        check_database_health(self.db_path)
    
    def get_table_list(self) -> list:
        """
        Get list of all tables in database.
        
        Returns:
            List of table names
        """
        try:
            conn = self.get_connection()
            cursor = conn.execute("""
                SELECT name FROM sqlite_master 
                WHERE type='table' 
                ORDER BY name
            """)
            return [row[0] for row in cursor.fetchall()]
        except Exception as e:
            print(f"Error getting table list: {e}")
            return []
    
    def get_table_info(self, table_name: str) -> list:
        """
        Get column information for a table.
        
        Args:
            table_name: Name of table
        
        Returns:
            List of tuples with column info (cid, name, type, notnull, dflt_value, pk)
        """
        try:
            conn = self.get_connection()
            cursor = conn.execute(f"PRAGMA table_info({table_name})")
            return cursor.fetchall()
        except Exception as e:
            print(f"Error getting table info: {e}")
            return []
    
    def get_row_count(self, table_name: str) -> int:
        """
        Get number of rows in a table.
        
        Args:
            table_name: Name of table
        
        Returns:
            Number of rows
        """
        try:
            conn = self.get_connection()
            cursor = conn.execute(f"SELECT COUNT(*) FROM {table_name}")
            return cursor.fetchone()[0]
        except Exception as e:
            print(f"Error getting row count: {e}")
            return 0
    
    def __enter__(self):
        """Context manager entry."""
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        """Context manager exit."""
        self.close()
    
    def __del__(self):
        """Destructor - ensure connection is closed."""
        self.close()


# =============================================================================
# Main
# =============================================================================

def main():
    """Main entry point."""
    import argparse
    
    parser = argparse.ArgumentParser(
        description='Initialize vzug-e-hinge database'
    )
    parser.add_argument(
        '--path',
        default=DEFAULT_DB_PATH,
        help=f'Database path (default: {DEFAULT_DB_PATH})'
    )
    parser.add_argument(
        '--overwrite',
        action='store_true',
        help='Delete and recreate database'
    )
    parser.add_argument(
        '--check',
        action='store_true',
        help='Check database health'
    )
    
    args = parser.parse_args()
    
    print("=" * 60)
    print("vzug-e-hinge Database Initialization")
    print("=" * 60)
    print()
    
    if args.check:
        check_database_health(args.path)
        return
    
    # Create database
    success = create_database(args.path, args.overwrite)
    
    if success:
        print("\n[✓] Database initialization complete!")
        print(f"\nYou can now use the database at: {args.path}")
        sys.exit(0)
    else:
        print("\n[✗] Database initialization failed!")
        sys.exit(1)


if __name__ == "__main__":
    main()
    
    # Example usage of DatabaseManager:
    # 
    # from database.init_database import DatabaseManager
    # 
    # # Create manager
    # db = DatabaseManager("database/ehinge.db")
    # 
    # # Initialize database
    # db.initialize()
    # 
    # # Get connection
    # conn = db.get_connection()
    # 
    # # Use connection
    # cursor = conn.execute("SELECT * FROM sessions")
    # 
    # # Check size
    # size, percentage, status = db.check_size()
    # print(f"Database size: {size} bytes ({percentage:.1f}%)")
    # 
    # # Close when done
    # db.close()
    # 
    # # Or use context manager:
    # with DatabaseManager("database/ehinge.db") as db:
    #     conn = db.get_connection()
    #     # Use connection...
    #     # Automatically closed when exiting context
