import sqlite3
import os
import json
from config import Config
from database import get_db_connection

class Server:
    @staticmethod
    def get_all():
        conn = get_db_connection()
        servers = conn.execute('SELECT * FROM servers ORDER BY created_at DESC').fetchall()
        
        result = []
        for s in servers:
            srv = dict(s)
            apps = conn.execute('SELECT * FROM applications WHERE server_id = ? ORDER BY id DESC', (s['id'],)).fetchall()
            srv['apps'] = [dict(a) for a in apps]
            
            total_ram = srv.get('ram_total_gb', 8.0) or 8.0
            used_ram = srv.get('ram_used_gb', 0.0) or 0.0
            srv['ram_percent'] = round((used_ram / total_ram) * 100, 1) if total_ram > 0 else 0.0
            
            total_disk = srv.get('disk_total_gb', 100.0) or 100.0
            used_disk = srv.get('disk_used_gb', 0.0) or 0.0
            srv['disk_percent'] = round((used_disk / total_disk) * 100, 1) if total_disk > 0 else 0.0
            
            result.append(srv)
            
        conn.close()
        return result

    @staticmethod
    def get_by_id(server_id):
        conn = get_db_connection()
        server = conn.execute('SELECT * FROM servers WHERE id = ?', (server_id,)).fetchone()
        if not server:
            conn.close()
            return None
            
        srv = dict(server)
        apps = conn.execute('SELECT * FROM applications WHERE server_id = ? ORDER BY id DESC', (server_id,)).fetchall()
        
        apps_list = []
        for a in apps:
            app_dict = dict(a)
            deps = conn.execute('SELECT * FROM deployments WHERE application_id = ? ORDER BY id DESC', (a['id'],)).fetchall()
            deps_list = []
            for d in deps:
                d_dict = dict(d)
                if d_dict.get('diagnostic_json'):
                    try:
                        d_dict['diagnostic'] = json.loads(d_dict['diagnostic_json'])
                    except Exception:
                        d_dict['diagnostic'] = None
                else:
                    d_dict['diagnostic'] = None
                deps_list.append(d_dict)

            app_dict['deployments'] = deps_list
            
            bkups = conn.execute('SELECT * FROM backups WHERE application_id = ? ORDER BY created_at DESC', (a['id'],)).fetchall()
            app_dict['backups'] = [dict(b) for b in bkups]

            db = conn.execute('SELECT * FROM database_configs WHERE application_id = ?', (a['id'],)).fetchone()
            app_dict['database'] = dict(db) if db else None

            apps_list.append(app_dict)
            
        srv['applications'] = apps_list
        srv['apps'] = apps_list
        conn.close()
        return srv

    @staticmethod
    def create(name, hostname, ip, ipv6=None, ssh_port=22, ssh_user='root', auth_type='key', password=None, os_info='Ubuntu 24.04 LTS'):
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO servers (name, hostname, ip, ipv6, ssh_port, ssh_user, auth_type, password, os_info)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (name, hostname, ip, ipv6, ssh_port, ssh_user, auth_type, password, os_info))
        conn.commit()
        sid = cursor.lastrowid
        conn.close()
        return sid

    @staticmethod
    def update_metrics(server_id, cpu_usage, ram_used, ram_total, disk_used, disk_total, status='online'):
        conn = get_db_connection()
        conn.execute('''
        UPDATE servers
        SET cpu_percent = ?, ram_used_gb = ?, ram_total_gb = ?, disk_used_gb = ?, disk_total_gb = ?, status = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
        ''', (cpu_usage, ram_used, ram_total, disk_used, disk_total, status, server_id))
        conn.commit()
        conn.close()

