-- v38 Attendance Register & Participation
CREATE TABLE IF NOT EXISTS session_attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    outreach_session_id INT DEFAULT NULL,
    programme_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    card_id INT DEFAULT NULL,
    attendance_code VARCHAR(60) NOT NULL,
    participant_name VARCHAR(190) NOT NULL,
    contact_phone VARCHAR(60) DEFAULT '',
    attendance_status VARCHAR(40) DEFAULT 'expected',
    check_in_channel VARCHAR(40) DEFAULT 'manual',
    attended_at DATETIME DEFAULT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    verified_at DATETIME NULL,
    UNIQUE KEY uniq_attendance_code (attendance_code),
    INDEX idx_attendance_tenant_status (tenant_id, attendance_status),
    INDEX idx_attendance_outreach (outreach_session_id),
    INDEX idx_attendance_programme (programme_id),
    INDEX idx_attendance_beneficiary (beneficiary_id),
    INDEX idx_attendance_household (household_id),
    INDEX idx_attendance_card (card_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
    (91, 'attendance', 'View attendance and participation workspace', 'attendance.view'),
    (92, 'attendance', 'Manage attendance and participation workspace', 'attendance.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 ('attendance.view','attendance.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 ('attendance.view','attendance.manage'))
    OR (r.slug IN ('finance_officer','partner_user') AND p.slug='attendance.view')
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug='attendance.view')
);

INSERT IGNORE INTO session_attendance
(tenant_id, outreach_session_id, programme_id, beneficiary_id, household_id, card_id, attendance_code, participant_name, contact_phone, attendance_status, check_in_channel, attended_at, notes, created_by, created_at, verified_at)
SELECT 1, 1, 1, 1, 1, NULL, 'ATT-1001', 'Aisha Ibrahim', '08030000001', 'verified', 'manual', NOW(), 'Seeded attendance record linked to outreach session.', 1, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM session_attendance LIMIT 1);