-- Accounting module: General Ledger tables + permissions
-- Run against your ERP database (e.g. erp_foundation)
-- Safe to re-run: CREATE IF NOT EXISTS + INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- General Ledger tables
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `chart_of_accounts` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`          VARCHAR(30)     NOT NULL,
    `name`          VARCHAR(150)    NOT NULL,
    `type`          ENUM('asset','liability','equity','revenue','expense') NOT NULL,
    `parent_id`     BIGINT UNSIGNED NULL,
    `is_postable`   TINYINT(1)      NOT NULL DEFAULT 1,
    `status`        ENUM('active','inactive') NOT NULL DEFAULT 'active',
    `description`   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 `type` (`type`),
    KEY `parent_id` (`parent_id`),
    KEY `status` (`status`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fiscal_years` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`          VARCHAR(100)    NOT NULL,
    `code`          VARCHAR(30)     NOT NULL,
    `start_date`    DATE            NOT NULL,
    `end_date`      DATE            NOT NULL,
    `status`        ENUM('open','closed') NOT NULL DEFAULT 'open',
    `is_current`    TINYINT(1)      NOT NULL DEFAULT 0,
    `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 `is_current` (`is_current`),
    KEY `start_date` (`start_date`),
    KEY `end_date` (`end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_entries` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)     NOT NULL,
    `entry_date`        DATE            NOT NULL,
    `fiscal_year_id`    BIGINT UNSIGNED NULL,
    `reference`         VARCHAR(100)    NULL,
    `description`       TEXT            NULL,
    `status`            ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    `total_debit`       DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `total_credit`      DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `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 `entry_date` (`entry_date`),
    KEY `fiscal_year_id` (`fiscal_year_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `journal_entry_lines` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `journal_entry_id`  BIGINT UNSIGNED NOT NULL,
    `account_id`        BIGINT UNSIGNED NOT NULL,
    `description`       VARCHAR(255)    NULL,
    `debit`             DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `credit`            DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `sort_order`        INT             NOT NULL DEFAULT 0,
    `created_at`        DATETIME        NULL,
    PRIMARY KEY (`id`),
    KEY `journal_entry_id` (`journal_entry_id`),
    KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Receivables (AR)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `customer_invoices` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)     NOT NULL,
    `company_id`        BIGINT UNSIGNED NOT NULL,
    `invoice_date`      DATE            NOT NULL,
    `due_date`          DATE            NULL,
    `status`            ENUM('draft','posted','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,
    `notes`             TEXT            NULL,
    `posted_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 `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 `customer_invoice_lines` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_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 `customer_invoice_id` (`customer_invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_receipts` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)     NOT NULL,
    `company_id`            BIGINT UNSIGNED NOT NULL,
    `customer_invoice_id`   BIGINT UNSIGNED NULL,
    `receipt_date`          DATE            NOT NULL,
    `amount`                DECIMAL(15,2)   NOT NULL DEFAULT 0.00,
    `method`                VARCHAR(50)     NULL,
    `status`                ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    `notes`                 TEXT            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 `company_id` (`company_id`),
    KEY `customer_invoice_id` (`customer_invoice_id`),
    KEY `status` (`status`),
    KEY `receipt_date` (`receipt_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `credit_notes` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)     NOT NULL,
    `company_id`        BIGINT UNSIGNED NOT NULL,
    `customer_invoice_id` BIGINT UNSIGNED NULL,
    `credit_date`       DATE            NOT NULL,
    `status`            ENUM('draft','posted','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,
    `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 `company_id` (`company_id`),
    KEY `customer_invoice_id` (`customer_invoice_id`),
    KEY `status` (`status`),
    KEY `credit_date` (`credit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ---------------------------------------------------------------------------
-- Payables (AP)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `supplier_bills` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)     NOT NULL,
    `supplier_id`       BIGINT UNSIGNED NOT NULL,
    `bill_date`         DATE            NOT NULL,
    `due_date`          DATE            NULL,
    `status`            ENUM('draft','posted','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,
    `notes`             TEXT            NULL,
    `posted_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 `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 `supplier_bill_lines` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `supplier_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 `supplier_bill_id` (`supplier_bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ap_payments` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)     NOT NULL,
    `supplier_id`       BIGINT UNSIGNED NOT NULL,
    `supplier_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','posted','cancelled') NOT NULL DEFAULT 'draft',
    `notes`             TEXT            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 `supplier_id` (`supplier_id`),
    KEY `supplier_bill_id` (`supplier_bill_id`),
    KEY `status` (`status`),
    KEY `payment_date` (`payment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `debit_notes` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)     NOT NULL,
    `supplier_id`       BIGINT UNSIGNED NOT NULL,
    `supplier_bill_id`  BIGINT UNSIGNED NULL,
    `debit_date`        DATE            NOT NULL,
    `status`            ENUM('draft','posted','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,
    `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 `supplier_id` (`supplier_id`),
    KEY `supplier_bill_id` (`supplier_bill_id`),
    KEY `status` (`status`),
    KEY `debit_date` (`debit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- ---------------------------------------------------------------------------
-- Permissions
-- ---------------------------------------------------------------------------

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

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

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

-- Journal entries
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'journal_entries', 'journal_entries.view', 'View Journal Entries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'journal_entries.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'journal_entries', 'journal_entries.create', 'Create Journal Entries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'journal_entries.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'journal_entries', 'journal_entries.edit', 'Edit Journal Entries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'journal_entries.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'journal_entries', 'journal_entries.post', 'Post Journal Entries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'journal_entries.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'journal_entries', 'journal_entries.delete', 'Delete Journal Entries', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'journal_entries.delete');

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

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

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

-- Bank reconciliations
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'bank_reconciliations', 'bank_reconciliations.view', 'View Bank Reconciliations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'bank_reconciliations.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'bank_reconciliations', 'bank_reconciliations.create', 'Create Bank Reconciliations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'bank_reconciliations.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'bank_reconciliations', 'bank_reconciliations.edit', 'Edit Bank Reconciliations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'bank_reconciliations.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'bank_reconciliations', 'bank_reconciliations.complete', 'Complete Bank Reconciliations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'bank_reconciliations.complete');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'bank_reconciliations', 'bank_reconciliations.delete', 'Delete Bank Reconciliations', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'bank_reconciliations.delete');

-- Payment vouchers
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payment_vouchers', 'payment_vouchers.view', 'View Payment Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payment_vouchers.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payment_vouchers', 'payment_vouchers.create', 'Create Payment Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payment_vouchers.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payment_vouchers', 'payment_vouchers.edit', 'Edit Payment Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payment_vouchers.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payment_vouchers', 'payment_vouchers.post', 'Post Payment Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payment_vouchers.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payment_vouchers', 'payment_vouchers.delete', 'Delete Payment Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payment_vouchers.delete');

-- Receipt vouchers
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'receipt_vouchers', 'receipt_vouchers.view', 'View Receipt Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'receipt_vouchers.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'receipt_vouchers', 'receipt_vouchers.create', 'Create Receipt Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'receipt_vouchers.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'receipt_vouchers', 'receipt_vouchers.edit', 'Edit Receipt Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'receipt_vouchers.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'receipt_vouchers', 'receipt_vouchers.post', 'Post Receipt Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'receipt_vouchers.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'receipt_vouchers', 'receipt_vouchers.delete', 'Delete Receipt Vouchers', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'receipt_vouchers.delete');

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

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

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

-- Receivables: Customer invoices
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_invoices', 'customer_invoices.view', 'View Customer Invoices', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_invoices.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_invoices', 'customer_invoices.create', 'Create Customer Invoices', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_invoices.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_invoices', 'customer_invoices.edit', 'Edit Customer Invoices', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_invoices.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_invoices', 'customer_invoices.post', 'Post Customer Invoices', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_invoices.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_invoices', 'customer_invoices.delete', 'Delete Customer Invoices', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_invoices.delete');

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

