-- ============================================================
--  SISTEMA DE CONTROL DE ASISTENCIA DIGITAL
--  Esquema de base de datos + datos de ejemplo (MySQL 8+)
-- ============================================================

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

-- Reinicio limpio (respeta el orden por llaves foráneas)
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS asistencia;
DROP TABLE IF EXISTS permisos;
DROP TABLE IF EXISTS vacaciones;
DROP TABLE IF EXISTS usuarios;
DROP TABLE IF EXISTS empleados;
DROP TABLE IF EXISTS cargos;
DROP TABLE IF EXISTS turnos;
DROP TABLE IF EXISTS departamentos;
SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- Departamentos / Áreas
-- ------------------------------------------------------------
CREATE TABLE departamentos (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  nombre       VARCHAR(100) NOT NULL,
  descripcion  VARCHAR(255),
  ubicacion    VARCHAR(120),
  activo       TINYINT(1) NOT NULL DEFAULT 1,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Cargos / Puestos
-- ------------------------------------------------------------
CREATE TABLE cargos (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  nombre          VARCHAR(100) NOT NULL,
  departamento_id INT,
  salario_base    DECIMAL(10,2) DEFAULT 0,
  activo          TINYINT(1) NOT NULL DEFAULT 1,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (departamento_id) REFERENCES departamentos(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Turnos / Horarios de trabajo
-- ------------------------------------------------------------
CREATE TABLE turnos (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  nombre         VARCHAR(80) NOT NULL,
  hora_entrada   TIME NOT NULL,
  hora_salida    TIME NOT NULL,
  tolerancia_min INT NOT NULL DEFAULT 10,   -- minutos de tolerancia antes de marcar tardanza
  dias_laborales VARCHAR(40) DEFAULT 'Lun-Vie',
  activo         TINYINT(1) NOT NULL DEFAULT 1,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Empleados
-- ------------------------------------------------------------
CREATE TABLE empleados (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  codigo            VARCHAR(20) NOT NULL UNIQUE,
  nombres           VARCHAR(100) NOT NULL,
  apellidos         VARCHAR(100) NOT NULL,
  dni               VARCHAR(20) UNIQUE,
  email             VARCHAR(120),
  telefono          VARCHAR(30),
  direccion         VARCHAR(200),
  fecha_nacimiento  DATE,
  fecha_ingreso     DATE NOT NULL,
  departamento_id   INT,
  cargo_id          INT,
  turno_id          INT,
  foto              VARCHAR(255),
  estado            ENUM('activo','inactivo','vacaciones','licencia') NOT NULL DEFAULT 'activo',
  created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (departamento_id) REFERENCES departamentos(id) ON DELETE SET NULL,
  FOREIGN KEY (cargo_id)        REFERENCES cargos(id)        ON DELETE SET NULL,
  FOREIGN KEY (turno_id)        REFERENCES turnos(id)        ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Usuarios del sistema (acceso / login)
-- ------------------------------------------------------------
CREATE TABLE usuarios (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  empleado_id   INT,
  nombre        VARCHAR(120) NOT NULL,
  username      VARCHAR(60) NOT NULL UNIQUE,
  email         VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  rol           ENUM('admin','rrhh','empleado') NOT NULL DEFAULT 'empleado',
  activo        TINYINT(1) NOT NULL DEFAULT 1,
  ultimo_acceso TIMESTAMP NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Registro de Asistencia (marcaje diario)
-- ------------------------------------------------------------
CREATE TABLE asistencia (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  empleado_id      INT NOT NULL,
  fecha            DATE NOT NULL,
  hora_entrada     TIME,
  hora_salida      TIME,
  estado           ENUM('presente','tardanza','falta','permiso','vacaciones') NOT NULL DEFAULT 'presente',
  horas_trabajadas DECIMAL(5,2) DEFAULT 0,
  minutos_tardanza INT DEFAULT 0,
  observacion      VARCHAR(255),
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_empleado_fecha (empleado_id, fecha),
  FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Permisos / Licencias
-- ------------------------------------------------------------
CREATE TABLE permisos (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  empleado_id  INT NOT NULL,
  tipo         ENUM('personal','medico','familiar','estudios','otro') NOT NULL DEFAULT 'personal',
  fecha_inicio DATE NOT NULL,
  fecha_fin    DATE NOT NULL,
  motivo       VARCHAR(255),
  estado       ENUM('pendiente','aprobado','rechazado') NOT NULL DEFAULT 'pendiente',
  aprobado_por INT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (empleado_id)  REFERENCES empleados(id) ON DELETE CASCADE,
  FOREIGN KEY (aprobado_por) REFERENCES usuarios(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Vacaciones
-- ------------------------------------------------------------
CREATE TABLE vacaciones (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  empleado_id  INT NOT NULL,
  fecha_inicio DATE NOT NULL,
  fecha_fin    DATE NOT NULL,
  dias         INT NOT NULL,
  periodo      VARCHAR(9),
  estado       ENUM('pendiente','aprobado','rechazado','gozado') NOT NULL DEFAULT 'pendiente',
  aprobado_por INT,
  observacion  VARCHAR(255),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (empleado_id)  REFERENCES empleados(id) ON DELETE CASCADE,
  FOREIGN KEY (aprobado_por) REFERENCES usuarios(id)  ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
--  DATOS DE EJEMPLO
-- ============================================================

INSERT INTO departamentos (nombre, descripcion, ubicacion) VALUES
  ('Gerencia',        'Dirección y gestión general',        'Piso 5'),
  ('Recursos Humanos','Gestión del personal y planillas',   'Piso 4'),
  ('Tecnología',      'Desarrollo y soporte de sistemas',   'Piso 3'),
  ('Ventas',          'Atención comercial y clientes',      'Piso 2'),
  ('Contabilidad',    'Finanzas, tesorería y contabilidad', 'Piso 4');

INSERT INTO cargos (nombre, departamento_id, salario_base) VALUES
  ('Gerente General',      1, 12000.00),
  ('Jefe de RR.HH.',       2, 7500.00),
  ('Analista de RR.HH.',   2, 4200.00),
  ('Líder de Desarrollo',  3, 8500.00),
  ('Desarrollador',        3, 5500.00),
  ('Soporte Técnico',      3, 3800.00),
  ('Ejecutivo de Ventas',  4, 3500.00),
  ('Contador',             5, 6000.00);

INSERT INTO turnos (nombre, hora_entrada, hora_salida, tolerancia_min, dias_laborales) VALUES
  ('Turno Mañana',   '08:00:00', '17:00:00', 10, 'Lun-Vie'),
  ('Turno Tarde',    '13:00:00', '22:00:00', 10, 'Lun-Vie'),
  ('Turno Comercial','09:00:00', '18:00:00', 15, 'Lun-Sab');

INSERT INTO empleados
  (codigo, nombres, apellidos, dni, email, telefono, direccion, fecha_nacimiento, fecha_ingreso, departamento_id, cargo_id, turno_id, estado) VALUES
  ('EMP-001','Carlos','Ramírez Soto','40123456','carlos.ramirez@empresa.com','987654321','Av. Central 123',   '1980-03-15','2018-01-10',1,1,1,'activo'),
  ('EMP-002','María','Fernández Díaz','41234567','maria.fernandez@empresa.com','987111222','Jr. Los Olivos 45','1985-07-22','2019-05-03',2,2,1,'activo'),
  ('EMP-003','Lucía','Gómez Vera','42345678','lucia.gomez@empresa.com','987333444','Calle Sol 78',           '1992-11-08','2021-02-15',2,3,1,'activo'),
  ('EMP-004','Jorge','Torres Luna','43456789','jorge.torres@empresa.com','987555666','Av. Primavera 900',     '1988-01-30','2020-08-20',3,4,1,'activo'),
  ('EMP-005','Andrea','Castro Ríos','44567890','andrea.castro@empresa.com','987777888','Jr. Amazonas 12',     '1995-06-12','2022-03-01',3,5,1,'activo'),
  ('EMP-006','Pedro','Salas Mora','45678901','pedro.salas@empresa.com','987999000','Calle Luna 34',           '1998-09-25','2023-01-09',3,6,2,'activo'),
  ('EMP-007','Rosa','Núñez Paz','46789012','rosa.nunez@empresa.com','986123123','Av. Grau 560',               '1993-04-18','2021-11-11',4,7,3,'activo'),
  ('EMP-008','Luis','Vargas Rojas','47890123','luis.vargas@empresa.com','986456456','Jr. Junín 88',           '1990-12-05','2019-09-30',5,8,1,'activo');

-- Usuarios del sistema.
-- Contraseñas: admin -> admin123 | rrhh -> rrhh123 | resto -> emp123
INSERT INTO usuarios (empleado_id, nombre, username, email, password_hash, rol) VALUES
  (1,'Carlos Ramírez','admin','carlos.ramirez@empresa.com','$2b$10$kuJfMMJ1cHPx1T1gZn9XiuNN/5eXfVO65xgnywv/z2.hwtVddiTqm','admin'),
  (2,'María Fernández','rrhh','maria.fernandez@empresa.com','$2b$10$ObL0JwaJ5gRjuvkF2hsLkOV8U0j2kxK48jX.wm8SKa1ROJKaN2g3i','rrhh'),
  (5,'Andrea Castro','andrea','andrea.castro@empresa.com','$2b$10$.TdiwXd8rr.zd4ZeNPYWJ.cwXtDaquuIwSQVlXc30ivqAqFf9zWDC','empleado');

-- Asistencia de ejemplo (últimos días).
INSERT INTO asistencia (empleado_id, fecha, hora_entrada, hora_salida, estado, horas_trabajadas, minutos_tardanza) VALUES
  (1,'2026-06-29','07:55:00','17:05:00','presente',9.00,0),
  (2,'2026-06-29','08:02:00','17:10:00','presente',9.00,0),
  (3,'2026-06-29','08:25:00','17:00:00','tardanza',8.50,25),
  (4,'2026-06-29','07:50:00','17:30:00','presente',9.50,0),
  (5,'2026-06-29',NULL,NULL,'falta',0,0),
  (6,'2026-06-29','13:05:00','22:00:00','presente',9.00,0),
  (7,'2026-06-29','09:10:00','18:00:00','tardanza',8.80,10),
  (8,'2026-06-29','07:58:00','17:02:00','presente',9.00,0),
  (1,'2026-06-30','07:59:00','17:00:00','presente',9.00,0),
  (2,'2026-06-30','08:00:00','17:05:00','presente',9.00,0),
  (3,'2026-06-30','08:05:00','17:00:00','presente',8.90,0),
  (4,'2026-06-30','08:40:00','17:20:00','tardanza',8.60,40),
  (6,'2026-06-30','13:00:00','22:00:00','presente',9.00,0),
  (7,'2026-06-30','09:05:00','18:05:00','presente',9.00,0),
  (8,'2026-06-30','07:55:00','17:00:00','presente',9.00,0);

INSERT INTO permisos (empleado_id, tipo, fecha_inicio, fecha_fin, motivo, estado, aprobado_por) VALUES
  (3,'medico',   '2026-07-03','2026-07-03','Cita médica programada','aprobado',2),
  (6,'personal', '2026-07-10','2026-07-10','Trámite personal',       'pendiente',NULL),
  (7,'familiar', '2026-06-28','2026-06-28','Asunto familiar',        'rechazado',2);

INSERT INTO vacaciones (empleado_id, fecha_inicio, fecha_fin, dias, periodo, estado, aprobado_por) VALUES
  (4,'2026-07-15','2026-07-29',15,'2025-2026','aprobado',2),
  (8,'2026-08-01','2026-08-15',15,'2025-2026','pendiente',NULL),
  (2,'2026-06-01','2026-06-15',15,'2024-2025','gozado',1);

-- Fin del script
SELECT 'Base de datos neg_control_asistencia creada correctamente.' AS mensaje;