CREATE TABLE IF NOT EXISTS tenants (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(180) NOT NULL,
  legal_name VARCHAR(220) NULL,
  domain VARCHAR(180) NULL UNIQUE,
  logo_path VARCHAR(255) NULL,
  status ENUM('trial','active','suspended','expired') NOT NULL DEFAULT 'trial',
  employee_limit INT UNSIGNED NOT NULL DEFAULT 0,
  branch_limit INT UNSIGNED NOT NULL DEFAULT 0,
  device_limit INT UNSIGNED NOT NULL DEFAULT 0,
  timezone VARCHAR(80) NOT NULL DEFAULT 'UTC',
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS modules (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  module_key VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  globally_enabled BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS packages (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  yearly_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  is_trial BOOLEAN NOT NULL DEFAULT FALSE,
  employee_limit INT UNSIGNED NOT NULL DEFAULT 0,
  branch_limit INT UNSIGNED NOT NULL DEFAULT 0,
  device_limit INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS package_modules (
  package_id BIGINT UNSIGNED NOT NULL,
  module_key VARCHAR(80) NOT NULL,
  PRIMARY KEY (package_id, module_key)
);

CREATE TABLE IF NOT EXISTS tenant_modules (
  tenant_id BIGINT UNSIGNED NOT NULL,
  module_key VARCHAR(80) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  PRIMARY KEY (tenant_id, module_key),
  INDEX tenant_modules_module_idx (module_key)
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  package_id BIGINT UNSIGNED NOT NULL,
  status ENUM('trial','active','past_due','cancelled','expired') NOT NULL DEFAULT 'trial',
  starts_at DATE NOT NULL,
  expires_at DATE NOT NULL,
  billing_cycle ENUM('monthly','yearly','trial') NOT NULL DEFAULT 'trial',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX subscriptions_tenant_status_idx (tenant_id, status, expires_at)
);

CREATE TABLE IF NOT EXISTS branches (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  code VARCHAR(60) NOT NULL,
  address TEXT NULL,
  timezone VARCHAR(80) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY branches_tenant_code_unique (tenant_id, code)
);

CREATE TABLE IF NOT EXISTS departments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  code VARCHAR(60) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX departments_tenant_parent_idx (tenant_id, parent_id)
);

CREATE TABLE IF NOT EXISTS designations (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  grade VARCHAR(80) NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS employees (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NOT NULL,
  department_id BIGINT UNSIGNED NULL,
  designation_id BIGINT UNSIGNED NULL,
  manager_id BIGINT UNSIGNED NULL,
  employee_code VARCHAR(80) NOT NULL,
  rfid_card_no VARCHAR(80) NULL,
  fingerprint_id VARCHAR(80) NULL,
  face_id VARCHAR(80) NULL,
  name VARCHAR(180) NOT NULL,
  email VARCHAR(180) NULL,
  phone VARCHAR(60) NULL,
  employment_status ENUM('active','inactive','terminated','suspended') NOT NULL DEFAULT 'active',
  salary_type ENUM('monthly','daily','hourly') NOT NULL DEFAULT 'monthly',
  basic_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  bank_name VARCHAR(160) NULL,
  bank_account_no VARCHAR(120) NULL,
  joined_at DATE NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY employees_tenant_code_unique (tenant_id, employee_code),
  INDEX employees_tenant_branch_idx (tenant_id, branch_id),
  INDEX employees_device_identity_idx (tenant_id, fingerprint_id, rfid_card_no)
);

CREATE TABLE IF NOT EXISTS devices (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  serial_no VARCHAR(120) NOT NULL,
  model VARCHAR(80) NOT NULL,
  ip_address VARCHAR(80) NULL,
  port INT UNSIGNED NULL,
  status ENUM('online','offline','disabled') NOT NULL DEFAULT 'offline',
  last_seen_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY devices_tenant_serial_unique (tenant_id, serial_no)
);

CREATE TABLE IF NOT EXISTS attendance_logs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NULL,
  employee_id BIGINT UNSIGNED NULL,
  punch_type ENUM('check_in','check_out','lunch_in','lunch_out','break_in','break_out','ot_in','ot_out') NOT NULL,
  auth_mode ENUM('fingerprint','face','rfid','pin','manual') NOT NULL,
  punched_at TIMESTAMP NOT NULL,
  raw_payload JSON NULL,
  created_at TIMESTAMP NULL,
  INDEX attendance_logs_tenant_time_idx (tenant_id, punched_at),
  INDEX attendance_logs_employee_time_idx (employee_id, punched_at)
);

CREATE TABLE IF NOT EXISTS attendance_summaries (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present','absent','leave','holiday','incomplete') NOT NULL,
  working_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  late_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  early_out_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  overtime_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  penalty_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY attendance_summary_unique (tenant_id, employee_id, attendance_date)
);

CREATE TABLE IF NOT EXISTS shifts (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  shift_type ENUM('fixed','rotational','night','flexible') NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  grace_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS rules (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  rule_key VARCHAR(120) NOT NULL,
  name VARCHAR(160) NOT NULL,
  domain ENUM('attendance','payroll','leave','holiday') NOT NULL,
  priority INT NOT NULL DEFAULT 100,
  conditions JSON NOT NULL,
  actions JSON NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  INDEX rules_tenant_domain_idx (tenant_id, domain, enabled, priority)
);

CREATE TABLE IF NOT EXISTS payroll_periods (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  branch_id BIGINT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  starts_at DATE NOT NULL,
  ends_at DATE NOT NULL,
  status ENUM('draft','processing','approved','locked') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS payslips (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  payroll_period_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  gross_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_deductions DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
  components JSON NOT NULL,
  deductions JSON NOT NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY payslips_period_employee_unique (payroll_period_id, employee_id)
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(160) NOT NULL,
  entity_type VARCHAR(160) NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(80) NULL,
  user_agent VARCHAR(255) NULL,
  metadata JSON NULL,
  created_at TIMESTAMP NULL,
  INDEX audit_logs_tenant_time_idx (tenant_id, created_at)
);