"""
Database initialization script
Creates required tables for the application
Run this script to initialize the database
"""
import pymysql
import os
import logging
import hashlib
from app.config import Config

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def hash_password(password):
    """Hash password using SHA256"""
    return hashlib.sha256(password.encode()).hexdigest()


def create_database():
    """Create the database if it doesn't exist"""
    try:
        conn = pymysql.connect(
            user=Config.DB_USER,
            password=Config.DB_PASSWORD,
            host=Config.DB_HOST,
            port=Config.DB_PORT
        )
        cursor = conn.cursor()
        
        # Create database
        cursor.execute(f"CREATE DATABASE IF NOT EXISTS `{Config.DB_NAME}`")
        logger.info(f"Database {Config.DB_NAME} created or already exists")
        
        cursor.close()
        conn.close()
    except Exception as e:
        logger.error(f"Error creating database: {e}")
        raise


def create_tables():
    """Create application tables"""
    try:
        conn = pymysql.connect(
            user=Config.DB_USER,
            password=Config.DB_PASSWORD,
            host=Config.DB_HOST,
            port=Config.DB_PORT,
            database=Config.DB_NAME
        )
        cursor = conn.cursor()
        
        # Users table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) UNIQUE NOT NULL,
                email VARCHAR(255),
                full_name VARCHAR(255),
                role VARCHAR(50) DEFAULT 'user',
                is_active TINYINT(1) DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'users' created or already exists")
        
        # User credentials table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_credentials (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'user_credentials' created or already exists")
        
        # Quality inspections table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS quality_inspections (
                id INT AUTO_INCREMENT PRIMARY KEY,
                inspection_type VARCHAR(100),
                status VARCHAR(50),
                inspector_id INT,
                inspection_date DATETIME DEFAULT CURRENT_TIMESTAMP,
                notes TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (inspector_id) REFERENCES users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'quality_inspections' created or already exists")
        
        # Settings table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS application_settings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                setting_key VARCHAR(255) UNIQUE NOT NULL,
                setting_value LONGTEXT,
                setting_type VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'application_settings' created or already exists")
        
        # Roles table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS roles (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) UNIQUE NOT NULL,
                description TEXT,
                level INT DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'roles' created or already exists")
        
        # User modules (which modules a user has access to)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_modules (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                module_name VARCHAR(100) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY unique_user_module (user_id, module_name),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'user_modules' created or already exists")
        
        # User permissions (granular permissions)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS user_permissions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                module_name VARCHAR(100) NOT NULL,
                section_name VARCHAR(100) NOT NULL,
                action_name VARCHAR(100) NOT NULL,
                granted TINYINT(1) DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY unique_permission (user_id, module_name, section_name, action_name),
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'user_permissions' created or already exists")
        
        # Worker-Manager bindings (for warehouse module hierarchy)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS worker_manager_bindings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                manager_id INT NOT NULL,
                worker_id INT NOT NULL,
                warehouse_zone VARCHAR(100),
                is_active TINYINT(1) DEFAULT 1,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY unique_binding (manager_id, worker_id),
                FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (worker_id) REFERENCES users(id) ON DELETE CASCADE,
                CHECK (manager_id != worker_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        """)
        logger.info("Table 'worker_manager_bindings' created or already exists")
        
        conn.commit()
        cursor.close()
        conn.close()
        logger.info("All tables created successfully")
    except Exception as e:
        logger.error(f"Error creating tables: {e}")
        raise


def insert_default_user():
    """Insert default admin user and roles"""
    try:
        conn = pymysql.connect(
            user=Config.DB_USER,
            password=Config.DB_PASSWORD,
            host=Config.DB_HOST,
            port=Config.DB_PORT,
            database=Config.DB_NAME
        )
        cursor = conn.cursor()
        
        # Insert default roles if they don't exist
        roles = [
            ('superadmin', 'Super Administrator - Full system access', 100),
            ('admin', 'Administrator - Administrative access', 90),
            ('manager', 'Manager - Quality - Full access to assigned modules', 70),
            ('warehouse_manager', 'Manager - Warehouse - Full warehouse module access', 75),
            ('worker', 'Worker - Quality - Limited access', 50),
            ('warehouse_worker', 'Worker - Warehouse - Input-only warehouse access', 35),
        ]
        
        for role_name, role_desc, role_level in roles:
            cursor.execute(
                "SELECT id FROM roles WHERE name = %s",
                (role_name,)
            )
            if not cursor.fetchone():
                cursor.execute(
                    "INSERT INTO roles (name, description, level) VALUES (%s, %s, %s)",
                    (role_name, role_desc, role_level)
                )
                logger.info(f"Role '{role_name}' created")
        
        # Check if admin user exists
        cursor.execute("SELECT id FROM users WHERE username = 'admin'")
        admin_result = cursor.fetchone()
        
        if admin_result:
            logger.info("Admin user already exists")
            cursor.close()
            conn.close()
            return
        
        # Insert admin user
        cursor.execute("""
            INSERT INTO users (username, email, full_name, role, is_active)
            VALUES (%s, %s, %s, %s, 1)
        """, ('admin', 'admin@quality-app.local', 'Administrator', 'admin'))
        
        # Get admin user ID
        cursor.execute("SELECT id FROM users WHERE username = 'admin'")
        admin_id = cursor.fetchone()[0]
        
        # Insert admin password (default: admin123)
        password_hash = hash_password('admin123')
        cursor.execute("""
            INSERT INTO user_credentials (user_id, password_hash)
            VALUES (%s, %s)
        """, (admin_id, password_hash))
        
        # Grant admin user access to all modules
        modules = ['quality', 'settings']
        for module in modules:
            cursor.execute("""
                INSERT IGNORE INTO user_modules (user_id, module_name)
                VALUES (%s, %s)
            """, (admin_id, module))
        
        conn.commit()
        cursor.close()
        conn.close()
        
        logger.info("Default admin user created (username: admin, password: admin123)")
        logger.warning("IMPORTANT: Change the default admin password after first login!")
    except Exception as e:
        logger.error(f"Error inserting default user: {e}")
        raise


if __name__ == '__main__':
    logger.info("Starting database initialization...")
    
    try:
        create_database()
        create_tables()
        insert_default_user()
        logger.info("Database initialization completed successfully!")
    except Exception as e:
        logger.error(f"Database initialization failed: {e}")
        exit(1)