-- ============================================================
-- Hammerhead Workspace — Deployment 13
-- César Operations Manager KPI Table
-- Completely separate from kpi_scheduling_entries.
-- Safe to re-run (CREATE TABLE IF NOT EXISTS).
-- ============================================================

CREATE TABLE IF NOT EXISTS `cesar_kpi_entries` (
  `id`               int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `week_ending`      date             NOT NULL,
  `status`           enum('draft','submitted','reviewed','approved','returned','locked') NOT NULL DEFAULT 'draft',

  -- Hours Pipeline
  `contracts_started` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,custom_name,hours}',
  `contracts_lost`    longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,custom_name,hours,comments}',
  `net_hours_change`  decimal(10,2)    DEFAULT NULL COMMENT 'auto: started minus lost',

  -- Post Order Updates
  `post_order_updates` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,comments}',

  -- Client Meetings
  `client_meetings`   longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,client_name,meeting_notes,open_notes,satisfaction,risk_level}',

  -- Site Visit Scores
  `site_visit_scores` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,score,reason}',

  -- New Client Reach Outs
  `new_client_reachouts` longtext DEFAULT NULL COMMENT 'JSON array of {company_name,contact_name,phone,notes}',

  -- Contract Renewals
  `contract_renewals` longtext DEFAULT NULL COMMENT 'JSON array of {site_id,site_name,client_name,rate_increase,notes}',

  -- OT vs Budget
  `ot_budgeted`       decimal(10,2)    DEFAULT 0,
  `ot_actual`         decimal(10,2)    DEFAULT 0,
  `ot_variance`       decimal(10,2)    DEFAULT NULL COMMENT 'auto: actual minus budgeted',

  -- Manager Performance Scores
  `score_karan`       tinyint(2)       DEFAULT 0,
  `score_karan_notes` text             DEFAULT NULL,
  `score_harry`       tinyint(2)       DEFAULT 0,
  `score_harry_notes` text             DEFAULT NULL,
  `score_jatinder`    tinyint(2)       DEFAULT 0,
  `score_jatinder_notes` text          DEFAULT NULL,
  `score_michael`     tinyint(2)       DEFAULT 0,
  `score_michael_notes` text           DEFAULT NULL,

  -- Escalations & Investigations
  `open_investigations`     smallint(5) UNSIGNED DEFAULT 0,
  `investigation_desc`      text        DEFAULT NULL,
  `escalations_received`    smallint(5) UNSIGNED DEFAULT 0,
  `escalations_from_summary` text       DEFAULT NULL,

  -- Review / workflow
  `submitted_at`     datetime         DEFAULT NULL,
  `reviewer_type`    varchar(20)      DEFAULT NULL,
  `reviewer_id`      int(11)          DEFAULT NULL,
  `reviewer_name`    varchar(200)     DEFAULT NULL,
  `review_comment`   text             DEFAULT NULL,
  `reviewed_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`),
  UNIQUE KEY `uniq_week` (`week_ending`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;