-- Upgrade v14: Referrals module
CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    partner_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    referral_code VARCHAR(60) NOT NULL,
    referral_type VARCHAR(80) DEFAULT 'case_management',
    title VARCHAR(180) NOT NULL,
    referred_to VARCHAR(180) DEFAULT '',
    referral_date DATE DEFAULT NULL,
    priority VARCHAR(30) DEFAULT 'medium',
    status VARCHAR(40) DEFAULT 'submitted',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    closed_at DATETIME NULL,
    INDEX idx_referrals_tenant_status (tenant_id, status),
    INDEX idx_referrals_code (referral_code),
    INDEX idx_referrals_partner (partner_id),
    INDEX idx_referrals_service_point (service_point_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(47, 'referrals', 'View referrals workspace', 'referrals.view'),
(48, 'referrals', 'Manage referrals workspace', 'referrals.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(2,47),(2,48),
(3,47),(3,48),
(4,47),(4,48),
(5,47),
(6,47),(6,48),
(7,47),(7,48),
(9,47),
(10,47),
(11,47);