SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ============================================
-- 1) EMPRESAS, USUARIOS Y EMPLEADOS
-- ============================================
CREATE TABLE companies (
  company_id       BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name             VARCHAR(255) NOT NULL,
  tax_id           VARCHAR(32),                 -- CIF/NIF
  timezone         VARCHAR(64) NOT NULL DEFAULT 'Europe/Madrid',
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  INDEX idx_tax (tax_id)
) ENGINE=InnoDB;

CREATE TABLE locations (
  location_id      BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  name             VARCHAR(150) NOT NULL,
  address          VARCHAR(255),
  geo_lat          DECIMAL(9,6),
  geo_lng          DECIMAL(9,6),
  geofence_radius_m SMALLINT UNSIGNED DEFAULT 0,
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  INDEX idx_company (company_id)
) ENGINE=InnoDB;

-- Usuarios del panel (admin, RRHH, inspector)
CREATE TABLE users (
  user_id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  name             VARCHAR(150) NOT NULL,
  email            VARCHAR(255) NOT NULL UNIQUE,
  password_hash    VARCHAR(255) NOT NULL,
  role             ENUM('owner','admin','hr','viewer','inspector_ro') NOT NULL DEFAULT 'viewer',
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id)
) ENGINE=InnoDB;

-- Empleados
CREATE TABLE employees (
  employee_id      BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  code             VARCHAR(64),                 -- id interno
  first_name       VARCHAR(120) NOT NULL,
  last_name        VARCHAR(120) NOT NULL,
  email            VARCHAR(255),
  phone            VARCHAR(64),
  default_location_id BIGINT UNSIGNED,
  hire_date        DATE,
  termination_date DATE,
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (default_location_id) REFERENCES locations(location_id),
  UNIQUE KEY uk_company_code (company_id, code),
  INDEX idx_company_active (company_id, is_active)
) ENGINE=InnoDB;

-- Cuentas de acceso de empleados
CREATE TABLE employee_accounts (
  emp_account_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  employee_id      BIGINT UNSIGNED NOT NULL,
  email            VARCHAR(255) NOT NULL UNIQUE,
  password_hash    VARCHAR(255) NOT NULL,
  mfa_secret       VARCHAR(64),
  email_verified   TINYINT(1) NOT NULL DEFAULT 0,
  last_login_at    DATETIME,
  failed_attempts  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until     DATETIME,
  privacy_accepted_at DATETIME,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  INDEX idx_employee (employee_id)
) ENGINE=InnoDB;

CREATE TABLE employee_password_resets (
  reset_id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  emp_account_id   BIGINT UNSIGNED NOT NULL,
  token            CHAR(64) NOT NULL UNIQUE,
  expires_at       DATETIME NOT NULL,
  used_at          DATETIME,
  created_at       DATETIME NOT NULL,
  FOREIGN KEY (emp_account_id) REFERENCES employee_accounts(emp_account_id),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB;

-- Credenciales adicionales (PIN, QR, NFC)
CREATE TABLE employee_credentials (
  cred_id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  employee_id      BIGINT UNSIGNED NOT NULL,
  pin_code         CHAR(6),
  qr_key           CHAR(24),
  nfc_uid          VARCHAR(64),
  is_enabled       TINYINT(1) NOT NULL DEFAULT 1,
  last_rotated_at  DATETIME,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  UNIQUE KEY uk_pin (pin_code),
  UNIQUE KEY uk_qr (qr_key),
  UNIQUE KEY uk_nfc (nfc_uid)
) ENGINE=InnoDB;

-- ============================================
-- 2) SESIONES Y DISPOSITIVOS
-- ============================================
CREATE TABLE mobile_sessions (
  session_id       BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  employee_id      BIGINT UNSIGNED NOT NULL,
  device_id        VARCHAR(128) NOT NULL,
  created_at       DATETIME NOT NULL,
  last_seen_at     DATETIME,
  is_valid         TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  INDEX idx_emp_device (employee_id, device_id)
) ENGINE=InnoDB;

CREATE TABLE kiosk_devices (
  kiosk_id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  location_id      BIGINT UNSIGNED,
  device_label     VARCHAR(120),
  device_uuid      VARCHAR(128) NOT NULL,
  bind_token       CHAR(64) NOT NULL,
  is_bound         TINYINT(1) NOT NULL DEFAULT 0,
  is_enabled       TINYINT(1) NOT NULL DEFAULT 1,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (location_id) REFERENCES locations(location_id),
  UNIQUE KEY uk_uuid (device_uuid),
  UNIQUE KEY uk_bind (bind_token),
  INDEX idx_company_loc (company_id, location_id)
) ENGINE=InnoDB;

-- ============================================
-- 3) HORARIOS Y TURNOS
-- ============================================
CREATE TABLE work_schedules (
  schedule_id      BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  name             VARCHAR(120) NOT NULL,
  timezone         VARCHAR(64) NOT NULL,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id)
) ENGINE=InnoDB;

