-- Base de datos: gestion_comisiones
-- Compatible con MySQL 5.7/8 y MariaDB 10.x
CREATE DATABASE IF NOT EXISTS `gestion_comisiones` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `gestion_comisiones`;

-- Roles
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT INTO roles (nombre) VALUES ('ADMIN'), ('ALCALDE'), ('EMPLEADO')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre);

-- Áreas
CREATE TABLE IF NOT EXISTS areas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(120) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL
) ENGINE=InnoDB;

-- Departamentos
CREATE TABLE IF NOT EXISTS departamentos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  area_id INT NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_dep_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- Las contraseñas hash anteriores son generadas con password_hash('admin123', PASSWORD_DEFAULT) y 'alcalde123'

-- Comisiones
CREATE TABLE IF NOT EXISTS comisiones (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titulo VARCHAR(180) NOT NULL,
  descripcion TEXT NULL,
  fecha_inicio DATE NOT NULL,
  fecha_fin DATE NOT NULL,
  area_id INT NULL,
  department_id INT NULL,
  estado ENUM('PENDIENTE','APROBADA','RECHAZADA') NOT NULL DEFAULT 'PENDIENTE',
  comentario_aprob TEXT NULL,
  creador_id INT NOT NULL,
  aprobador_id INT NULL,
  fecha_aprob DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_com_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE SET NULL,
  CONSTRAINT fk_com_dep FOREIGN KEY (department_id) REFERENCES departamentos(id) ON DELETE SET NULL,
  CONSTRAINT fk_com_creador FOREIGN KEY (creador_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_com_aprob FOREIGN KEY (aprobador_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Participantes
CREATE TABLE IF NOT EXISTS comision_participantes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  comision_id INT NOT NULL,
  usuario_id INT NOT NULL,
  CONSTRAINT fk_cp_com FOREIGN KEY (comision_id) REFERENCES comisiones(id) ON DELETE CASCADE,
  CONSTRAINT fk_cp_user FOREIGN KEY (usuario_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_com_user (comision_id, usuario_id)
) ENGINE=InnoDB;

-- Trazabilidad
CREATE TABLE IF NOT EXISTS trazabilidad (
  id INT AUTO_INCREMENT PRIMARY KEY,
  modulo VARCHAR(60) NOT NULL,
  accion VARCHAR(60) NOT NULL,
  usuario_id INT NOT NULL,
  ref_id INT NULL,
  detalle VARCHAR(255) NULL,
  fecha_hora DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tz_user FOREIGN KEY (usuario_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX (modulo), INDEX (accion), INDEX (ref_id)
) ENGINE=InnoDB;
