-- Procurement module Phase 1
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Suppliers
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `suppliers` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `email`                 VARCHAR(191)        NULL,
    `phone`                 VARCHAR(30)         NULL,
    `website`               VARCHAR(255)        NULL,
    `address`               VARCHAR(255)        NULL,
    `city`                  VARCHAR(100)        NULL,
    `country`               VARCHAR(100)        NULL,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `code` (`code`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_contacts` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `first_name`            VARCHAR(100)        NOT NULL,
    `last_name`             VARCHAR(100)        NOT NULL,
    `email`                 VARCHAR(191)        NULL,
    `phone`                 VARCHAR(30)         NULL,
    `job_title`             VARCHAR(100)        NULL,
    `is_primary`            TINYINT(1)          NOT NULL DEFAULT 0,
    `status`                ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `notes`                 TEXT                NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `supplier_id` (`supplier_id`),
    KEY `status` (`status`),
    KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Purchase requests
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_requests` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `request_date`          DATE                NOT NULL,
    `needed_by`             DATE                NULL,
    `department`            VARCHAR(100)        NULL,
    `status`                ENUM('draft','pending_approval','approved','rejected','converted','cancelled') NOT NULL DEFAULT 'draft',
    `subtotal`              DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `tax_amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `total`                 DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `notes`                 TEXT                NULL,
    `rejection_reason`      TEXT                NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `status` (`status`),
    KEY `request_date` (`request_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_request_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `purchase_request_id`   BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `unit`                  VARCHAR(30)         NULL DEFAULT 'pcs',
    `unit_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `line_total`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `purchase_request_id` (`purchase_request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- RFQs
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `rfqs` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `purchase_request_id`   BIGINT UNSIGNED     NULL,
    `rfq_date`              DATE                NOT NULL,
    `valid_until`           DATE                NULL,
    `status`                ENUM('draft','pending_approval','approved','rejected','sent','converted','cancelled') NOT NULL DEFAULT 'draft',
    `subtotal`              DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `tax_amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `total`                 DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `notes`                 TEXT                NULL,
    `rejection_reason`      TEXT                NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `purchase_request_id` (`purchase_request_id`),
    KEY `status` (`status`),
    KEY `rfq_date` (`rfq_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rfq_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `rfq_id`                BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `unit`                  VARCHAR(30)         NULL DEFAULT 'pcs',
    `unit_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `line_total`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `rfq_id` (`rfq_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rfq_suppliers` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `rfq_id`                BIGINT UNSIGNED     NOT NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `rfq_supplier` (`rfq_id`, `supplier_id`),
    KEY `supplier_id` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Supplier quotations
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `supplier_quotations` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `rfq_id`                BIGINT UNSIGNED     NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `quotation_date`        DATE                NOT NULL,
    `valid_until`           DATE                NULL,
    `status`                ENUM('draft','pending_approval','approved','rejected','sent','converted','cancelled') NOT NULL DEFAULT 'draft',
    `subtotal`              DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `tax_amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `total`                 DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `notes`                 TEXT                NULL,
    `rejection_reason`      TEXT                NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `rfq_id` (`rfq_id`),
    KEY `supplier_id` (`supplier_id`),
    KEY `status` (`status`),
    KEY `quotation_date` (`quotation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_quotation_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `supplier_quotation_id` BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `unit`                  VARCHAR(30)         NULL DEFAULT 'pcs',
    `unit_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `line_total`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `supplier_quotation_id` (`supplier_quotation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Purchase orders
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_orders` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `supplier_quotation_id` BIGINT UNSIGNED     NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `order_date`            DATE                NOT NULL,
    `expected_date`         DATE                NULL,
    `status`                ENUM('draft','pending_approval','approved','rejected','confirmed','cancelled') NOT NULL DEFAULT 'draft',
    `subtotal`              DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `tax_amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `total`                 DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `notes`                 TEXT                NULL,
    `rejection_reason`      TEXT                NULL,
    `approved_by`           BIGINT UNSIGNED     NULL,
    `approved_at`           DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `supplier_quotation_id` (`supplier_quotation_id`),
    KEY `supplier_id` (`supplier_id`),
    KEY `status` (`status`),
    KEY `order_date` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_order_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `purchase_order_id`     BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `unit`                  VARCHAR(30)         NULL DEFAULT 'pcs',
    `unit_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `line_total`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `purchase_order_id` (`purchase_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Goods receipts (GRN)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `goods_receipts` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `purchase_order_id`     BIGINT UNSIGNED     NOT NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `receipt_date`          DATE                NOT NULL,
    `status`                ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT                NULL,
    `posted_by`             BIGINT UNSIGNED     NULL,
    `posted_at`             DATETIME            NULL,
    `created_by`            BIGINT UNSIGNED     NULL,
    `updated_by`            BIGINT UNSIGNED     NULL,
    `created_at`            DATETIME            NULL,
    `updated_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `number` (`number`),
    KEY `purchase_order_id` (`purchase_order_id`),
    KEY `supplier_id` (`supplier_id`),
    KEY `status` (`status`),
    KEY `receipt_date` (`receipt_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `goods_receipt_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `goods_receipt_id`      BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `quantity`              DECIMAL(12,3)       NOT NULL DEFAULT 1.000,
    `unit`                  VARCHAR(30)         NULL DEFAULT 'pcs',
    `notes`                 TEXT                NULL,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `goods_receipt_id` (`goods_receipt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Permissions
-- ---------------------------------------------------------------------------
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'procurement', 'procurement.view', 'View Procurement Dashboard', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'procurement.view');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'suppliers', 'suppliers.view', 'View Suppliers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'suppliers.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'suppliers', 'suppliers.create', 'Create Suppliers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'suppliers.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'suppliers', 'suppliers.edit', 'Edit Suppliers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'suppliers.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'suppliers', 'suppliers.delete', 'Delete Suppliers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'suppliers.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_contacts', 'supplier_contacts.view', 'View Supplier Contacts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_contacts.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_contacts', 'supplier_contacts.create', 'Create Supplier Contacts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_contacts.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_contacts', 'supplier_contacts.edit', 'Edit Supplier Contacts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_contacts.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_contacts', 'supplier_contacts.delete', 'Delete Supplier Contacts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_contacts.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_requests', 'purchase_requests.view', 'View Purchase Requests', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_requests.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_requests', 'purchase_requests.create', 'Create Purchase Requests', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_requests.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_requests', 'purchase_requests.edit', 'Edit Purchase Requests', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_requests.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_requests', 'purchase_requests.delete', 'Delete Purchase Requests', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_requests.delete');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_requests', 'purchase_requests.approve', 'Approve Purchase Requests', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_requests.approve');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'rfqs', 'rfqs.view', 'View RFQs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'rfqs.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'rfqs', 'rfqs.create', 'Create RFQs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'rfqs.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'rfqs', 'rfqs.edit', 'Edit RFQs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'rfqs.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'rfqs', 'rfqs.delete', 'Delete RFQs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'rfqs.delete');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'rfqs', 'rfqs.approve', 'Approve RFQs', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'rfqs.approve');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_quotations', 'supplier_quotations.view', 'View Supplier Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_quotations.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_quotations', 'supplier_quotations.create', 'Create Supplier Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_quotations.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_quotations', 'supplier_quotations.edit', 'Edit Supplier Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_quotations.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_quotations', 'supplier_quotations.delete', 'Delete Supplier Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_quotations.delete');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_quotations', 'supplier_quotations.approve', 'Approve Supplier Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_quotations.approve');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_orders', 'purchase_orders.view', 'View Purchase Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_orders.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_orders', 'purchase_orders.create', 'Create Purchase Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_orders.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_orders', 'purchase_orders.edit', 'Edit Purchase Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_orders.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_orders', 'purchase_orders.delete', 'Delete Purchase Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_orders.delete');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'purchase_orders', 'purchase_orders.approve', 'Approve Purchase Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'purchase_orders.approve');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'goods_receipts', 'goods_receipts.view', 'View Goods Receipts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'goods_receipts.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'goods_receipts', 'goods_receipts.create', 'Create Goods Receipts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'goods_receipts.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'goods_receipts', 'goods_receipts.edit', 'Edit Goods Receipts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'goods_receipts.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'goods_receipts', 'goods_receipts.delete', 'Delete Goods Receipts', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'goods_receipts.delete');

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND (
      p.slug = 'procurement.view'
      OR p.slug LIKE 'suppliers.%'
      OR p.slug LIKE 'supplier_contacts.%'
      OR p.slug LIKE 'purchase_requests.%'
      OR p.slug LIKE 'rfqs.%'
      OR p.slug LIKE 'supplier_quotations.%'
      OR p.slug LIKE 'purchase_orders.%'
      OR p.slug LIKE 'goods_receipts.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
