-- ============================================================
-- Personal Salud - Base de datos
-- Sistema Intranet - Salud Malloa
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `personal_salud`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `personal_salud`;

-- ------------------------------------------------------------
-- Tabla: roles
-- ------------------------------------------------------------
CREATE TABLE `roles` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre`      VARCHAR(50)  NOT NULL UNIQUE,
  `descripcion` VARCHAR(255) DEFAULT NULL,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `roles` (`nombre`, `descripcion`) VALUES
  ('administrador', 'Acceso total al sistema'),
  ('usuario',       'Acceso estándar de lectura y subida de documentos');

-- ------------------------------------------------------------
-- Tabla: usuarios
-- ------------------------------------------------------------
CREATE TABLE `usuarios` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email`        VARCHAR(255) NOT NULL UNIQUE,
  `nombre`       VARCHAR(150) DEFAULT NULL,
  `rol_id`       INT UNSIGNED NOT NULL DEFAULT 2,
  `activo`       TINYINT(1)   NOT NULL DEFAULT 1,
  `ultimo_login` TIMESTAMP    NULL DEFAULT NULL,
  `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_usuario_rol` (`rol_id`),
  CONSTRAINT `fk_usuario_rol` FOREIGN KEY (`rol_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Tabla: departamentos
-- ------------------------------------------------------------
CREATE TABLE `departamentos` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre`      VARCHAR(150) NOT NULL UNIQUE,
  `descripcion` TEXT         DEFAULT NULL,
  `activo`      TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `departamentos` (`nombre`) VALUES
  ('Dirección Comunal'),
  ('Finanzas'),
  ('Adquisiciones'),
  ('CESFAM Malloa'),
  ('CESFAM Pelequén'),
  ('Posta Rural de Corcolén'),
  ('Recursos Humanos'),
  ('Informática');

-- ------------------------------------------------------------
-- Tabla: categorias
-- ------------------------------------------------------------
CREATE TABLE `categorias` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nombre`      VARCHAR(100) NOT NULL UNIQUE,
  `descripcion` VARCHAR(255) DEFAULT NULL,
  `activo`      TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `categorias` (`nombre`) VALUES
  ('Oficio'),
  ('Acto Administrativo'),
  ('Resolución'),
  ('Circular'),
  ('Memorándum'),
  ('Respaldo'),
  ('Contrato'),
  ('Otros');

-- ------------------------------------------------------------
-- Tabla: documentos
-- ------------------------------------------------------------
CREATE TABLE `documentos` (
  `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `titulo`           VARCHAR(255)  NOT NULL,
  `descripcion`      TEXT          DEFAULT NULL,
  `departamento_id`  INT UNSIGNED  NOT NULL,
  `categoria_id`     INT UNSIGNED  NOT NULL,
  `usuario_id`       INT UNSIGNED  NOT NULL,
  `nombre_archivo`   VARCHAR(255)  NOT NULL,
  `nombre_original`  VARCHAR(255)  NOT NULL,
  `mime_type`        VARCHAR(100)  NOT NULL,
  `tamano`           BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `fecha_subida`     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `activo`           TINYINT(1)    NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `fk_doc_departamento` (`departamento_id`),
  KEY `fk_doc_categoria`    (`categoria_id`),
  KEY `fk_doc_usuario`      (`usuario_id`),
  KEY `idx_fecha_subida`    (`fecha_subida`),
  CONSTRAINT `fk_doc_departamento` FOREIGN KEY (`departamento_id`) REFERENCES `departamentos` (`id`),
  CONSTRAINT `fk_doc_categoria`    FOREIGN KEY (`categoria_id`)    REFERENCES `categorias`    (`id`),
  CONSTRAINT `fk_doc_usuario`      FOREIGN KEY (`usuario_id`)      REFERENCES `usuarios`      (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Tabla: comunicados
-- ------------------------------------------------------------
CREATE TABLE `comunicados` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `titulo`          VARCHAR(255) NOT NULL,
  `contenido`       TEXT         NOT NULL,
  `usuario_id`      INT UNSIGNED NOT NULL,
  `fecha_pub`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `activo`          TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `fk_com_usuario` (`usuario_id`),
  CONSTRAINT `fk_com_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Tabla pivote: comunicados_centros (destinatarios)
-- ------------------------------------------------------------
CREATE TABLE `comunicados_centros` (
  `comunicado_id`  INT UNSIGNED NOT NULL,
  `departamento_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`comunicado_id`, `departamento_id`),
  CONSTRAINT `fk_cc_comunicado`   FOREIGN KEY (`comunicado_id`)   REFERENCES `comunicados`   (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cc_departamento` FOREIGN KEY (`departamento_id`) REFERENCES `departamentos` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------
-- Tabla: sesiones_log (auditoría)
-- ------------------------------------------------------------
CREATE TABLE `sesiones_log` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` INT UNSIGNED    NOT NULL,
  `ip`         VARCHAR(45)     NOT NULL,
  `accion`     VARCHAR(50)     NOT NULL,
  `detalle`    VARCHAR(255)    DEFAULT NULL,
  `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_sl_usuario` (`usuario_id`),
  CONSTRAINT `fk_sl_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;