CREATE TABLE work_schedule_rules (
  rule_id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  schedule_id      BIGINT UNSIGNED NOT NULL,
  weekday          TINYINT NOT NULL, -- 1=lunes..7=domingo
  expected_start   TIME,
  expected_end     TIME,
  break_minutes    SMALLINT DEFAULT 0,
  is_workday       TINYINT(1) NOT NULL DEFAULT 1,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (schedule_id) REFERENCES work_schedules(schedule_id),
  UNIQUE KEY uk_sched_day (schedule_id, weekday)
) ENGINE=InnoDB;

CREATE TABLE employee_schedules (
  employee_schedule_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  employee_id      BIGINT UNSIGNED NOT NULL,
  schedule_id      BIGINT UNSIGNED NOT NULL,
  valid_from       DATE NOT NULL,
  valid_to         DATE,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  FOREIGN KEY (schedule_id) REFERENCES work_schedules(schedule_id)
) ENGINE=InnoDB;

-- ============================================
-- 4) FICHAJES
-- ============================================
CREATE TABLE time_events (
  time_event_id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  employee_id      BIGINT UNSIGNED NOT NULL,
  location_id      BIGINT UNSIGNED,
  event_type       ENUM('in','out','break_start','break_end') NOT NULL,
  occurred_at_utc  DATETIME NOT NULL,
  local_time       DATETIME NOT NULL,
  timezone         VARCHAR(64) NOT NULL,
  source           ENUM('mobile','kiosk','web','api') NOT NULL,
  device_id        VARCHAR(128),
  ip_address       VARBINARY(16),
  geo_lat          DECIMAL(9,6),
  geo_lng          DECIMAL(9,6),
  geofence_ok      TINYINT(1),
  note             VARCHAR(500),
  photo_url        VARCHAR(255),
  prev_event_hash  CHAR(64),
  event_hash       CHAR(64) NOT NULL,
  created_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  FOREIGN KEY (location_id) REFERENCES locations(location_id),
  INDEX idx_emp_time (employee_id, occurred_at_utc),
  INDEX idx_company_time (company_id, occurred_at_utc)
) ENGINE=InnoDB;

CREATE TABLE time_event_corrections (
  correction_id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  employee_id      BIGINT UNSIGNED NOT NULL,
  requested_by     BIGINT UNSIGNED NOT NULL,
  original_event_id BIGINT UNSIGNED,
  requested_change JSON NOT NULL,
  reason           VARCHAR(500),
  status           ENUM('pending','approved','rejected','applied') DEFAULT 'pending',
  reviewed_by      BIGINT UNSIGNED,
  reviewed_at      DATETIME,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  FOREIGN KEY (requested_by) REFERENCES users(user_id),
  FOREIGN KEY (original_event_id) REFERENCES time_events(time_event_id)
) ENGINE=InnoDB;

-- ============================================
-- 5) AUSENCIAS Y FESTIVOS
-- ============================================
CREATE TABLE holidays (
  holiday_id       BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  date             DATE NOT NULL,
  name             VARCHAR(150),
  is_national      TINYINT(1) DEFAULT 0,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  UNIQUE KEY uk_company_date (company_id, date)
) ENGINE=InnoDB;

CREATE TABLE leaves (
  leave_id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  employee_id      BIGINT UNSIGNED NOT NULL,
  leave_type       ENUM('vacation','sick','personal','unpaid','other') NOT NULL,
  start_date       DATE NOT NULL,
  end_date         DATE NOT NULL,
  approved_by      BIGINT UNSIGNED,
  status           ENUM('pending','approved','rejected') DEFAULT 'pending',
  note             VARCHAR(500),
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
  FOREIGN KEY (approved_by) REFERENCES users(user_id)
) ENGINE=InnoDB;

-- ============================================
-- 6) INSPECCIÓN Y AUDITORÍA
-- ============================================
CREATE TABLE inspector_access_tokens (
  token_id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  token            CHAR(64) NOT NULL UNIQUE,
  expires_at       DATETIME NOT NULL,
  created_by       BIGINT UNSIGNED NOT NULL,
  is_enabled       TINYINT(1) NOT NULL DEFAULT 1,
  scope            ENUM('all','employee','date_range') NOT NULL DEFAULT 'all',
  scope_payload    JSON,
  created_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (created_by) REFERENCES users(user_id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  audit_id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  actor_user_id    BIGINT UNSIGNED,
  action           VARCHAR(64) NOT NULL,
  target_type      VARCHAR(64),
  target_id        BIGINT UNSIGNED,
  details          JSON,
  ip_address       VARBINARY(16),
  created_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (actor_user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

CREATE TABLE exports (
  export_id        BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  company_id       BIGINT UNSIGNED NOT NULL,
  requested_by     BIGINT UNSIGNED,
  parameters       JSON NOT NULL,
  file_url         VARCHAR(255) NOT NULL,
  created_at       DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id),
  FOREIGN KEY (requested_by) REFERENCES users(user_id)
) ENGINE=InnoDB;
