SET @has_absence_location_id := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND COLUMN_NAME = 'location_id'
);
SET @sql_add_absence_location_id := IF(@has_absence_location_id = 0,
  'ALTER TABLE absence_types ADD COLUMN location_id BIGINT UNSIGNED NULL AFTER tenant_id',
  'SELECT 1'
);
PREPARE stmt_add_absence_location_id FROM @sql_add_absence_location_id;
EXECUTE stmt_add_absence_location_id;
DEALLOCATE PREPARE stmt_add_absence_location_id;

SET @has_absence_scope_location_id := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND COLUMN_NAME = 'scope_location_id'
);
SET @sql_add_absence_scope_location_id := IF(@has_absence_scope_location_id = 0,
  'ALTER TABLE absence_types ADD COLUMN scope_location_id BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER location_id',
  'SELECT 1'
);
PREPARE stmt_add_absence_scope_location_id FROM @sql_add_absence_scope_location_id;
EXECUTE stmt_add_absence_scope_location_id;
DEALLOCATE PREPARE stmt_add_absence_scope_location_id;

SET @has_absence_notes := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND COLUMN_NAME = 'notes'
);
SET @sql_add_absence_notes := IF(@has_absence_notes = 0,
  'ALTER TABLE absence_types ADD COLUMN notes TEXT NULL AFTER sort_order',
  'SELECT 1'
);
PREPARE stmt_add_absence_notes FROM @sql_add_absence_notes;
EXECUTE stmt_add_absence_notes;
DEALLOCATE PREPARE stmt_add_absence_notes;

SET @has_absence_location_fk := (
  SELECT COUNT(*)
  FROM information_schema.REFERENTIAL_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND CONSTRAINT_NAME = 'fk_absence_types_location'
);
SET @sql_add_absence_location_fk := IF(@has_absence_location_fk = 0,
  'ALTER TABLE absence_types ADD CONSTRAINT fk_absence_types_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL',
  'SELECT 1'
);
PREPARE stmt_add_absence_location_fk FROM @sql_add_absence_location_fk;
EXECUTE stmt_add_absence_location_fk;
DEALLOCATE PREPARE stmt_add_absence_location_fk;

SET @has_scope_column := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND COLUMN_NAME = 'scope_location_id'
);
SET @sql_sync_absence_scope := IF(@has_scope_column > 0,
  'UPDATE absence_types SET scope_location_id = COALESCE(location_id, 0)',
  'SELECT 1'
);
PREPARE stmt_sync_absence_scope FROM @sql_sync_absence_scope;
EXECUTE stmt_sync_absence_scope;
DEALLOCATE PREPARE stmt_sync_absence_scope;

SET @has_old_absence_unique := (
  SELECT COUNT(*)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND INDEX_NAME = 'uq_absence_types_tenant_code'
);
SET @sql_drop_old_absence_unique := IF(@has_old_absence_unique > 0,
  'ALTER TABLE absence_types DROP INDEX uq_absence_types_tenant_code',
  'SELECT 1'
);
PREPARE stmt_drop_old_absence_unique FROM @sql_drop_old_absence_unique;
EXECUTE stmt_drop_old_absence_unique;
DEALLOCATE PREPARE stmt_drop_old_absence_unique;

SET @has_new_absence_unique := (
  SELECT COUNT(*)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND INDEX_NAME = 'uq_absence_types_scope_code'
);
SET @sql_add_new_absence_unique := IF(@has_new_absence_unique = 0,
  'ALTER TABLE absence_types ADD UNIQUE KEY uq_absence_types_scope_code (tenant_id, scope_location_id, code)',
  'SELECT 1'
);
PREPARE stmt_add_new_absence_unique FROM @sql_add_new_absence_unique;
EXECUTE stmt_add_new_absence_unique;
DEALLOCATE PREPARE stmt_add_new_absence_unique;

SET @has_absence_scope_index := (
  SELECT COUNT(*)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'absence_types'
    AND INDEX_NAME = 'idx_absence_types_scope_active'
);
SET @sql_add_absence_scope_index := IF(@has_absence_scope_index = 0,
  'ALTER TABLE absence_types ADD KEY idx_absence_types_scope_active (tenant_id, location_id, is_active, sort_order)',
  'SELECT 1'
);
PREPARE stmt_add_absence_scope_index FROM @sql_add_absence_scope_index;
EXECUTE stmt_add_absence_scope_index;
DEALLOCATE PREPARE stmt_add_absence_scope_index;

CREATE TABLE IF NOT EXISTS loan_sources (
  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,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  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_loan_sources_scope_code (tenant_id, scope_location_id, code),
  KEY idx_loan_sources_scope_active (tenant_id, location_id, is_active, sort_order),
  CONSTRAINT fk_loan_sources_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS addition_types (
  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,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  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_addition_types_scope_code (tenant_id, scope_location_id, code),
  KEY idx_addition_types_scope_active (tenant_id, location_id, is_active, sort_order),
  CONSTRAINT fk_addition_types_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_policies (
  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,
  policy_key VARCHAR(60) NOT NULL DEFAULT 'default',
  policy_name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_default TINYINT(1) NOT NULL DEFAULT 1,
  settings_json LONGTEXT 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_policies_scope_key (tenant_id, scope_location_id, policy_key),
  KEY idx_payroll_policies_scope_active (tenant_id, location_id, is_active, is_default),
  CONSTRAINT fk_payroll_policies_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;