CREATE TABLE IF NOT EXISTS service_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    name VARCHAR(180) NOT NULL,
    point_type VARCHAR(80) DEFAULT 'service_center',
    state VARCHAR(120) DEFAULT '',
    lga VARCHAR(120) DEFAULT '',
    ward VARCHAR(120) DEFAULT '',
    community VARCHAR(150) DEFAULT '',
    address VARCHAR(255) DEFAULT '',
    latitude VARCHAR(40) DEFAULT '',
    longitude VARCHAR(40) DEFAULT '',
    contact_person VARCHAR(150) DEFAULT '',
    contact_phone VARCHAR(50) DEFAULT '',
    capacity_per_day INT DEFAULT 0,
    operating_days VARCHAR(150) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_service_points_tenant_status (tenant_id, status),
    INDEX idx_service_points_programme (programme_id),
    INDEX idx_service_points_type (point_type),
    INDEX idx_service_points_lga (lga)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(45, 'service_points', 'View service points', 'service_points.view'),
(46, 'service_points', 'Manage service points', 'service_points.manage');

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

INSERT INTO service_points (tenant_id, programme_id, name, point_type, state, lga, ward, community, address, latitude, longitude, contact_person, contact_phone, capacity_per_day, operating_days, status, notes, created_by, created_at)
SELECT 1, 1, 'Maiduguri Central Registration Hub', 'registration_center', 'Borno', 'Maiduguri', 'Bolori', 'Custom Area', 'Near State Secretariat Annex, Maiduguri', '11.8469', '13.1603', 'Desk Supervisor', '08031230001', 280, 'Mon-Fri, 8am-4pm', 'active', 'Primary centre for registration, document review, and appointment support.', 2, NOW()
WHERE NOT EXISTS (SELECT 1 FROM service_points WHERE tenant_id=1 AND name='Maiduguri Central Registration Hub');