-- v31: outcome tracking and results
CREATE TABLE IF NOT EXISTS outcome_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  programme_id INT DEFAULT NULL,
  project_id INT DEFAULT NULL,
  beneficiary_id INT DEFAULT NULL,
  household_id INT DEFAULT NULL,
  partner_id INT DEFAULT NULL,
  indicator_id INT DEFAULT NULL,
  outcome_code VARCHAR(60) NOT NULL,
  outcome_type VARCHAR(80) DEFAULT 'programme_outcome',
  title VARCHAR(180) NOT NULL,
  result_period VARCHAR(100) DEFAULT '',
  baseline_value DECIMAL(18,2) DEFAULT 0,
  target_value DECIMAL(18,2) DEFAULT 0,
  actual_value DECIMAL(18,2) DEFAULT 0,
  unit VARCHAR(40) DEFAULT '',
  verification_status VARCHAR(40) DEFAULT 'pending',
  outcome_status VARCHAR(40) DEFAULT 'on_track',
  evidence_note TEXT NULL,
  notes TEXT NULL,
  created_by INT DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  verified_at DATETIME NULL,
  UNIQUE KEY uniq_outcome_code (outcome_code),
  INDEX idx_outcomes_tenant_status (tenant_id, outcome_status),
  INDEX idx_outcomes_verification (tenant_id, verification_status),
  INDEX idx_outcomes_programme (programme_id),
  INDEX idx_outcomes_project (project_id),
  INDEX idx_outcomes_beneficiary (beneficiary_id),
  INDEX idx_outcomes_household (household_id),
  INDEX idx_outcomes_partner (partner_id),
  INDEX idx_outcomes_indicator (indicator_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (id, module, name, slug) VALUES
(79, 'outcomes', 'View outcome tracking and results', 'outcomes.view'),
(80, 'outcomes', 'Manage outcome tracking and results', 'outcomes.manage')
ON DUPLICATE KEY UPDATE module=VALUES(module), name=VALUES(name), slug=VALUES(slug);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('outcomes.view','outcomes.manage')
LEFT JOIN role_permissions rp ON rp.role_id=r.id AND rp.permission_id=p.id
WHERE rp.id IS NULL AND (
  (r.slug IN ('super_admin','tenant_admin','programme_manager','field_officer','me_officer') AND p.slug IN ('outcomes.view','outcomes.manage'))
  OR (r.slug IN ('finance_officer','case_worker') AND p.slug='outcomes.view')
  OR (r.slug='partner_user' AND p.slug='outcomes.view')
  OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='outcomes.view')
);

INSERT INTO outcome_results
(tenant_id, programme_id, project_id, beneficiary_id, household_id, partner_id, indicator_id, outcome_code, outcome_type, title, result_period, baseline_value, target_value, actual_value, unit, verification_status, outcome_status, evidence_note, notes, created_by, created_at, updated_at, verified_at)
SELECT
  1,
  1,
  (SELECT id FROM projects WHERE tenant_id=1 ORDER BY id ASC LIMIT 1),
  1,
  1,
  (SELECT id FROM partners WHERE tenant_id=1 ORDER BY id ASC LIMIT 1),
  (SELECT id FROM indicators WHERE tenant_id=1 ORDER BY id ASC LIMIT 1),
  'OUT-1001',
  'programme_outcome',
  'Households with improved food security status',
  'Q2 2026',
  250.00,
  1000.00,
  740.00,
  'households',
  'verified',
  'on_track',
  'Quarterly household verification and monitoring visits show improved food consumption scores among enrolled households.',
  'Demo outcome record seeded for the outcome tracking and results workspace.',
  1,
  NOW(),
  NOW(),
  NOW()
WHERE NOT EXISTS (SELECT 1 FROM outcome_results WHERE outcome_code='OUT-1001');