-- Ponto e Ausências (MVP) for the "ponto-sas" database

SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `ponto-sas`
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

USE `ponto-sas`;

SET foreign_key_checks = 0;

-- Escala planejada por dia (para permitir ver "conflito de escala")
CREATE TABLE IF NOT EXISTS `employee_shift_daily` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,
  `work_date` DATE NOT NULL,

  `shift_type_id` BIGINT UNSIGNED NULL,
  `is_day_off` TINYINT(1) NOT NULL DEFAULT 0,
  `expected_minutes` SMALLINT NULL,
  `notes` VARCHAR(255) NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY `uk_employee_shift_daily` (`employee_id`, `work_date`),
  KEY `idx_employee_shift_daily_employee_date` (`employee_id`, `work_date`),

  CONSTRAINT `fk_esd_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_esd_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_esd_shift_type`
    FOREIGN KEY (`shift_type_id`) REFERENCES `work_shift_types`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `holidays` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `holiday_date` DATE NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_holidays_tenant_date` (`tenant_id`, `holiday_date`),

  CONSTRAINT `fk_holidays_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ausências / Férias / Licenças
CREATE TABLE IF NOT EXISTS `absence_requests` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,

  `absence_type` ENUM(
    'FERIAS',
    'LICENCA_MEDICA',
    'FOLGA',
    'AUSENCIA',
    'FALTA',
    'FOLGA_COMPENSATORIA',
    'OUTRO'
  ) NOT NULL,

  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `days_requested` SMALLINT NULL,
  `reason_text` VARCHAR(500) NULL,

  `status` ENUM('PENDING','APPROVED','REJECTED','CANCELLED') NOT NULL DEFAULT 'PENDING',

  `requested_by_user_id` BIGINT UNSIGNED NULL,
  `approved_by_user_id` BIGINT UNSIGNED NULL,

  `requested_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `approved_at` TIMESTAMP NULL DEFAULT NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_abs_employee_dates` (`employee_id`, `start_date`, `end_date`),

  CONSTRAINT `fk_abs_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_abs_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_abs_requested_by`
    FOREIGN KEY (`requested_by_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_abs_approved_by`
    FOREIGN KEY (`approved_by_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Regras de ponto: tolerâncias e configurações (escopo por tenant/dept/employee)
CREATE TABLE IF NOT EXISTS `point_rules` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,

  `scope_type` ENUM('TENANT','DEPARTMENT','EMPLOYEE') NOT NULL,
  `department_id` BIGINT UNSIGNED NULL,
  `employee_id` BIGINT UNSIGNED NULL,

  `tolerance_early_entry_min` INT NOT NULL DEFAULT 0,
  `tolerance_late_entry_min` INT NOT NULL DEFAULT 0,
  `tolerance_exit_early_min` INT NOT NULL DEFAULT 0,

  `block_overtime` TINYINT(1) NOT NULL DEFAULT 0,

  `night_turn_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `night_turn_cutoff_time` TIME NOT NULL DEFAULT '00:00:00',
  `night_extra_calc` ENUM(
    'ADICIONAL_NOTURNO_PADRAO_20',
    'HORA_EXTRA_50',
    'HORA_EXTRA_100',
    'BANCO_DE_HORAS'
  ) NOT NULL DEFAULT 'HORA_EXTRA_50',

  `valid_from` DATE NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by_user_id` BIGINT UNSIGNED NULL,

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT `fk_rules_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rules_department`
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rules_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_rules_created_by`
    FOREIGN KEY (`created_by_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Registros de ponto
CREATE TABLE IF NOT EXISTS `time_punches` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` BIGINT UNSIGNED NOT NULL,

  `work_date` DATE NOT NULL,
  `event_code` ENUM(
    'ENTRADA_1',
    'SAIDA_1',
    'ENTRADA_2',
    'SAIDA_2',
    'ALMOCO_ENTRADA',
    'ALMOCO_SAIDA'
  ) NOT NULL,
  `sequence` TINYINT NOT NULL DEFAULT 1,

  `event_at` DATETIME NOT NULL,
  `source` ENUM('mobile','web','manual') NOT NULL DEFAULT 'mobile',

  `gps_lat` DECIMAL(9,6) NULL,
  `gps_lng` DECIMAL(9,6) NULL,
  `ip_address` VARCHAR(45) NULL,
  `device_label` VARCHAR(100) NULL,

  `status` ENUM('OPEN','APPROVED','REJECTED','EDITED','CANCELLED') NOT NULL DEFAULT 'OPEN',

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  UNIQUE KEY `uk_time_punches` (`employee_id`, `work_date`, `event_code`, `sequence`),
  KEY `idx_time_punches_employee_date` (`employee_id`, `work_date`),

  CONSTRAINT `fk_punches_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_punches_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Histórico de edições/ajustes manuais
CREATE TABLE IF NOT EXISTS `time_punch_edits` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,

  `punch_id` BIGINT UNSIGNED NOT NULL,
  `old_event_at` DATETIME NULL,
  `new_event_at` DATETIME NULL,

  `reason_type` ENUM('ESQUECIMENTO','ATESTADO','AJUSTE_MANUAL','HORA_EXTRA','OUTRO') NOT NULL,
  `reason_text` VARCHAR(255) NULL,

  `changed_by_user_id` BIGINT UNSIGNED NULL,
  `changed_from` ENUM('desktop','mobile') NOT NULL DEFAULT 'desktop',

  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT `fk_punch_edits_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_punch_edits_punch`
    FOREIGN KEY (`punch_id`) REFERENCES `time_punches`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_punch_edits_changed_by`
    FOREIGN KEY (`changed_by_user_id`) REFERENCES `tenant_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET foreign_key_checks = 1;