-- Upgrade v17: warehouse inventory and stock control
CREATE TABLE IF NOT EXISTS inventory_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    service_point_id INT DEFAULT NULL,
    programme_id INT DEFAULT NULL,
    item_code VARCHAR(60) NOT NULL,
    item_name VARCHAR(180) NOT NULL,
    category VARCHAR(100) DEFAULT '',
    unit VARCHAR(40) DEFAULT 'unit',
    opening_balance DECIMAL(12,2) DEFAULT 0,
    current_balance DECIMAL(12,2) DEFAULT 0,
    reorder_level DECIMAL(12,2) DEFAULT 0,
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_inventory_items_tenant_status (tenant_id, status),
    INDEX idx_inventory_items_code (item_code),
    INDEX idx_inventory_items_service_point (service_point_id),
    INDEX idx_inventory_items_programme (programme_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    inventory_item_id INT NOT NULL,
    service_point_id INT DEFAULT NULL,
    programme_id INT DEFAULT NULL,
    transaction_code VARCHAR(60) NOT NULL,
    transaction_type VARCHAR(50) DEFAULT 'receipt',
    quantity DECIMAL(12,2) DEFAULT 0,
    reference_type VARCHAR(80) DEFAULT '',
    reference_id INT DEFAULT NULL,
    transaction_date DATE DEFAULT NULL,
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_stock_txn_tenant_type (tenant_id, transaction_type),
    INDEX idx_stock_txn_item (inventory_item_id),
    INDEX idx_stock_txn_code (transaction_code),
    INDEX idx_stock_txn_date (transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(53, 'inventory', 'View inventory and stock workspace', 'inventory.view'),
(54, 'inventory', 'Manage inventory and stock workspace', 'inventory.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 ('inventory.view','inventory.manage')
WHERE (
    (r.slug IN ('super_admin','tenant_admin','programme_manager','finance_officer','case_worker','field_officer') AND p.slug IN ('inventory.view','inventory.manage'))
    OR (r.slug IN ('auditor','read_only_viewer','partner_user') AND p.slug='inventory.view')
);