-- Hammerhead Workspace - Patch 3: KPI Tracker
-- Replaces the Google Sheets / Apps Script KPI workflow with native tables.
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS `kpi_metric_defs` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `department_id` int(10) UNSIGNED NOT NULL,
  `metric_key` varchar(80) NOT NULL,
  `metric_label` varchar(150) NOT NULL,
  `unit` varchar(30) DEFAULT '',          -- '%', 'count', 'hrs', etc.
  `input_type` enum('number','percent','text','select') NOT NULL DEFAULT 'number',
  `select_options` text DEFAULT NULL,     -- pipe-separated, only used when input_type = 'select'
  `sort_order` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dept_metric` (`department_id`,`metric_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `kpi_entries` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `department_id` int(10) UNSIGNED NOT NULL,
  `user_type` enum('manager','superadmin','employee') NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_name` varchar(200) DEFAULT '',
  `period_start` date NOT NULL,           -- Monday of the reporting week
  `values_json` longtext NOT NULL,        -- { metric_key: value, ... }
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_kpi_dept_period` (`department_id`,`period_start`),
  KEY `idx_kpi_user` (`user_type`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Starter metric definitions per department (edit anytime from Admin > KPI Tracker > Setup)
INSERT INTO `kpi_metric_defs` (`department_id`, `metric_key`, `metric_label`, `unit`, `input_type`, `sort_order`)
SELECT d.id, 'tasks_completed', 'Tasks Completed', 'count', 'number', 1 FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM kpi_metric_defs m WHERE m.department_id = d.id AND m.metric_key = 'tasks_completed');

INSERT INTO `kpi_metric_defs` (`department_id`, `metric_key`, `metric_label`, `unit`, `input_type`, `sort_order`)
SELECT d.id, 'completion_rate', 'Completion Rate', '%', 'percent', 2 FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM kpi_metric_defs m WHERE m.department_id = d.id AND m.metric_key = 'completion_rate');

INSERT INTO `kpi_metric_defs` (`department_id`, `metric_key`, `metric_label`, `unit`, `input_type`, `sort_order`)
SELECT d.id, 'notes', 'Notes / Blockers', '', 'text', 3 FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM kpi_metric_defs m WHERE m.department_id = d.id AND m.metric_key = 'notes');