-- Overtime Rates by Designation
-- This table allows setting different overtime rates for different designations

CREATE TABLE IF NOT EXISTS `overtime_designation_rates` (
  `rate_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `designation_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),
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int(11),
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rate_id`),
  UNIQUE KEY `unique_site_designation` (`site_id`, `designation_id`),
  KEY `fk_ot_dr_site` (`site_id`),
  KEY `fk_ot_dr_designation` (`designation_id`),
  KEY `fk_ot_dr_created_by` (`created_by`),
  KEY `fk_ot_dr_updated_by` (`updated_by`),
  CONSTRAINT `fk_ot_dr_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_ot_dr_designation` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`designation_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ot_dr_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_ot_dr_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

