CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','juridico','consulta') NOT NULL DEFAULT 'juridico',
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legal_queries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    question TEXT NOT NULL,
    facts LONGTEXT NULL,
    organization VARCHAR(255) NULL,
    person_status VARCHAR(190) NULL,
    requested_output ENUM('respuesta','informe','oficio') NOT NULL DEFAULT 'oficio',
    status ENUM('borrador','analizando','analizado','revisado','cerrado','error') NOT NULL DEFAULT 'borrador',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_legal_queries_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legal_analyses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    legal_query_id BIGINT UNSIGNED NOT NULL,
    model VARCHAR(80) NOT NULL,
    response_id VARCHAR(150) NULL,
    analysis_text LONGTEXT NOT NULL,
    raw_response LONGTEXT NULL,
    confidence ENUM('alto','medio','bajo','insuficiente') NOT NULL DEFAULT 'medio',
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_analyses_query FOREIGN KEY (legal_query_id) REFERENCES legal_queries(id) ON DELETE CASCADE,
    CONSTRAINT fk_analyses_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analysis_sources (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    legal_analysis_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(500) NULL,
    url TEXT NOT NULL,
    domain VARCHAR(190) NOT NULL,
    source_type ENUM('dictamen','ley','reglamento','resolucion','instructivo','otro') NOT NULL DEFAULT 'otro',
    official_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sources_analysis FOREIGN KEY (legal_analysis_id) REFERENCES legal_analyses(id) ON DELETE CASCADE,
    INDEX idx_sources_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    metadata LONGTEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_entity (entity_type, entity_id),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;