CREATE TABLE IF NOT EXISTS risk_register (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    agreement_id INT DEFAULT NULL,
    risk_code VARCHAR(60) NOT NULL,
    record_type VARCHAR(20) DEFAULT 'risk',
    title VARCHAR(180) NOT NULL,
    category VARCHAR(80) DEFAULT 'operational',
    probability VARCHAR(20) DEFAULT 'medium',
    impact_level VARCHAR(20) DEFAULT 'medium',
    severity VARCHAR(20) DEFAULT 'moderate',
    owner_name VARCHAR(150) DEFAULT '',
    due_date DATE DEFAULT NULL,
    status VARCHAR(40) DEFAULT 'open',
    mitigation_plan TEXT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL,
    INDEX idx_risk_register_tenant_status (tenant_id, status),
    INDEX idx_risk_register_code (risk_code),
    INDEX idx_risk_register_programme (programme_id),
    INDEX idx_risk_register_project (project_id),
    INDEX idx_risk_register_agreement (agreement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(61, 'risks', 'View risk and issue register', 'risks.view'),
(62, 'risks', 'Manage risk and issue register', 'risks.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1,61),(1,62),(2,61),(2,62),(3,61),(3,62),(5,61),(5,62),(6,61),(7,61),(7,62),(8,61),(9,61),(11,61);