-- Procurement module Phase 2
-- Purchase Returns, Vendor Bills, Supplier Payments, Supplier Contracts
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Purchase returns
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_returns` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `purchase_order_id`     BIGINT UNSIGNED     NULL,
    `goods_receipt_id`      BIGINT UNSIGNED     NULL,
    `return_date`           DATE                NOT NULL,
    `status`                ENUM('draft','submitted','approved','completed','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,
    `reason`                TEXT                NULL,
    `notes`                 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_id` (`supplier_id`),
    KEY `purchase_order_id` (`purchase_order_id`),
    KEY `goods_receipt_id` (`goods_receipt_id`),
    KEY `status` (`status`),
    KEY `return_date` (`return_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_return_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `purchase_return_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_return_id` (`purchase_return_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Vendor bills
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vendor_bills` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `purchase_order_id`     BIGINT UNSIGNED     NULL,
    `goods_receipt_id`      BIGINT UNSIGNED     NULL,
    `bill_date`             DATE                NOT NULL,
    `due_date`              DATE                NULL,
    `status`                ENUM('draft','issued','partially_paid','paid','overdue','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,
    `amount_paid`           DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `payment_notes`         TEXT                NULL,
    `notes`                 TEXT                NULL,
    `issued_at`             DATETIME            NULL,
    `paid_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_id` (`supplier_id`),
    KEY `purchase_order_id` (`purchase_order_id`),
    KEY `goods_receipt_id` (`goods_receipt_id`),
    KEY `status` (`status`),
    KEY `bill_date` (`bill_date`),
    KEY `due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vendor_bill_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `vendor_bill_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 `vendor_bill_id` (`vendor_bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Supplier payments
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `supplier_payments` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `vendor_bill_id`        BIGINT UNSIGNED     NULL,
    `payment_date`          DATE                NOT NULL,
    `amount`                DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `method`                VARCHAR(50)         NULL,
    `status`                ENUM('draft','pending','processed','cancelled') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT                NULL,
    `processed_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_id` (`supplier_id`),
    KEY `vendor_bill_id` (`vendor_bill_id`),
    KEY `status` (`status`),
    KEY `payment_date` (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Supplier contracts
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `supplier_contracts` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `title`                 VARCHAR(200)        NOT NULL,
    `supplier_id`           BIGINT UNSIGNED     NOT NULL,
    `status`                ENUM('draft','active','expired','terminated','cancelled') NOT NULL DEFAULT 'draft',
    `start_date`            DATE                NULL,
    `end_date`              DATE                NULL,
    `value`                 DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `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 `number` (`number`),
    KEY `supplier_id` (`supplier_id`),
    KEY `status` (`status`),
    KEY `start_date` (`start_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'procurement', 'procurement.approve', 'Approve Procurement Documents', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'procurement.approve');

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

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 LIKE 'purchase_returns.%'
      OR p.slug LIKE 'vendor_bills.%'
      OR p.slug LIKE 'supplier_payments.%'
      OR p.slug LIKE 'supplier_contracts.%'
      OR p.slug IN ('procurement.approve', 'procurement.reports')
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
