import sqlite3
import os
import json
from config import BASE_DIR

DB_PATH = os.path.join(BASE_DIR, 'database', 'deploy_manager.db')

def get_db_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Servers table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS servers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        hostname TEXT NOT NULL,
        ip TEXT NOT NULL,
        ipv6 TEXT,
        ssh_port INTEGER DEFAULT 22,
        ssh_user TEXT DEFAULT 'root',
        auth_type TEXT DEFAULT 'key',
        ssh_key_path TEXT,
        password TEXT,
        status TEXT DEFAULT 'unknown',
        os_info TEXT DEFAULT 'Ubuntu Linux',
        cpu_cores INTEGER DEFAULT 2,
        total_ram_gb REAL DEFAULT 8.0,
        used_ram_gb REAL DEFAULT 0.0,
        total_disk_gb REAL DEFAULT 100.0,
        used_disk_gb REAL DEFAULT 0.0,
        cpu_usage_pct REAL DEFAULT 0.0,
        uptime_str TEXT DEFAULT 'Unknown',
        listening_ports_json TEXT,
        last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')
    
    # Applications table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS applications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        server_id INTEGER NOT NULL,
        name TEXT NOT NULL,
        access_type TEXT DEFAULT 'ip_direct',
        current_access_url TEXT NOT NULL,
        active_domain TEXT,
        detected_alias TEXT,
        external_port INTEGER DEFAULT 80,
        internal_port INTEGER NOT NULL,
        bind_ip TEXT DEFAULT '0.0.0.0',
        path TEXT NOT NULL,
        framework TEXT DEFAULT 'Python Flask',
        entrypoint_file TEXT DEFAULT 'app.py',
        entrypoint_object TEXT DEFAULT 'app',
        entrypoint_cmd TEXT,
        service_name TEXT,
        nginx_usage TEXT DEFAULT 'Nao utilizado',
        status TEXT DEFAULT 'online',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE
    )
    ''')

    # Deployments table with sha256_hash and stored_zip_path
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS deployments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        application_id INTEGER NOT NULL,
        release_number INTEGER DEFAULT 1,
        version TEXT NOT NULL,
        description TEXT,
        zip_filename TEXT,
        stored_zip_path TEXT,
        sha256_hash TEXT,
        backup_path TEXT,
        status TEXT DEFAULT 'success',
        duration_seconds REAL DEFAULT 0.0,
        logs TEXT,
        diagnostic_json TEXT,
        is_active INTEGER DEFAULT 0,
        deployed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
    )
    ''')

    cursor.execute("PRAGMA table_info(deployments)")
    dep_cols = [c[1] for c in cursor.fetchall()]
    if 'release_number' not in dep_cols:
        cursor.execute("ALTER TABLE deployments ADD COLUMN release_number INTEGER DEFAULT 1")
    if 'stored_zip_path' not in dep_cols:
        cursor.execute("ALTER TABLE deployments ADD COLUMN stored_zip_path TEXT")
    if 'sha256_hash' not in dep_cols:
        cursor.execute("ALTER TABLE deployments ADD COLUMN sha256_hash TEXT")
    if 'diagnostic_json' not in dep_cols:
        cursor.execute("ALTER TABLE deployments ADD COLUMN diagnostic_json TEXT")
    if 'is_active' not in dep_cols:
        cursor.execute("ALTER TABLE deployments ADD COLUMN is_active INTEGER DEFAULT 0")

    # Database configs table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS database_configs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        server_id INTEGER NOT NULL,
        application_id INTEGER,
        db_type TEXT DEFAULT 'postgresql',
        name TEXT NOT NULL,
        user TEXT DEFAULT 'postgres',
        password TEXT,
        host TEXT DEFAULT 'localhost',
        port INTEGER DEFAULT 5432,
        size_mb REAL DEFAULT 0.0,
        last_backup_at TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (server_id) REFERENCES servers(id) ON DELETE CASCADE,
        FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE SET NULL
    )
    ''')

    # Backups table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS backups (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        application_id INTEGER,
        database_id INTEGER,
        backup_type TEXT DEFAULT 'app',
        file_name TEXT NOT NULL,
        file_size_mb REAL DEFAULT 0.0,
        remote_path TEXT,
        local_path TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
        FOREIGN KEY (database_id) REFERENCES database_configs(id) ON DELETE CASCADE
    )
    ''')
    
    conn.commit()
    conn.close()
