-- ============================================================
--  GESTOR DOCUMENTAL — FONDO DOCUMENTAL SGC
--  Modelo de datos completo
--
--  Convenciones: snake_case, InnoDB, utf8mb4, PDO/prepared
--  statements en la capa de aplicación (igual que el resto de
--  tus sistemas).
-- ============================================================

-- Fuerza utf8mb4 para ESTA sesión de importación, sin importar el
-- charset por defecto del cliente que ejecute el script (algunos
-- clientes mysql CLI usan latin1 por defecto y corrompen tildes/ñ
-- si no se declara esto explícitamente).
SET NAMES utf8mb4;


-- ────────────────────────────────────────────────────────────
--  1. USUARIOS Y SEGURIDAD DE SESIÓN
-- ────────────────────────────────────────────────────────────

-- Cuentas del sistema. El registro público (página de "Crear
-- cuenta") SIEMPRE inserta rol='cargador' — los roles revisor/
-- admin solo los crea un admin ya autenticado, nunca el propio
-- usuario. Esa regla vive en la capa de aplicación, no aquí.
CREATE TABLE usuarios (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre_completo    VARCHAR(150) NOT NULL,
    correo             VARCHAR(150) NOT NULL,
    password_hash      VARCHAR(255) NOT NULL,   -- password_hash() con PASSWORD_BCRYPT o ARGON2ID
    rol                ENUM('cargador','revisor','admin') NOT NULL DEFAULT 'cargador',
    activo             TINYINT(1) NOT NULL DEFAULT 1,
    intentos_fallidos  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    bloqueado_hasta    DATETIME NULL,           -- lockout temporal tras varios intentos fallidos
    ultimo_login       DATETIME NULL,
    creado_en          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_usuarios_correo (correo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "Recordarme" seguro: patrón selector/validador (NUNCA guardar
-- el token de la cookie directo en BD). El selector identifica
-- la fila sin filtrar el secreto; el validador se compara con
-- hash. Si se compromete la BD, no sirve para suplantar sesión.
CREATE TABLE sesiones_recordarme (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id     INT UNSIGNED NOT NULL,
    selector       VARCHAR(24)  NOT NULL,
    validador_hash VARCHAR(255) NOT NULL,
    expira_en      DATETIME NOT NULL,
    creado_en      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sesiones_recordarme_selector (selector),
    CONSTRAINT fk_sr_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Recuperación de contraseña: mismo patrón (nunca el token plano).
CREATE TABLE password_resets (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT UNSIGNED NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expira_en  DATETIME NOT NULL,
    usado      TINYINT(1) NOT NULL DEFAULT 0,
    creado_en  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pr_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ────────────────────────────────────────────────────────────
--  2. TITULARES DE DERECHOS
-- ────────────────────────────────────────────────────────────

-- Personas/entidades que participan en una obra (autor, editor,
-- productor, etc). NO requieren cuenta de usuario — un coautor
-- que nunca entra a la plataforma igual se registra aquí con
-- sus datos básicos (para el split sheet, por ejemplo).
-- usuario_id queda NULL si el titular no tiene login propio.
CREATE TABLE titulares (
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    tipo_persona       ENUM('natural','juridica') NOT NULL,
    nombres_completos  VARCHAR(200) NOT NULL,
    identificacion     VARCHAR(30)  NOT NULL,   -- cédula o NIT
    correo             VARCHAR(150) NULL,
    celular            VARCHAR(20)  NULL,
    direccion          VARCHAR(255) NULL,
    es_causahabiente   TINYINT(1) NOT NULL DEFAULT 0,
    usuario_id         INT UNSIGNED NULL,
    creado_en          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_titulares_identificacion (identificacion),
    CONSTRAINT fk_titular_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ────────────────────────────────────────────────────────────
--  3. OBRAS
-- ────────────────────────────────────────────────────────────

CREATE TABLE obras (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    titulo         VARCHAR(255) NOT NULL,
    estado         ENUM('borrador','finalizado') NOT NULL DEFAULT 'borrador',
    creado_por     INT UNSIGNED NOT NULL,
    creado_en      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finalizado_en  DATETIME NULL,
    CONSTRAINT fk_obra_creador FOREIGN KEY (creado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Relación obra↔titular con el rol específico que esa persona
-- tiene EN esa obra (la misma persona puede ser autor en una obra
-- e intérprete en otra, o ambos roles en la misma obra — por eso
-- el UNIQUE incluye el rol, no solo obra+titular).
CREATE TABLE obra_titulares (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    obra_id             INT NOT NULL,
    titular_id          INT NOT NULL,
    rol                 ENUM('autor','compositor','interprete','ejecutante',
                              'productor_fonografico','editor','arreglista') NOT NULL,
    porcentaje_reparto  DECIMAL(5,2) NULL,  -- % de participación (split sheet); NULL si el rol no reparte
    creado_en           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_obra_titular_rol (obra_id, titular_id, rol),
    CONSTRAINT fk_ot_obra     FOREIGN KEY (obra_id)    REFERENCES obras(id),
    CONSTRAINT fk_ot_titular  FOREIGN KEY (titular_id) REFERENCES titulares(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ────────────────────────────────────────────────────────────
--  4. CATÁLOGO DE DOCUMENTOS REQUERIDOS (los 20 del fondo
--     documental) — editable desde el portal por el admin,
--     sin tocar código.
-- ────────────────────────────────────────────────────────────

-- condicion_campo/condicion_valor son un mecanismo simple de
-- "esto solo aplica si...": ej. condicion_campo='tipo_persona',
-- condicion_valor='juridica' para el Certificado de Existencia.
-- Si condicion_campo es NULL, el documento aplica siempre a
-- toda entidad de tipo aplica_a.
CREATE TABLE documentos_requeridos (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    nombre            VARCHAR(150) NOT NULL,
    descripcion       TEXT NULL,
    aplica_a          ENUM('obra','titular','relacion') NOT NULL,
    condicion_campo   VARCHAR(50) NULL,
    condicion_valor   VARCHAR(50) NULL,
    obligatorio       TINYINT(1) NOT NULL DEFAULT 1,   -- bloquea pasar de borrador a finalizado si falta
    requiere_revision TINYINT(1) NOT NULL DEFAULT 0,   -- si no, "cargado" = "cumplido" automático
    activo            TINYINT(1) NOT NULL DEFAULT 1,
    orden             SMALLINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Carga inicial: los 20 documentos del fondo documental, con la
-- clasificación acordada. Ajusta obligatorio/requiere_revision
-- según necesites — quedó como punto de partida razonable.
INSERT INTO documentos_requeridos (nombre, aplica_a, condicion_campo, condicion_valor, obligatorio, requiere_revision, orden) VALUES
('Título de la obra',                              'obra',     NULL,             NULL,          1, 0, 1),
('Nombre autor compositor',                         'relacion', 'rol',            'autor',       1, 0, 2),
('Nombre arreglistas/intérpretes/ejecutantes',      'relacion', 'rol',            'interprete',  0, 0, 3),
('Letra de la obra musical fijada',                 'obra',     NULL,             NULL,          1, 1, 4),
('Guion melódico',                                  'obra',     NULL,             NULL,          0, 0, 5),
('Contrato de edición o cesión de derechos',        'relacion', 'rol',            'editor',      1, 1, 6),
('Formulario de ingreso',                           'titular',  NULL,             NULL,          1, 0, 7),
('Contrato mandato',                                'titular',  NULL,             NULL,          1, 1, 8),
('Carátulas o láminas originales',                  'obra',     NULL,             NULL,          1, 0, 9),
('RUT',                                             'titular',  NULL,             NULL,          0, 0, 10),
('Acta de sucesión para causahabientes',            'titular',  'es_causahabiente','1',          0, 1, 11),
('Registro ante la DNDA',                           'obra',     NULL,             NULL,          1, 1, 12),
('Metadata',                                        'obra',     NULL,             NULL,          1, 0, 13),
('Código ISWC/ISRC',                                'obra',     NULL,             NULL,          1, 0, 14),
('Código IPI/IPN',                                  'titular',  NULL,             NULL,          1, 0, 15),
('Contrato de inclusión en fonograma',              'relacion', 'rol',            'productor_fonografico', 1, 1, 16),
('Autorización previa y expresa para arreglos',     'relacion', 'rol',            'arreglista',  1, 1, 17),
('Audio legítimamente fijado (MP3)',                'obra',     NULL,             NULL,          1, 0, 18),
('Split sheet / clave de reparto',                  'relacion', NULL,             NULL,          1, 1, 19),
('Registro de obras y fonogramas',                  'obra',     NULL,             NULL,          0, 1, 20),
('Certificado de existencia y representación legal','titular',  'tipo_persona',   'juridica',    1, 1, 21);


-- ────────────────────────────────────────────────────────────
--  5. DOCUMENTOS CARGADOS
-- ────────────────────────────────────────────────────────────

-- entidad_tipo + entidad_id son polimórficos: apuntan a
-- obras.id, titulares.id, u obra_titulares.id según lo que
-- diga documentos_requeridos.aplica_a para esa fila.
-- Sin FK real por ser polimórfico — se valida en la app.
--
-- tipo_carga distingue archivo real vs texto pegado en textarea
-- (ej. metadata, códigos ISWC/ISRC). Un documento_requerido
-- puede tener VARIOS registros (múltiples archivos permitidos,
-- máx. 5MB cada uno — ese límite se valida en PHP, no en BD).
CREATE TABLE documentos (
    id                        INT AUTO_INCREMENT PRIMARY KEY,
    documento_requerido_id    INT NOT NULL,
    entidad_tipo              ENUM('obra','titular','relacion') NOT NULL,
    entidad_id                INT NOT NULL,
    tipo_carga                ENUM('archivo','texto') NOT NULL,
    archivo_path              VARCHAR(255) NULL,
    archivo_nombre_original   VARCHAR(255) NULL,
    archivo_peso_bytes        INT UNSIGNED NULL,
    contenido_texto           MEDIUMTEXT NULL,
    estado                    ENUM('cumplido','pendiente_revision','aprobado','rechazado') NOT NULL,
    motivo_rechazo            VARCHAR(255) NULL,
    cargado_por               INT UNSIGNED NOT NULL,
    revisado_por              INT UNSIGNED NULL,
    fecha_carga               DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_revision            DATETIME NULL,
    KEY idx_documentos_entidad (documento_requerido_id, entidad_tipo, entidad_id),
    KEY idx_documentos_estado (estado),
    CONSTRAINT fk_doc_requerido FOREIGN KEY (documento_requerido_id) REFERENCES documentos_requeridos(id),
    CONSTRAINT fk_doc_cargador  FOREIGN KEY (cargado_por)  REFERENCES usuarios(id),
    CONSTRAINT fk_doc_revisor   FOREIGN KEY (revisado_por) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
