-- Deployment 30: Jatinder (Accounts) KPI table

CREATE TABLE IF NOT EXISTS `kpi_jatinder_entries` (
  `id`               bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `submitter_type`   enum('manager','employee') NOT NULL DEFAULT 'manager',
  `submitter_id`     int(11) NOT NULL,
  `submitter_name`   varchar(200) NOT NULL,
  `week_ending`      date NOT NULL,
  `week_starting`    date DEFAULT NULL,
  `status`           enum('draft','submitted','reviewed','approved','returned','locked') NOT NULL DEFAULT 'draft',
  -- Staffing & Training
  `new_hires`              int(10) DEFAULT 0,
  `new_hire_names`         varchar(500) DEFAULT NULL,
  `new_hire_training_hrs`  decimal(7,1) DEFAULT 0.0,
  `new_contract_hrs`       decimal(7,1) DEFAULT 0.0,
  `new_contract_site`      varchar(300) DEFAULT NULL,
  `lost_contract_hrs`      decimal(7,1) DEFAULT 0.0,
  `lost_contract_site`     varchar(300) DEFAULT NULL,
  -- Violations
  `guards_sleeping`        int(10) DEFAULT 0,
  `serious_violations`     int(10) DEFAULT 0,
  `failed_reinspections`   int(10) DEFAULT 0,
  `avg_score_per_site`     decimal(4,2) DEFAULT 0.00,
  `uniform_compliance_pct` int(10) DEFAULT 0,
  `violation_desc`         text DEFAULT NULL,
  `violation_handled`      text DEFAULT NULL,
  `improvements`           text DEFAULT NULL,
  -- Client Activity
  `client_meetings`        int(10) DEFAULT 0,
  `client_names_met`       varchar(500) DEFAULT NULL,
  `client_complaints`      int(10) DEFAULT 0,
  `client_compliments`     int(10) DEFAULT 0,
  `avg_response_time_hrs`  decimal(5,2) DEFAULT 0.00,
  -- Sites Visited
  `sites_visited`          longtext DEFAULT NULL,
  -- Guard Recognition
  `recognition_count`      int(10) DEFAULT 0,
  `recognition_names`      varchar(500) DEFAULT NULL,
  -- Site Inspections (shift breakdown)
  `insp_day_total`         int(10) DEFAULT 0,
  `insp_day_failed`        int(10) DEFAULT 0,
  `insp_swing_total`       int(10) DEFAULT 0,
  `insp_swing_failed`      int(10) DEFAULT 0,
  `insp_grave_total`       int(10) DEFAULT 0,
  `insp_grave_failed`      int(10) DEFAULT 0,
  `insp_total`             int(10) DEFAULT 0,
  `insp_failed_total`      int(10) DEFAULT 0,
  `failed_inspections_detail` longtext DEFAULT NULL,
  -- Refresher Training
  `refresher_done`         varchar(10) DEFAULT NULL,
  `refresher_detail`       longtext DEFAULT NULL,
  -- Site Improvement Work
  `site_improvements`      longtext DEFAULT NULL,
  -- MSU
  `msu_pickup`             int(10) DEFAULT 0,
  `msu_dropoff`            int(10) DEFAULT 0,
  `msu_troubleshoot`       varchar(100) DEFAULT NULL,
  `msu_trouble_notes`      text DEFAULT NULL,
  `afterhours_response`    varchar(10) DEFAULT NULL,
  `afterhours_site`        varchar(200) DEFAULT NULL,
  `afterhours_time`        varchar(10) DEFAULT NULL,
  `afterhours_notes`       text DEFAULT NULL,
  `msu_notes`              text DEFAULT NULL,
  -- Phone Audit
  `phone_audit_done`       varchar(100) DEFAULT NULL,
  `phone_items_saved`      varchar(100) DEFAULT NULL,
  `phone_unauth_apps`      varchar(20) DEFAULT NULL,
  `phone_count`            int(10) DEFAULT 0,
  `phone_violators`        longtext DEFAULT NULL,
  `phone_apps_notes`       text DEFAULT NULL,
  `phone_notes`            text DEFAULT NULL,
  -- General Notes
  `highlights`             text DEFAULT NULL,
  `concerns`               text DEFAULT NULL,
  `remarks`                text DEFAULT NULL,
  -- Review / Approval
  `reviewer_type`          enum('manager','superadmin') DEFAULT NULL,
  `reviewer_id`            int(11) DEFAULT NULL,
  `reviewer_name`          varchar(200) DEFAULT NULL,
  `review_comment`         text DEFAULT NULL,
  `review_score`           tinyint(2) DEFAULT NULL,
  `score_revised`          tinyint(1) DEFAULT 0,
  `return_reason`          text DEFAULT NULL,
  `submitted_at`           datetime DEFAULT NULL,
  `reviewed_at`            datetime DEFAULT NULL,
  `approved_at`            datetime DEFAULT NULL,
  `returned_at`            datetime DEFAULT NULL,
  `locked_at`              datetime DEFAULT NULL,
  `created_at`             timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at`             timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_jat_submitter` (`submitter_type`,`submitter_id`),
  KEY `idx_jat_status`    (`status`),
  KEY `idx_jat_week`      (`week_ending`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `kpi_jatinder_review_history` (
  `id`          bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `entry_id`    bigint(20) UNSIGNED NOT NULL,
  `changed_by`  varchar(200) NOT NULL,
  `old_score`   tinyint(2) DEFAULT NULL,
  `new_score`   tinyint(2) DEFAULT NULL,
  `old_comment` text DEFAULT NULL,
  `new_comment` text DEFAULT NULL,
  `reason`      text DEFAULT NULL,
  `created_at`  timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_jrh_entry` (`entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;