#!/usr/bin/env python3
"""
One-time script: Ensure auth tables exist and create/update admin6004 with password admin123.
Run from dashboard-backend: python ensure_auth_and_admin.py
"""
import os
import sys
import pymysql
from pymysql.cursors import DictCursor

# Load env and config
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from dotenv import load_dotenv
load_dotenv()

DB_HOST = os.getenv('DB_HOST', '127.0.0.1')
DB_PORT = int(os.getenv('DB_PORT', 3306))
DB_USER = os.getenv('DB_USER', 'admin')
DB_PASSWORD = os.getenv('DB_PASSWORD', '')
DB_NAME = os.getenv('DB_NAME', 'voicebot_cluster')

# Use bcrypt like auth_handler
import bcrypt
def hash_password(password):
    salt = bcrypt.gensalt()
    return bcrypt.hashpw(password.encode('utf-8'), salt).decode('utf-8')

def main():
    print("Connecting to database...")
    conn = pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASSWORD,
        database=DB_NAME,
        charset='utf8mb4',
        cursorclass=DictCursor,
    )
    cursor = conn.cursor()

    # 1. Create businesses if not exists
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS businesses (
            bid VARCHAR(20) PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            is_active TINYINT(1) DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    print("  businesses table OK")

    # 2. Create business_users if not exists
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS business_users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(100) UNIQUE NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            password_hash VARCHAR(255),
            plain_password VARCHAR(255),
            full_name VARCHAR(255),
            role VARCHAR(20) DEFAULT 'user',
            is_master TINYINT(1) DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            last_login TIMESTAMP NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_username (username),
            INDEX idx_email (email)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    print("  business_users table OK")

    # 3. Create user_business_access if not exists
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS user_business_access (
            user_id INT NOT NULL,
            bid VARCHAR(20) NOT NULL,
            role VARCHAR(20) DEFAULT 'user',
            PRIMARY KEY (user_id, bid),
            FOREIGN KEY (user_id) REFERENCES business_users(id) ON DELETE CASCADE,
            FOREIGN KEY (bid) REFERENCES businesses(bid) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    print("  user_business_access table OK")

    # 4. Create user_activity_log if not exists
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS user_activity_log (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT,
            username VARCHAR(100),
            activity_type VARCHAR(50),
            description TEXT,
            ip_address VARCHAR(45),
            user_agent TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_id (user_id),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    """)
    print("  user_activity_log table OK")

    conn.commit()

    # 5. Ensure business 6004 exists
    cursor.execute("SELECT bid FROM businesses WHERE bid = %s", ('6004',))
    if not cursor.fetchone():
        cursor.execute(
            "INSERT INTO businesses (bid, name, description, is_active) VALUES (%s, %s, %s, TRUE)",
            ('6004', 'Business 6004', 'MCUBE Sales',)
        )
        conn.commit()
        print("  Inserted business 6004")
    else:
        print("  Business 6004 exists")

    # 6. Create or update admin6004 with password admin123
    username = 'admin6004'
    email = 'admin6004@local'
    password = 'admin123'
    password_hash = hash_password(password)

    cursor.execute(
        "SELECT id, username FROM business_users WHERE username = %s",
        (username,)
    )
    row = cursor.fetchone()

    if row:
        cursor.execute(
            "UPDATE business_users SET password_hash = %s, plain_password = %s, is_active = TRUE WHERE id = %s",
            (password_hash, password, row['id'])
        )
        user_id = row['id']
        print(f"  Updated user {username} (id={user_id}) with password admin123")
    else:
        cursor.execute(
            """INSERT INTO business_users
            (username, email, password_hash, plain_password, full_name, role, is_master, is_active)
            VALUES (%s, %s, %s, %s, %s, %s, %s, TRUE)""",
            (username, email, password_hash, password, 'Admin 6004', 'admin', False)
        )
        user_id = cursor.lastrowid
        conn.commit()
        print(f"  Created user {username} (id={user_id}) with password admin123")

    # 7. Ensure user has access to business 6004
    cursor.execute(
        "SELECT 1 FROM user_business_access WHERE user_id = %s AND bid = %s",
        (user_id, '6004')
    )
    if not cursor.fetchone():
        cursor.execute(
            "INSERT INTO user_business_access (user_id, bid, role) VALUES (%s, %s, 'admin')",
            (user_id, '6004')
        )
        conn.commit()
        print("  Assigned admin6004 to business 6004 as admin")
    else:
        print("  admin6004 already has access to business 6004")

    conn.close()
    print("\nDone. You can log in with username: admin6004, password: admin123")

if __name__ == '__main__':
    main()