-- v35 Knowledge base and SOP library
CREATE TABLE IF NOT EXISTS knowledge_base_articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    article_code VARCHAR(60) NOT NULL,
    title VARCHAR(190) NOT NULL,
    category VARCHAR(80) DEFAULT 'sop',
    audience_scope VARCHAR(40) DEFAULT 'internal',
    owner_unit VARCHAR(150) DEFAULT '',
    summary TEXT NULL,
    body LONGTEXT NULL,
    effective_date DATE DEFAULT NULL,
    review_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'draft',
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY uniq_knowledge_article_code (article_code),
    INDEX idx_knowledge_tenant_status (tenant_id, status),
    INDEX idx_knowledge_category (category),
    INDEX idx_knowledge_audience (audience_scope),
    INDEX idx_knowledge_review (review_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(85, 'knowledge_base', 'View knowledge base and SOP workspace', 'knowledge_base.view'),
(86, 'knowledge_base', 'Manage knowledge base and SOP workspace', 'knowledge_base.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug IN ('knowledge_base.view','knowledge_base.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','case_worker','me_officer') AND p.slug IN ('knowledge_base.view','knowledge_base.manage'))
    OR (r.slug IN ('finance_officer','partner_user') AND p.slug='knowledge_base.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='knowledge_base.view')
);

INSERT INTO knowledge_base_articles
(tenant_id, article_code, title, category, audience_scope, owner_unit, summary, body, effective_date, review_date, status, created_by, created_at, updated_at)
VALUES
(1, 'KB-240101-SOCU01', 'SOCU bulk upload SOP', 'sop', 'internal', 'SOCU Desk / Registry Operations', 'Standard operating procedure for validating and importing external household and beneficiary files from the State Operations Coordinating Unit.', '1. Validate the SOCU file against the approved field mapping profile.
2. Check mandatory fields including household head, LGA, ward, beneficiary name, and phone where available.
3. Run duplicate screening before approval.
4. Log import batch references and exception rows.
5. Escalate unresolved mapping or identity conflicts to the tenant data manager.', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 90 DAY), 'active', 1, NOW(), NOW()),
(1, 'KB-240101-GRV01', 'Grievance handling checklist', 'checklist', 'internal', 'Case Management Unit', 'Quick checklist for intake, categorisation, escalation, and closure of grievance records.', 'Capture the complaint channel, claimant identity or anonymity preference, category, severity, SLA timeline, ownership, and supporting evidence. Escalate fraud and protection issues immediately.', CURDATE(), DATE_ADD(CURDATE(), INTERVAL 60 DAY), 'active', 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE title=VALUES(title), summary=VALUES(summary), body=VALUES(body), review_date=VALUES(review_date), status=VALUES(status), updated_at=NOW();