CREATE TABLE IF NOT EXISTS verification_appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    registration_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    appointment_code VARCHAR(60) NOT NULL,
    appointment_date DATETIME NOT NULL,
    location_name VARCHAR(255) DEFAULT '',
    contact_phone VARCHAR(50) DEFAULT '',
    purpose VARCHAR(120) DEFAULT 'Verification',
    status VARCHAR(40) DEFAULT 'scheduled',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_verification_tenant_status (tenant_id, status),
    INDEX idx_verification_code (appointment_code),
    INDEX idx_verification_registration (registration_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dedupe_cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    case_code VARCHAR(60) NOT NULL,
    record_type VARCHAR(40) NOT NULL,
    primary_record_id INT NOT NULL,
    duplicate_record_id INT NOT NULL,
    detection_rule VARCHAR(120) DEFAULT '',
    confidence_score DECIMAL(5,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'open',
    resolution_notes TEXT NULL,
    created_by INT DEFAULT 0,
    resolved_by INT DEFAULT 0,
    created_at DATETIME NULL,
    resolved_at DATETIME NULL,
    INDEX idx_dedupe_tenant_status (tenant_id, status),
    INDEX idx_dedupe_case_code (case_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(35, 'verification', 'View verification scheduler', 'verification.view'),
(36, 'verification', 'Manage verification scheduler', 'verification.manage'),
(37, 'dedupe', 'View deduplication center', 'dedupe.view'),
(38, 'dedupe', 'Manage deduplication center', 'dedupe.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 ('verification.view','verification.manage','dedupe.view','dedupe.manage')
WHERE (
    (r.slug IN ('super_admin', 'tenant_admin') AND p.slug IN ('verification.view','verification.manage','dedupe.view','dedupe.manage'))
    OR (r.slug IN ('programme_manager','field_officer') AND p.slug IN ('verification.view','verification.manage'))
    OR (r.slug IN ('auditor','read_only_viewer') AND p.slug IN ('verification.view','dedupe.view'))
);