-- Sales documents: Quotations, Price Lists, Templates, Sales Orders, Deliveries
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Price lists
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `price_lists` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `currency`              VARCHAR(3)          NOT NULL DEFAULT 'USD',
    `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 `price_list_items` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `price_list_id`         BIGINT UNSIGNED     NOT NULL,
    `item_code`             VARCHAR(50)         NULL,
    `item_name`             VARCHAR(200)        NOT NULL,
    `description`           TEXT                NULL,
    `unit`                  VARCHAR(30)         NULL DEFAULT 'pcs',
    `unit_price`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `sort_order`            INT                 NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `price_list_id` (`price_list_id`),
    KEY `item_code` (`item_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Quotation templates
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `quotation_templates` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `subject`               VARCHAR(200)        NULL,
    `intro_text`            TEXT                NULL,
    `terms_text`            TEXT                NULL,
    `default_validity_days` INT UNSIGNED        NOT NULL DEFAULT 30,
    `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Quotations
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `quotations` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `contact_id`            BIGINT UNSIGNED     NULL,
    `opportunity_id`        BIGINT UNSIGNED     NULL,
    `template_id`           BIGINT UNSIGNED     NULL,
    `price_list_id`         BIGINT UNSIGNED     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 `company_id` (`company_id`),
    KEY `status` (`status`),
    KEY `quotation_date` (`quotation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ---------------------------------------------------------------------------
-- Sales orders
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_orders` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `quotation_id`          BIGINT UNSIGNED     NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `contact_id`            BIGINT UNSIGNED     NULL,
    `order_date`            DATE                NOT NULL,
    `required_date`         DATE                NULL,
    `status`                ENUM('draft','pending_approval','approved','rejected','confirmed','in_progress','fulfilled','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,
    `tracking_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 `company_id` (`company_id`),
    KEY `quotation_id` (`quotation_id`),
    KEY `status` (`status`),
    KEY `order_date` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ---------------------------------------------------------------------------
-- Deliveries
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `deliveries` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `sales_order_id`        BIGINT UNSIGNED     NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `delivery_date`         DATE                NOT NULL,
    `status`                ENUM('draft','ready','dispatched','in_transit','delivered','cancelled') NOT NULL DEFAULT 'draft',
    `carrier`               VARCHAR(100)        NULL,
    `tracking_number`       VARCHAR(100)        NULL,
    `shipping_address`      TEXT                NULL,
    `dispatched_at`         DATETIME            NULL,
    `delivered_at`          DATETIME            NULL,
    `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 `company_id` (`company_id`),
    KEY `sales_order_id` (`sales_order_id`),
    KEY `status` (`status`),
    KEY `delivery_date` (`delivery_date`),
    KEY `tracking_number` (`tracking_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'price_lists', 'price_lists.create', 'Create Price Lists', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'price_lists.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'price_lists', 'price_lists.edit', 'Edit Price Lists', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'price_lists.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'price_lists', 'price_lists.delete', 'Delete Price Lists', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'price_lists.delete');

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'quotation_templates', 'quotation_templates.create', 'Create Quotation Templates', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'quotation_templates.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'quotation_templates', 'quotation_templates.edit', 'Edit Quotation Templates', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'quotation_templates.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'quotation_templates', 'quotation_templates.delete', 'Delete Quotation Templates', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'quotation_templates.delete');

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'quotations', 'quotations.create', 'Create Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'quotations.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'quotations', 'quotations.edit', 'Edit Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'quotations.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'quotations', 'quotations.delete', 'Delete Quotations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'quotations.delete');

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'sales_orders', 'sales_orders.create', 'Create Sales Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'sales_orders.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'sales_orders', 'sales_orders.edit', 'Edit Sales Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'sales_orders.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'sales_orders', 'sales_orders.delete', 'Delete Sales Orders', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'sales_orders.delete');

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

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

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'deliveries', 'deliveries.create', 'Create Deliveries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'deliveries.create');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'deliveries', 'deliveries.edit', 'Edit Deliveries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'deliveries.edit');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'deliveries', 'deliveries.delete', 'Delete Deliveries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'deliveries.delete');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'deliveries', 'deliveries.dispatch', 'Dispatch Deliveries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'deliveries.dispatch');

-- Grant all new permissions to administrator
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 'price_lists.%'
      OR p.slug LIKE 'quotation_templates.%'
      OR p.slug LIKE 'quotations.%'
      OR p.slug LIKE 'sales_orders.%'
      OR p.slug LIKE 'deliveries.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