-- Receivables: Credit notes
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'credit_notes', 'credit_notes.view', 'View Credit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'credit_notes.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'credit_notes', 'credit_notes.create', 'Create Credit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'credit_notes.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'credit_notes', 'credit_notes.edit', 'Edit Credit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'credit_notes.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'credit_notes', 'credit_notes.post', 'Post Credit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'credit_notes.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'credit_notes', 'credit_notes.delete', 'Delete Credit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'credit_notes.delete');

-- Receivables: Statements & aging
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'customer_statements', 'customer_statements.view', 'View Customer Statements', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'customer_statements.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'receivables_aging', 'receivables_aging.view', 'View Receivables Aging Report', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'receivables_aging.view');

-- Payables: Supplier bills
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_bills', 'supplier_bills.view', 'View Supplier Bills', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_bills.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_bills', 'supplier_bills.create', 'Create Supplier Bills', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_bills.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_bills', 'supplier_bills.edit', 'Edit Supplier Bills', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_bills.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_bills', 'supplier_bills.post', 'Post Supplier Bills', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_bills.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_bills', 'supplier_bills.delete', 'Delete Supplier Bills', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_bills.delete');

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

-- Payables: Debit notes
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'debit_notes', 'debit_notes.view', 'View Debit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'debit_notes.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'debit_notes', 'debit_notes.create', 'Create Debit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'debit_notes.create');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'debit_notes', 'debit_notes.edit', 'Edit Debit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'debit_notes.edit');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'debit_notes', 'debit_notes.post', 'Post Debit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'debit_notes.post');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'debit_notes', 'debit_notes.delete', 'Delete Debit Notes', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'debit_notes.delete');

-- Payables: Statements & aging
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'supplier_statements', 'supplier_statements.view', 'View Supplier Statements', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'supplier_statements.view');
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'payables_aging', 'payables_aging.view', 'View Payables Aging Report', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'payables_aging.view');

-- Grant all accounting 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 = 'accounting.view'
      OR p.slug = 'accounting.reports'
      OR p.slug LIKE 'chart_of_accounts.%'
      OR p.slug LIKE 'journal_entries.%'
      OR p.slug LIKE 'fiscal_years.%'
      OR p.slug LIKE 'cost_centers.%'
      OR p.slug LIKE 'bank_accounts.%'
      OR p.slug LIKE 'bank_reconciliations.%'
      OR p.slug LIKE 'payment_vouchers.%'
      OR p.slug LIKE 'receipt_vouchers.%'
      OR p.slug LIKE 'expenses.%'
      OR p.slug LIKE 'taxes.%'
      OR p.slug LIKE 'budgets.%'
      OR p.slug LIKE 'customer_invoices.%'
      OR p.slug LIKE 'receipts.%'
      OR p.slug LIKE 'credit_notes.%'
      OR p.slug LIKE 'customer_statements.%'
      OR p.slug LIKE 'receivables_aging.%'
      OR p.slug LIKE 'supplier_bills.%'
      OR p.slug LIKE 'payments.%'
      OR p.slug LIKE 'debit_notes.%'
      OR p.slug LIKE 'supplier_statements.%'
      OR p.slug LIKE 'payables_aging.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
