import os
import psycopg2
from dotenv import load_dotenv

load_dotenv()

def crear_tablas_face_recognition():
    try:
        conn = psycopg2.connect(
            host=os.getenv("DB_HOST"),
            port=os.getenv("DB_PORT"),
            dbname=os.getenv("DB_NAME"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD")
        )
        cursor = conn.cursor()
        cursor.execute("""
            CREATE SCHEMA IF NOT EXISTS face_recognition;

            CREATE TABLE IF NOT EXISTS face_recognition.auditoria (
                id SERIAL PRIMARY KEY,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                ip_cliente VARCHAR(50),
                metodo_http VARCHAR(10),
                url TEXT,
                headers JSONB,
                cuerpo TEXT,
                usuario TEXT,
                estado_respuesta INT,
                mensaje TEXT
            );

            CREATE TABLE IF NOT EXISTS face_recognition.consumo_tokens (
                id SERIAL PRIMARY KEY,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                modelo TEXT NOT NULL,
                tokens_utilizados INT NOT NULL,
                costo_aproximado NUMERIC(10, 4),
                cedula TEXT NOT NULL,
                endpoint TEXT,
                respuesta_modelo JSONB
            );

            CREATE TABLE IF NOT EXISTS face_recognition.resultado_prueba_vida (
                id SERIAL PRIMARY KEY,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                cedula TEXT NOT NULL,
                estado  varchar NOT NULL,
                tipo_error TEXT,
                video_base64 TEXT NOT NULL,
                resultado_json JSONB,
                consumo_tokens_id INT REFERENCES face_recognition.consumo_tokens(id) ON DELETE SET NULL
            );
        """)
        conn.commit()
        print("✅ Tablas creadas exitosamente (si no existían).")
    except Exception as e:
        print("❌ Error creando tablas:", e)
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    crear_tablas_face_recognition()