class Application:
    @staticmethod
    def get_by_id(app_id):
        conn = get_db_connection()
        app = conn.execute('SELECT * FROM applications WHERE id = ?', (app_id,)).fetchone()
        if not app:
            conn.close()
            return None
        
        a_dict = dict(app)
        server = conn.execute('SELECT * FROM servers WHERE id = ?', (a_dict['server_id'],)).fetchone()
        a_dict['server'] = dict(server) if server else None
        
        deps = conn.execute('SELECT * FROM deployments WHERE application_id = ? ORDER BY id DESC', (app_id,)).fetchall()
        deps_list = []
        for d in deps:
            d_dict = dict(d)
            if d_dict.get('diagnostic_json'):
                try:
                    d_dict['diagnostic'] = json.loads(d_dict['diagnostic_json'])
                except Exception:
                    d_dict['diagnostic'] = None
            else:
                d_dict['diagnostic'] = None
            deps_list.append(d_dict)

        a_dict['deployments'] = deps_list
        
        bkups = conn.execute('SELECT * FROM backups WHERE application_id = ? ORDER BY created_at DESC', (app_id,)).fetchall()
        a_dict['backups'] = [dict(b) for b in bkups]

        db = conn.execute('SELECT * FROM database_configs WHERE application_id = ?', (app_id,)).fetchone()
        a_dict['database'] = dict(db) if db else None

        conn.close()
        return a_dict

    @staticmethod
    def create(server_id, name, internal_port, path, access_type=None, current_access_url=None, active_domain=None, detected_alias=None, external_port=None, bind_ip='0.0.0.0', framework='Python Flask', entrypoint_file='app.py', entrypoint_object='app', entrypoint_cmd=None, service_name=None, nginx_usage='Nao utilizado', domain=None, **kwargs):
        if domain and not active_domain:
            active_domain = domain
            access_type = 'nginx_proxy'
            current_access_url = f"https://{domain}"
            external_port = 443
            nginx_usage = f"Nginx Reverse Proxy (:443 ➔ :{internal_port})"
        
        if not current_access_url:
            access_type = access_type or 'ip_direct'
            external_port = external_port or internal_port
            current_access_url = f"http://31.97.147.114:{internal_port}"

        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO applications (
            server_id, name, access_type, current_access_url, active_domain, detected_alias,
            external_port, internal_port, bind_ip, path, framework, entrypoint_file, entrypoint_object, entrypoint_cmd, service_name, nginx_usage
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (server_id, name, access_type, current_access_url, active_domain, detected_alias, external_port, internal_port, bind_ip, path, framework, entrypoint_file, entrypoint_object, entrypoint_cmd, service_name, nginx_usage))
        conn.commit()
        aid = cursor.lastrowid
        conn.close()
        return aid

    @staticmethod
    def update_entrypoint(app_id, entrypoint_file, entrypoint_object, entrypoint_cmd=None):
        conn = get_db_connection()
        conn.execute('''
        UPDATE applications
        SET entrypoint_file = ?, entrypoint_object = ?, entrypoint_cmd = ?
        WHERE id = ?
        ''', (entrypoint_file, entrypoint_object, entrypoint_cmd, app_id))
        conn.commit()
        conn.close()

    @staticmethod
    def update_status(app_id, status):
        conn = get_db_connection()
        conn.execute('UPDATE applications SET status = ? WHERE id = ?', (status, app_id))
        conn.commit()
        conn.close()

    @staticmethod
    def delete(app_id):
        conn = get_db_connection()
        conn.execute('DELETE FROM applications WHERE id = ?', (app_id,))
        conn.commit()
        conn.close()

class Deployment:
    @staticmethod
    def create(application_id, version, description, zip_filename=None, backup_path=None, status='success', duration=0.0, duration_seconds=None, logs='', diagnostic=None, diagnostic_data=None, is_active=0, stored_zip_path=None, sha256_hash=None, **kwargs):
        conn = get_db_connection()
        cursor = conn.cursor()
        
        row = conn.execute('SELECT MAX(release_number) as max_rel FROM deployments WHERE application_id = ?', (application_id,)).fetchone()
        next_rel = (row['max_rel'] or 0) + 1 if row else 1
        
        effective_duration = duration_seconds if duration_seconds is not None else duration
        effective_diag = diagnostic if diagnostic is not None else diagnostic_data
        effective_zip_name = zip_filename or (os.path.basename(stored_zip_path) if stored_zip_path else f"release_{version}.zip")

        diag_json = json.dumps(effective_diag) if effective_diag else None

        if is_active == 1:
            conn.execute('UPDATE deployments SET is_active = 0 WHERE application_id = ?', (application_id,))

        cursor.execute('''
        INSERT INTO deployments (
            application_id, release_number, version, description, zip_filename, stored_zip_path, sha256_hash, backup_path, status, duration_seconds, logs, diagnostic_json, is_active
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (application_id, next_rel, version, description, effective_zip_name, stored_zip_path, sha256_hash, backup_path, status, effective_duration, logs, diag_json, is_active))
        conn.commit()
        did = cursor.lastrowid
        conn.close()
        return did

    @staticmethod
    def set_active_release(application_id, deployment_id):
        conn = get_db_connection()
        conn.execute('UPDATE deployments SET is_active = 0 WHERE application_id = ?', (application_id,))
        conn.execute('UPDATE deployments SET is_active = 1 WHERE id = ?', (deployment_id,))
        conn.commit()
        conn.close()

class DatabaseConfig:
    @staticmethod
    def create(server_id, application_id, db_type, name, user, password, host='localhost', port=5432, size_mb=0.0):
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO database_configs (server_id, application_id, db_type, name, user, password, host, port, size_mb)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (server_id, application_id, db_type, name, user, password, host, port, size_mb))
        conn.commit()
        dbid = cursor.lastrowid
        conn.close()
        return dbid

class Backup:
    @staticmethod
    def create(application_id, database_id=None, backup_type='snapshot', file_name='', file_size_mb=0.0, remote_path=None, local_path=None):
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute('''
        INSERT INTO backups (application_id, database_id, backup_type, file_name, file_size_mb, remote_path, local_path)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (application_id, database_id, backup_type, file_name, file_size_mb, remote_path, local_path))
        conn.commit()
        bid = cursor.lastrowid
        conn.close()
        return bid
