CREATE TABLE IF NOT EXISTS employees (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  home_location_id BIGINT UNSIGNED NULL,
  employee_code VARCHAR(40) NOT NULL,
  full_name VARCHAR(190) NOT NULL,
  gender VARCHAR(20) NULL,
  employment_status VARCHAR(40) NOT NULL DEFAULT 'tetap',
  position_title VARCHAR(120) NULL,
  base_salary_monthly DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  salary_type VARCHAR(30) NOT NULL DEFAULT 'bulanan',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  portal_enabled TINYINT(1) NOT NULL DEFAULT 0,
  payroll_enabled TINYINT(1) NOT NULL DEFAULT 1,
  show_in_spk_handover TINYINT(1) NOT NULL DEFAULT 0,
  show_in_warehouse_input TINYINT(1) NOT NULL DEFAULT 0,
  show_in_internal_dropdowns TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  joined_at DATE NULL,
  resigned_at DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_employees_tenant_code (tenant_id, employee_code),
  KEY idx_employees_location_active (home_location_id, is_active),
  KEY idx_employees_payroll (tenant_id, payroll_enabled, is_active),
  CONSTRAINT fk_employees_location FOREIGN KEY (home_location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS absence_types (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  deduction_type VARCHAR(20) NOT NULL DEFAULT 'none',
  deduction_value DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  requires_note TINYINT(1) NOT NULL DEFAULT 0,
  affects_payroll TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_absence_types_tenant_code (tenant_id, code),
  KEY idx_absence_types_active (tenant_id, is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_periods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  scope_location_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  period_name VARCHAR(120) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  payroll_year SMALLINT NOT NULL,
  payroll_month_label VARCHAR(40) NOT NULL,
  thr_mode VARCHAR(20) NOT NULL DEFAULT 'off',
  status VARCHAR(20) NOT NULL DEFAULT 'open',
  generated_at DATETIME NULL,
  posted_at DATETIME NULL,
  locked_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payroll_period_scope (tenant_id, scope_location_id, start_date, end_date),
  KEY idx_payroll_period_status (tenant_id, location_id, status),
  KEY idx_payroll_period_dates (tenant_id, start_date, end_date),
  CONSTRAINT fk_payroll_period_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_payroll_period_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employee_absences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NULL,
  absence_type_id BIGINT UNSIGNED NOT NULL,
  date_from DATE NOT NULL,
  date_to DATE NOT NULL,
  days_count INT NOT NULL DEFAULT 1,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_employee_absence_range (tenant_id, employee_id, date_from, date_to),
  KEY idx_employee_absence_period (period_id, employee_id),
  CONSTRAINT fk_employee_absence_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
  CONSTRAINT fk_employee_absence_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE SET NULL,
  CONSTRAINT fk_employee_absence_type FOREIGN KEY (absence_type_id) REFERENCES absence_types(id) ON DELETE RESTRICT,
  CONSTRAINT fk_employee_absence_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_employee_absence_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS thr_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  method VARCHAR(20) NOT NULL DEFAULT 'manual',
  formula_type VARCHAR(40) NULL,
  formula_value DECIMAL(14,2) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_thr_period_employee (period_id, employee_id),
  KEY idx_thr_employee (tenant_id, employee_id),
  CONSTRAINT fk_thr_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_thr_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
  CONSTRAINT fk_thr_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employee_loans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NULL,
  loan_source VARCHAR(20) NOT NULL DEFAULT 'STORE',
  transaction_date DATE NOT NULL,
  principal_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  repayment_type VARCHAR(20) NOT NULL DEFAULT 'DIRECT',
  tenor INT NULL,
  installment_amount DECIMAL(14,2) NULL,
  amount_due_this_period DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_employee_loans_period (tenant_id, period_id, is_active),
  KEY idx_employee_loans_employee (tenant_id, employee_id, transaction_date),
  KEY idx_employee_loans_source (tenant_id, loan_source, is_active),
  CONSTRAINT fk_employee_loans_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
  CONSTRAINT fk_employee_loans_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE SET NULL,
  CONSTRAINT fk_employee_loans_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employee_additions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NULL,
  addition_type VARCHAR(40) NOT NULL DEFAULT 'MANUAL_ADDITION',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_employee_additions_period (tenant_id, period_id, addition_type),
  KEY idx_employee_additions_employee (tenant_id, employee_id),
  CONSTRAINT fk_employee_additions_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
  CONSTRAINT fk_employee_additions_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE SET NULL,
  CONSTRAINT fk_employee_additions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS print_error_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  employee_id BIGINT UNSIGNED NULL,
  related_user_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  spk_id BIGINT UNSIGNED NULL,
  incident_date DATE NOT NULL,
  error_type VARCHAR(30) NOT NULL DEFAULT 'OTHER',
  summary VARCHAR(190) NOT NULL,
  qty DECIMAL(14,2) NULL,
  unit_price DECIMAL(14,2) NULL,
  total_loss_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  is_personal_charge TINYINT(1) NOT NULL DEFAULT 0,
  charge_amount DECIMAL(14,2) NULL,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_print_error_period (tenant_id, location_id, incident_date),
  KEY idx_print_error_employee (tenant_id, employee_id, incident_date),
  KEY idx_print_error_charge (tenant_id, is_personal_charge, incident_date),
  CONSTRAINT fk_print_error_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_print_error_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
  CONSTRAINT fk_print_error_user FOREIGN KEY (related_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_print_error_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_print_error_spk FOREIGN KEY (spk_id) REFERENCES spk(id) ON DELETE SET NULL,
  CONSTRAINT fk_print_error_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_manual_deductions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NOT NULL,
  deduction_code VARCHAR(40) NOT NULL DEFAULT 'MANUAL_DEDUCTION',
  deduction_name VARCHAR(120) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_payroll_manual_deductions_period (tenant_id, period_id, employee_id),
  CONSTRAINT fk_payroll_manual_deductions_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payroll_manual_deductions_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_payroll_manual_deductions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_slips (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  period_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  employee_code_snapshot VARCHAR(40) NOT NULL,
  employee_name_snapshot VARCHAR(190) NOT NULL,
  position_snapshot VARCHAR(120) NULL,
  base_salary_snapshot DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_earnings DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_deductions DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  gross_salary DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  net_salary DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  generated_by BIGINT UNSIGNED NULL,
  generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  posted_at DATETIME NULL,
  locked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payroll_slips_period_employee (period_id, employee_id),
  KEY idx_payroll_slips_status (tenant_id, period_id, status),
  CONSTRAINT fk_payroll_slips_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_payroll_slips_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
  CONSTRAINT fk_payroll_slips_generated_by FOREIGN KEY (generated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_component_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payroll_slip_id BIGINT UNSIGNED NOT NULL,
  component_group VARCHAR(20) NOT NULL,
  component_source VARCHAR(40) NOT NULL,
  component_code VARCHAR(40) NOT NULL,
  component_name VARCHAR(120) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  meta_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_payroll_component_group (payroll_slip_id, component_group),
  KEY idx_payroll_component_code (component_code),
  CONSTRAINT fk_payroll_component_slip FOREIGN KEY (payroll_slip_id) REFERENCES payroll_slips(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
('hr.view', 'HR & Payroll', 'hr', NOW()),
('hr.manage', 'Kelola HR & Payroll', 'hr', NOW()),
('payroll.view', 'Lihat Payroll', 'hr', NOW());

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN ('hr.view', 'hr.manage', 'payroll.view')
WHERE r.code IN ('owner', 'admin', 'superadmin', 'kepala_toko')
   OR r.name IN ('Owner', 'Admin', 'Superadmin', 'Kepala Toko');

SET @has_employee_id := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'employee_id'
);
SET @sql_add_employee_id := IF(@has_employee_id = 0,
  'ALTER TABLE users ADD COLUMN employee_id BIGINT UNSIGNED NULL AFTER thumbnail',
  'SELECT 1'
);
PREPARE stmt_add_employee_id FROM @sql_add_employee_id;
EXECUTE stmt_add_employee_id;
DEALLOCATE PREPARE stmt_add_employee_id;

SET @has_idx_users_employee := (
  SELECT COUNT(*)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND INDEX_NAME = 'idx_users_employee'
);
SET @sql_add_idx_users_employee := IF(@has_idx_users_employee = 0,
  'ALTER TABLE users ADD KEY idx_users_employee (employee_id)',
  'SELECT 1'
);
PREPARE stmt_add_idx_users_employee FROM @sql_add_idx_users_employee;
EXECUTE stmt_add_idx_users_employee;
DEALLOCATE PREPARE stmt_add_idx_users_employee;

SET @has_fk_users_employee := (
  SELECT COUNT(*)
  FROM information_schema.REFERENTIAL_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND CONSTRAINT_NAME = 'fk_users_employee'
);
SET @sql_add_fk_users_employee := IF(@has_fk_users_employee = 0,
  'ALTER TABLE users ADD CONSTRAINT fk_users_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_add_fk_users_employee FROM @sql_add_fk_users_employee;
EXECUTE stmt_add_fk_users_employee;
DEALLOCATE PREPARE stmt_add_fk_users_employee;