-- 014: KPI module + approved authority roles (owner decision DEC-04, 2026-10-29)
-- Authority to APPROVE KPI definitions/targets/reviews:
--   youth_exec_office | party_exec_office | secretary_general (active assignment required).
-- kpis.approve permission alone is NOT sufficient — role membership is checked server-side.

-- +up
CREATE TABLE kpi_definitions (
  id BIGSERIAL PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name_ar VARCHAR(200) NOT NULL,
  name_en VARCHAR(200) NULL,
  description TEXT NULL,
  unit VARCHAR(30) NOT NULL DEFAULT 'count' CHECK (unit IN ('count','percent','hours','score')),
  direction VARCHAR(20) NOT NULL DEFAULT 'higher_better' CHECK (direction IN ('higher_better','lower_better')),
  status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','archived')),
  created_by BIGINT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by BIGINT NULL REFERENCES users(id),
  approved_by BIGINT NULL REFERENCES users(id),
  approved_at TIMESTAMPTZ NULL
);

CREATE TABLE kpi_targets (
  id BIGSERIAL PRIMARY KEY,
  definition_id BIGINT NOT NULL REFERENCES kpi_definitions(id),
  period VARCHAR(10) NOT NULL CHECK (period ~ '^\d{4}(-Q[1-4])?$'),
  scope_type VARCHAR(20) NOT NULL DEFAULT 'national' CHECK (scope_type IN ('national','secretariat')),
  org_unit_id BIGINT NULL REFERENCES org_units(id),
  target_value numeric NOT NULL CHECK (target_value >= 0),
  approved_by BIGINT NULL REFERENCES users(id),
  approved_at TIMESTAMPTZ NULL,
  created_by BIGINT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (definition_id, period, scope_type, org_unit_id)
);

CREATE TABLE kpi_measurements (
  id BIGSERIAL PRIMARY KEY,
  definition_id BIGINT NOT NULL REFERENCES kpi_definitions(id),
  period VARCHAR(10) NOT NULL CHECK (period ~ '^\d{4}(-Q[1-4])?$'),
  scope_type VARCHAR(20) NOT NULL DEFAULT 'national' CHECK (scope_type IN ('national','secretariat')),
  org_unit_id BIGINT NULL REFERENCES org_units(id),
  value numeric NOT NULL CHECK (value >= 0),
  evidence_ref VARCHAR(255) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'submitted' CHECK (status IN ('submitted','approved','rejected')),
  review_note VARCHAR(500) NULL,
  submitted_by BIGINT NULL REFERENCES users(id),
  submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  reviewed_by BIGINT NULL REFERENCES users(id),
  reviewed_at TIMESTAMPTZ NULL
);
CREATE INDEX idx_kpi_meas_def_period ON kpi_measurements(definition_id, period);
CREATE INDEX idx_kpi_targets_def ON kpi_targets(definition_id);

-- Authority roles per DEC-04 (governance confirmed by owner decision text)
INSERT INTO roles (code, name_ar, description, is_system, is_enabled, governance_confirmed, sort_order) VALUES
  ('youth_exec_office',  'عضو المكتب التنفيذي لاتحاد الشباب', 'سلطة اعتماد المؤشرات والأهداف (DEC-04)', TRUE, TRUE, TRUE, 60),
  ('party_exec_office',  'عضو المكتب التنفيذي للحزب',        'سلطة اعتماد المؤشرات والأهداف (DEC-04)', TRUE, TRUE, TRUE, 61),
  ('secretary_general',  'الأمين العام',                     'سلطة اعتماد وطنية لكل المحافظات (DEC-04)', TRUE, TRUE, TRUE, 62);
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code IN ('youth_exec_office','party_exec_office','secretary_general')
  AND p.code IN ('kpis.view','kpis.approve','dashboard.view');

-- +down
DROP TABLE IF EXISTS kpi_measurements;
DROP TABLE IF EXISTS kpi_targets;
DROP TABLE IF EXISTS kpi_definitions;
DELETE FROM role_permissions WHERE role_id IN (SELECT id FROM roles WHERE code IN ('youth_exec_office','party_exec_office','secretary_general'));
DELETE FROM user_roles WHERE role_id IN (SELECT id FROM roles WHERE code IN ('youth_exec_office','party_exec_office','secretary_general'));
DELETE FROM roles WHERE code IN ('youth_exec_office','party_exec_office','secretary_general');