CREATE TABLE IF NOT EXISTS vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    vendor_code VARCHAR(60) NOT NULL,
    name VARCHAR(180) NOT NULL,
    category VARCHAR(100) DEFAULT '',
    contact_person VARCHAR(150) DEFAULT '',
    email VARCHAR(150) DEFAULT '',
    phone VARCHAR(50) DEFAULT '',
    status VARCHAR(40) DEFAULT 'active',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    INDEX idx_vendors_tenant_status (tenant_id, status),
    INDEX idx_vendors_code (vendor_code),
    INDEX idx_vendors_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS procurement_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    programme_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    budget_allocation_id INT DEFAULT NULL,
    vendor_id INT DEFAULT NULL,
    request_code VARCHAR(60) NOT NULL,
    request_title VARCHAR(180) NOT NULL,
    item_summary TEXT NULL,
    quantity DECIMAL(12,2) DEFAULT 0,
    unit VARCHAR(40) DEFAULT 'unit',
    estimated_amount DECIMAL(18,2) DEFAULT 0,
    request_date DATE DEFAULT NULL,
    priority VARCHAR(30) DEFAULT 'medium',
    status VARCHAR(40) DEFAULT 'draft',
    notes TEXT NULL,
    created_by INT DEFAULT 0,
    created_at DATETIME NULL,
    approved_at DATETIME NULL,
    delivered_at DATETIME NULL,
    INDEX idx_procurement_tenant_status (tenant_id, status),
    INDEX idx_procurement_code (request_code),
    INDEX idx_procurement_programme (programme_id),
    INDEX idx_procurement_project (project_id),
    INDEX idx_procurement_budget (budget_allocation_id),
    INDEX idx_procurement_vendor (vendor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(57, 'procurement', 'View procurement and vendor workspace', 'procurement.view'),
(58, 'procurement', 'Manage procurement and vendor workspace', 'procurement.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1,57),(1,58),(2,57),(2,58),(3,57),(3,58),(5,57),(5,58),(7,57),(7,58),(9,57),(11,57);