import sqlite3
import os
from werkzeug.security import generate_password_hash

DB_NAME = "data/config.db"

def get_db_connection():
    if not os.path.exists("data"):
        os.makedirs("data")
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Tabla de configuraciones de subdominios/slugs
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug TEXT UNIQUE NOT NULL,
            api_url TEXT NOT NULL,
            api_url_timbrada TEXT,
            api_url_pin TEXT,
            login_pin INTEGER DEFAULT 0,
            jwt TEXT NOT NULL,
            empresa_nombre TEXT NOT NULL,
            activo INTEGER DEFAULT 1
        )
    ''')
    
    # Asegurar que las columnas existen si la tabla ya fue creada previamente
    for col_sql in [
        'ALTER TABLE settings ADD COLUMN api_url_timbrada TEXT',
        'ALTER TABLE settings ADD COLUMN api_url_pin TEXT',
        'ALTER TABLE settings ADD COLUMN login_pin INTEGER DEFAULT 0',
    ]:
        try:
            cursor.execute(col_sql)
        except sqlite3.OperationalError:
            pass
    
    # Tabla de usuarios administradores
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL
        )
    ''')

    # Tabla de usuarios por subdominio (slug_users)
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS slug_users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug TEXT NOT NULL,
            username TEXT NOT NULL,
            password_hash TEXT NOT NULL,
            role TEXT NOT NULL,
            UNIQUE(slug, username)
        )
    ''')

    # Tabla de IPs permitidas por subdominio (allowed_ips)
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS allowed_ips (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug TEXT NOT NULL,
            ip_address TEXT NOT NULL,
            status TEXT DEFAULT 'pending',
            UNIQUE(slug, ip_address)
        )
    ''')    
    # Crear usuario administrador por defecto si no existe
    cursor.execute("SELECT * FROM users WHERE username = 'admin'")
    if not cursor.fetchone():
        # Clave proporcionada por el usuario: 2026@Fer
        h = generate_password_hash("2026@Fer")
        cursor.execute("INSERT INTO users (username, password_hash) VALUES (?, ?)", ("admin", h))
        print("✅ Usuario administrador creado por defecto (admin / 2026@Fer)")
        
    # Crear configuración inicial de fernandoi si no existe (basada en lo actual)
    cursor.execute("SELECT * FROM settings WHERE slug = 'fernandoi'")
    if not cursor.fetchone():
        cursor.execute('''
            INSERT INTO settings (slug, api_url, api_url_timbrada, jwt, empresa_nombre) 
            VALUES (?, ?, ?, ?, ?)
        ''', (
            "fernandoi", 
            "https://fernandoi.kapitalcompany.com/index.php?page=api_rrhh&action=colaboradores_activos",
            "https://fernandoi.kapitalcompany.com/index.php?page=api_rrhh&action=registrar_timbradas",
            "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpYXQiOjE3NzYzNjU0OTEsImRhdGEiOnsibmljayI6IkZlcm5hbmRvSSJ9fQ.pUN59ipY-C0mU1aYPsPcY-QuUDasRwMAv1DWmqmj7yI",
            "FernandoI"
        ))
        print("✅ Configuración inicial 'fernandoi' creada")
        
    conn.commit()
    conn.close()

if __name__ == "__main__":
    init_db()