-- Overtime Default Rates Table
-- Stores the default overtime rate for each site
-- This rate applies to all employees who don't have a designation-specific rate

-- Drop table if exists (to fix any constraint conflicts)
DROP TABLE IF EXISTS `overtime_default_rates`;

CREATE TABLE `overtime_default_rates` (
  `default_rate_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `overtime_rate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`default_rate_id`),
  UNIQUE KEY `unique_site_default_rate` (`site_id`),
  KEY `fk_ot_def_rate_site` (`site_id`),
  KEY `fk_ot_def_rate_created_by` (`created_by`),
  KEY `fk_ot_def_rate_updated_by` (`updated_by`),
  CONSTRAINT `fk_ot_def_rate_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ot_def_rate_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ot_def_rate_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

