-- ============================================================ -- 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;