-- Sales: Invoices, Returns, Sales Team
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Invoices
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoices` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `sales_order_id`        BIGINT UNSIGNED     NULL,
    `invoice_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 `company_id` (`company_id`),
    KEY `sales_order_id` (`sales_order_id`),
    KEY `status` (`status`),
    KEY `invoice_date` (`invoice_date`),
    KEY `due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ---------------------------------------------------------------------------
-- Sales returns
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_returns` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `invoice_id`            BIGINT UNSIGNED     NULL,
    `sales_order_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 `company_id` (`company_id`),
    KEY `invoice_id` (`invoice_id`),
    KEY `sales_order_id` (`sales_order_id`),
    KEY `status` (`status`),
    KEY `return_date` (`return_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ---------------------------------------------------------------------------
-- Refunds
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `refunds` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `company_id`            BIGINT UNSIGNED     NOT NULL,
    `invoice_id`            BIGINT UNSIGNED     NULL,
    `sales_return_id`       BIGINT UNSIGNED     NULL,
    `refund_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 `company_id` (`company_id`),
    KEY `invoice_id` (`invoice_id`),
    KEY `sales_return_id` (`sales_return_id`),
    KEY `status` (`status`),
    KEY `refund_date` (`refund_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Sales team: territories
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_territories` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `region`                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;

-- ---------------------------------------------------------------------------
-- Sales representatives
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_representatives` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`                  VARCHAR(150)        NOT NULL,
    `code`                  VARCHAR(30)         NULL,
    `employee_id`           BIGINT UNSIGNED     NULL,
    `territory_id`          BIGINT UNSIGNED     NULL,
    `email`                 VARCHAR(150)        NULL,
    `phone`                 VARCHAR(50)         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 `employee_id` (`employee_id`),
    KEY `territory_id` (`territory_id`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Sales targets
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_targets` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `sales_rep_id`          BIGINT UNSIGNED     NOT NULL,
    `territory_id`          BIGINT UNSIGNED     NULL,
    `period_start`          DATE                NOT NULL,
    `period_end`            DATE                NOT NULL,
    `target_amount`         DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `achieved_amount`       DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`                ENUM('active','completed','cancelled') 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 `sales_rep_id` (`sales_rep_id`),
    KEY `territory_id` (`territory_id`),
    KEY `status` (`status`),
    KEY `period_start` (`period_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Sales commissions
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sales_commissions` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `sales_rep_id`          BIGINT UNSIGNED     NOT NULL,
    `period_start`          DATE                NOT NULL,
    `period_end`            DATE                NOT NULL,
    `base_amount`           DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `rate_percent`          DECIMAL(8,2)        NOT NULL DEFAULT 0.00,
    `commission_amount`     DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`                ENUM('draft','approved','paid','cancelled') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT                NULL,
    `approved_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`),
    KEY `sales_rep_id` (`sales_rep_id`),
    KEY `status` (`status`),
    KEY `period_start` (`period_start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

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

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

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

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

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

-- Permissions: sales_team (reps, targets, commissions, territories)
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'sales_team', 'sales_team.view', 'View Sales Team', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'sales_team.view');

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

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

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

-- 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 'invoices.%'
      OR p.slug LIKE 'sales_returns.%'
      OR p.slug LIKE 'refunds.%'
      OR p.slug LIKE 'sales_team.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
