CREATE DATABASE IF NOT EXISTS certifica_institucional CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE certifica_institucional;
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS validation_logs,certificate_status_history,certificates,imports,template_signatories,signatories,certificate_templates,activity_participants,activities,activity_types,people,audit_logs,users,role_permissions,permissions,roles,branches,institutions;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE institutions(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,legal_name VARCHAR(200),tax_id VARCHAR(30),email VARCHAR(160),phone VARCHAR(40),address VARCHAR(250),logo_path VARCHAR(255),primary_color VARCHAR(20) DEFAULT '#173B63',secondary_color VARCHAR(20) DEFAULT '#B48B3C',status ENUM('active','inactive') DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL) ENGINE=InnoDB;
CREATE TABLE branches(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT NOT NULL,name VARCHAR(160) NOT NULL,code VARCHAR(30),address VARCHAR(250),status ENUM('active','inactive') DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(institution_id) REFERENCES institutions(id)) ENGINE=InnoDB;
CREATE TABLE roles(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,slug VARCHAR(80) NOT NULL UNIQUE,description VARCHAR(250));
CREATE TABLE permissions(id INT AUTO_INCREMENT PRIMARY KEY,module VARCHAR(60) NOT NULL,name VARCHAR(120) NOT NULL,slug VARCHAR(120) NOT NULL UNIQUE);
CREATE TABLE role_permissions(role_id INT NOT NULL,permission_id INT NOT NULL,PRIMARY KEY(role_id,permission_id),FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE);
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT,branch_id INT,role_id INT NOT NULL,name VARCHAR(160) NOT NULL,email VARCHAR(160) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,status ENUM('active','inactive','blocked') DEFAULT 'active',last_login_at DATETIME NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(institution_id) REFERENCES institutions(id),FOREIGN KEY(branch_id) REFERENCES branches(id),FOREIGN KEY(role_id) REFERENCES roles(id));
CREATE TABLE people(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT NOT NULL,document_type VARCHAR(30) DEFAULT 'DNI',document_number VARCHAR(40) NOT NULL,first_names VARCHAR(160) NOT NULL,last_names VARCHAR(160) NOT NULL,email VARCHAR(160),phone VARCHAR(40),organization VARCHAR(180),position_name VARCHAR(160),department VARCHAR(100),province VARCHAR(100),district VARCHAR(100),status ENUM('active','inactive') DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,UNIQUE KEY uq_people_doc(institution_id,document_number),FOREIGN KEY(institution_id) REFERENCES institutions(id)) ENGINE=InnoDB;
CREATE TABLE activity_types(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,slug VARCHAR(80) NOT NULL UNIQUE,status ENUM('active','inactive') DEFAULT 'active');
CREATE TABLE activities(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT NOT NULL,activity_type_id INT NOT NULL,branch_id INT NULL,name VARCHAR(220) NOT NULL,code VARCHAR(60),description TEXT,start_date DATE,end_date DATE,hours DECIMAL(8,2),modality VARCHAR(40) DEFAULT 'Presencial',location VARCHAR(220),requires_grade TINYINT(1) DEFAULT 0,min_grade DECIMAL(8,2),grade_max DECIMAL(8,2) DEFAULT 20,requires_attendance TINYINT(1) DEFAULT 0,min_attendance DECIMAL(8,2),status ENUM('active','closed','cancelled') DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,FOREIGN KEY(institution_id) REFERENCES institutions(id),FOREIGN KEY(activity_type_id) REFERENCES activity_types(id),FOREIGN KEY(branch_id) REFERENCES branches(id)) ENGINE=InnoDB;
CREATE TABLE activity_participants(id INT AUTO_INCREMENT PRIMARY KEY,activity_id INT NOT NULL,person_id INT NOT NULL,grade DECIMAL(8,2) NULL,attendance DECIMAL(8,2) NULL,status ENUM('eligible','not_eligible','withdrawn') DEFAULT 'eligible',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,UNIQUE KEY uq_activity_person(activity_id,person_id),FOREIGN KEY(activity_id) REFERENCES activities(id) ON DELETE CASCADE,FOREIGN KEY(person_id) REFERENCES people(id)) ENGINE=InnoDB;
CREATE TABLE imports(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT NOT NULL,activity_id INT NOT NULL,filename VARCHAR(255),total_rows INT DEFAULT 0,valid_rows INT DEFAULT 0,error_rows INT DEFAULT 0,imported_by INT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(institution_id) REFERENCES institutions(id),FOREIGN KEY(activity_id) REFERENCES activities(id),FOREIGN KEY(imported_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE certificate_templates(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT NOT NULL,name VARCHAR(160) NOT NULL,document_type VARCHAR(100) DEFAULT 'Certificado',html_body LONGTEXT,background_path VARCHAR(255),orientation VARCHAR(20) DEFAULT 'landscape',status ENUM('active','inactive') DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NULL,FOREIGN KEY(institution_id) REFERENCES institutions(id)) ENGINE=InnoDB;
CREATE TABLE signatories(id INT AUTO_INCREMENT PRIMARY KEY,institution_id INT NOT NULL,name VARCHAR(160) NOT NULL,position_name VARCHAR(160) NOT NULL,signature_path VARCHAR(255),valid_from DATE,valid_to DATE,status ENUM('active','inactive') DEFAULT 'active',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(institution_id) REFERENCES institutions(id)) ENGINE=InnoDB;
CREATE TABLE template_signatories(template_id INT NOT NULL,signatory_id INT NOT NULL,position_order INT DEFAULT 1,PRIMARY KEY(template_id,signatory_id),FOREIGN KEY(template_id) REFERENCES certificate_templates(id) ON DELETE CASCADE,FOREIGN KEY(signatory_id) REFERENCES signatories(id) ON DELETE CASCADE);
CREATE TABLE certificates(id INT AUTO_INCREMENT PRIMARY KEY,uuid CHAR(32) NOT NULL UNIQUE,institution_id INT NOT NULL,branch_id INT NULL,activity_id INT NOT NULL,person_id INT NOT NULL,participant_id INT NOT NULL,template_id INT NULL,template_html_snapshot LONGTEXT,signatories_snapshot LONGTEXT,code VARCHAR(50) UNIQUE NULL,verification_token VARCHAR(80) NOT NULL UNIQUE,status ENUM('generated','issued','revoked','replaced') DEFAULT 'generated',generated_at DATETIME NULL,issued_at DATETIME NULL,file_path VARCHAR(255),hash_sha256 CHAR(64),replaces_certificate_id INT NULL,created_by INT NULL,issued_by INT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(institution_id) REFERENCES institutions(id),FOREIGN KEY(branch_id) REFERENCES branches(id),FOREIGN KEY(activity_id) REFERENCES activities(id),FOREIGN KEY(person_id) REFERENCES people(id),FOREIGN KEY(participant_id) REFERENCES activity_participants(id),FOREIGN KEY(template_id) REFERENCES certificate_templates(id),FOREIGN KEY(created_by) REFERENCES users(id),FOREIGN KEY(issued_by) REFERENCES users(id),FOREIGN KEY(replaces_certificate_id) REFERENCES certificates(id)) ENGINE=InnoDB;
CREATE TABLE certificate_status_history(id INT AUTO_INCREMENT PRIMARY KEY,certificate_id INT NOT NULL,status VARCHAR(40) NOT NULL,reason VARCHAR(500),user_id INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(certificate_id) REFERENCES certificates(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE audit_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,institution_id INT NULL,action VARCHAR(120) NOT NULL,description VARCHAR(500),entity_type VARCHAR(100),entity_id BIGINT,ip_address VARCHAR(64),user_agent VARCHAR(255),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_audit_inst(institution_id,created_at),FOREIGN KEY(user_id) REFERENCES users(id),FOREIGN KEY(institution_id) REFERENCES institutions(id)) ENGINE=InnoDB;
CREATE TABLE validation_logs(id BIGINT AUTO_INCREMENT PRIMARY KEY,certificate_id INT,query_value VARCHAR(120),ip_address VARCHAR(64),user_agent VARCHAR(255),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_validation_cert(certificate_id,created_at),FOREIGN KEY(certificate_id) REFERENCES certificates(id)) ENGINE=InnoDB;
INSERT INTO institutions(id,name,legal_name,tax_id,email,phone,address,primary_color,secondary_color) VALUES(1,'Institución Demo','Institución Educativa Demo S.A.C.','20123456789','contacto@institucion.pe','(01) 000-0000','Lima, Perú','#173B63','#B48B3C');
INSERT INTO branches(id,institution_id,name,code,address) VALUES(1,1,'Sede Principal','LIM-01','Lima, Perú');
INSERT INTO roles(id,name,slug,description) VALUES(1,'Superadministrador','superadmin','Control total del sistema'),(2,'Administrador institucional','admin','Gestión integral de su institución'),(3,'Responsable de certificación','certifier','Genera, revisa y emite certificados'),(4,'Operador','operator','Registra personas, participantes e importaciones'),(5,'Auditor / Consulta','auditor','Acceso de lectura y auditoría');
INSERT INTO permissions(module,name,slug) VALUES
('personas','Ver personas','people.view'),('personas','Crear personas','people.create'),('personas','Editar personas','people.edit'),
('actividades','Ver actividades','activities.view'),('actividades','Crear actividades','activities.create'),('actividades','Editar actividades','activities.edit'),
('participantes','Ver participantes','participants.view'),('participantes','Agregar participantes','participants.create'),('importaciones','Importar Excel','imports.create'),
('certificados','Ver certificados','certificates.view'),('certificados','Generar certificados','certificates.generate'),('certificados','Emitir certificados','certificates.issue'),('certificados','Anular certificados','certificates.revoke'),
('plantillas','Administrar plantillas','templates.manage'),('firmantes','Administrar firmantes','signers.manage'),('usuarios','Administrar usuarios','users.manage'),('roles','Administrar roles','roles.manage'),('reportes','Ver reportes','reports.view'),('auditoria','Ver auditoría','audit.view'),('configuracion','Administrar configuración','settings.manage');
INSERT INTO role_permissions(role_id,permission_id) SELECT 2,id FROM permissions;
INSERT INTO role_permissions(role_id,permission_id) SELECT 3,id FROM permissions WHERE slug IN('people.view','activities.view','participants.view','participants.create','imports.create','certificates.view','certificates.generate','certificates.issue','certificates.revoke','reports.view');
INSERT INTO role_permissions(role_id,permission_id) SELECT 4,id FROM permissions WHERE slug IN('people.view','people.create','people.edit','activities.view','participants.view','participants.create','imports.create','certificates.view');
INSERT INTO role_permissions(role_id,permission_id) SELECT 5,id FROM permissions WHERE slug IN('people.view','activities.view','participants.view','certificates.view','reports.view','audit.view');
INSERT INTO users(id,institution_id,branch_id,role_id,name,email,password_hash,status,created_at) VALUES(1,1,NULL,1,'Administrador General','admin@certifica.local','$2y$12$egRTQ9PQBTte0dY8NUOIZ..4hSiXczCX.S.6dyoihNFi.4cAQNhEi','active',NOW());
INSERT INTO activity_types(name,slug) VALUES('Curso','curso'),('Taller','taller'),('Seminario','seminario'),('Webinar','webinar'),('Conferencia','conferencia'),('Programa','programa'),('Diplomado','diplomado'),('Capacitación','capacitacion'),('Evento','evento'),('Reconocimiento','reconocimiento'),('Constancia','constancia'),('Acreditación','acreditacion');
INSERT INTO certificate_templates(id,institution_id,name,document_type,html_body,status) VALUES(1,1,'Certificado corporativo','Certificado','<style>body{font-family:dejavusans;color:#13243a}.cert{height:185mm;border:8px solid #173b63;padding:13mm;text-align:center;box-sizing:border-box}.eyebrow{letter-spacing:2px;color:#b48b3c;font-size:12px}.title{font-size:34px;font-weight:700;margin:10px 0}.name{font-size:28px;font-weight:700;color:#173b63;margin:15px 0}.body{font-size:16px;line-height:1.7}.qr{margin-top:10px}.qr svg{width:75px;height:75px}.code{font-size:11px;color:#56677a}</style><div class="cert"><div class="eyebrow">{{institucion}}</div><div class="title">CERTIFICADO</div><div class="body">Se otorga el presente a</div><div class="name">{{nombre_completo}}</div><div class="body">por su participación en <strong>{{actividad}}</strong><br>con una duración de {{horas}} horas.</div><div class="qr">{{qr}}</div><div class="code">Código: {{codigo_certificado}}</div><div style="margin-top:10px">{{firmas}}</div></div>','active');