USE hrms_payroll;

CREATE TABLE IF NOT EXISTS ot_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    regular_multiplier DECIMAL(4,2) NOT NULL DEFAULT 1.50,
    off_day_multiplier DECIMAL(4,2) NOT NULL DEFAULT 2.00,
    holiday_multiplier DECIMAL(4,2) NOT NULL DEFAULT 2.50,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

-- Add column to designations if it doesn't exist
SET @dbname = 'hrms_payroll';
SET @tablename = 'designations';
SET @columnname = 'ot_policy_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  "ALTER TABLE designations ADD COLUMN ot_policy_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (ot_policy_id) REFERENCES ot_policies(id) ON DELETE SET NULL;"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Add column to salary_structures if it doesn't exist
SET @tablename = 'salary_structures';
SET @columnname = 'ot_policy_id';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (table_name = @tablename)
      AND (table_schema = @dbname)
      AND (column_name = @columnname)
  ) > 0,
  "SELECT 1",
  "ALTER TABLE salary_structures ADD COLUMN ot_policy_id BIGINT UNSIGNED NULL, ADD FOREIGN KEY (ot_policy_id) REFERENCES ot_policies(id) ON DELETE SET NULL;"
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

DELETE FROM company_settings WHERE setting_key = 'overtime_multiplier';

INSERT INTO ot_policies (company_id, name, regular_multiplier, off_day_multiplier, holiday_multiplier, is_default)
VALUES (1, 'Default Company Policy', 1.50, 2.00, 2.50, 1)
ON DUPLICATE KEY UPDATE name = name;