-- Core schema (MVP) for the "ponto-sas" database
-- Run this in phpMyAdmin (SQL tab) or via mysql client.

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;

DROP TABLE IF EXISTS `work_shift_types`;
DROP TABLE IF EXISTS `employees`;
DROP TABLE IF EXISTS `tenant_users`;
DROP TABLE IF EXISTS `departments`;
DROP TABLE IF EXISTS `tenants`;

SET foreign_key_checks = 1;

CREATE TABLE `tenants` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(80) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_tenants_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `departments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(20) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_departments_tenant_name` (`tenant_id`, `name`),
  CONSTRAINT `fk_departments_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tenant_users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(190) NULL,
  `username` VARCHAR(60) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `profile` ENUM('admin','manager','analyst','viewer') NOT NULL,
  `status` ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `notify_welcome` TINYINT(1) NOT NULL DEFAULT 0,
  `last_login_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_tenant_users_tenant_username` (`tenant_id`, `username`),
  UNIQUE KEY `uk_tenant_users_tenant_email` (`tenant_id`, `email`),
  CONSTRAINT `fk_tenant_users_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `employees` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,

  -- personal
  `full_name` VARCHAR(150) NOT NULL,
  `cpf` VARCHAR(14) NULL,
  `rg` VARCHAR(20) NULL,
  `birth_date` DATE NULL,
  `gender` ENUM('FEMININO','MASCULINO','OUTRO','NAO_INFORMAR') NULL,

  -- contact
  `email` VARCHAR(190) NULL,
  `phone` VARCHAR(30) NULL,

  -- professional
  `department_id` BIGINT UNSIGNED NULL,
  `job_title` VARCHAR(120) NULL,
  `contract_type` ENUM('CLT','PJ','ESTAGIO','TEMPORARIO','JOVEM_APRENDIZ','OUTRO') NULL,
  `salary_base` DECIMAL(12,2) NULL,
  `admission_date` DATE NULL,

  -- address (min)
  `cep` VARCHAR(10) NULL,
  `street` VARCHAR(120) NULL,
  `number` VARCHAR(20) NULL,
  `complement` VARCHAR(60) NULL,
  `neighborhood` VARCHAR(80) NULL,
  `city` VARCHAR(80) NULL,
  `state` CHAR(2) NULL,

  `status` ENUM('active','inactive','pending','onboarding') NOT NULL DEFAULT 'active',

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

  UNIQUE KEY `uk_employees_tenant_cpf` (`tenant_id`, `cpf`),
  CONSTRAINT `fk_employees_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_employees_department`
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `work_shift_types` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tenant_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `break_minutes` SMALLINT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_shift_types_tenant_name` (`tenant_id`, `name`),
  CONSTRAINT `fk_shift_types_tenant`
    FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;