CREATE TABLE IF NOT EXISTS in_kind_distributions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    household_id INT DEFAULT NULL,
    beneficiary_id INT DEFAULT NULL,
    service_point_id INT DEFAULT NULL,
    distribution_code VARCHAR(60) NOT NULL,
    item_name VARCHAR(180) NOT NULL,
    quantity DECIMAL(12,2) DEFAULT 0,
    unit VARCHAR(40) DEFAULT 'unit',
    distribution_date DATE DEFAULT NULL,
    delivery_channel VARCHAR(80) DEFAULT 'service_point',
    status VARCHAR(40) DEFAULT 'planned',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    delivered_at DATETIME NULL,
    INDEX idx_in_kind_tenant_status (tenant_id, status),
    INDEX idx_in_kind_code (distribution_code),
    INDEX idx_in_kind_programme (programme_id),
    INDEX idx_in_kind_beneficiary (beneficiary_id),
    INDEX idx_in_kind_household (household_id),
    INDEX idx_in_kind_service_point (service_point_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(51, 'distributions', 'View in-kind distribution workspace', 'distributions.view'),
(52, 'distributions', 'Manage in-kind distribution workspace', 'distributions.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 ('distributions.view','distributions.manage')
WHERE (
    (r.slug IN ('super_admin','tenant_admin','programme_manager','field_officer','case_worker','finance_officer') AND p.slug IN ('distributions.view','distributions.manage'))
    OR (r.slug IN ('auditor','read_only_viewer','partner_user','me_officer') AND p.slug='distributions.view')
);