CREATE TABLE IF NOT EXISTS assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    field_task_id INT DEFAULT NULL,
    assessment_code VARCHAR(60) NOT NULL,
    assessment_type VARCHAR(80) DEFAULT 'vulnerability_screening',
    assessment_date DATE DEFAULT NULL,
    score DECIMAL(10,2) DEFAULT 0,
    recommendation VARCHAR(80) DEFAULT 'review',
    status VARCHAR(40) DEFAULT 'submitted',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    reviewed_by INT DEFAULT 0,
    reviewed_at DATETIME NULL,
    INDEX idx_assessments_tenant_status (tenant_id, status),
    INDEX idx_assessments_code (assessment_code),
    INDEX idx_assessments_programme (programme_id),
    INDEX idx_assessments_household (household_id),
    INDEX idx_assessments_beneficiary (beneficiary_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(49, 'assessments', 'View assessments workspace', 'assessments.view'),
(50, 'assessments', 'Manage assessments workspace', 'assessments.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(2,49),(2,50),
(3,49),(3,50),
(4,49),(4,50),
(5,49),
(6,49),(6,50),
(7,49),(7,50),
(9,49),
(11,49);