-- Accounting module Phase 2
-- Banking, Fixed Assets, Taxes
-- Safe to re-run: uses IF NOT EXISTS / INSERT ... WHERE NOT EXISTS

-- ---------------------------------------------------------------------------
-- Banking
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `bank_accounts` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `code`              VARCHAR(30)         NOT NULL,
    `name`              VARCHAR(150)        NOT NULL,
    `bank_name`         VARCHAR(150)        NULL,
    `account_number`    VARCHAR(80)         NULL,
    `branch`            VARCHAR(150)        NULL,
    `currency`          VARCHAR(10)         NOT NULL DEFAULT 'USD',
    `account_id`        BIGINT UNSIGNED     NOT NULL,
    `opening_balance`   DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `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 `account_id` (`account_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cash_accounts` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `code`              VARCHAR(30)         NOT NULL,
    `name`              VARCHAR(150)        NOT NULL,
    `currency`          VARCHAR(10)         NOT NULL DEFAULT 'USD',
    `account_id`        BIGINT UNSIGNED     NOT NULL,
    `opening_balance`   DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `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 `account_id` (`account_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bank_transfers` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `from_type`         ENUM('bank','cash') NOT NULL,
    `from_id`           BIGINT UNSIGNED     NOT NULL,
    `to_type`           ENUM('bank','cash') NOT NULL,
    `to_id`             BIGINT UNSIGNED     NOT NULL,
    `transfer_date`     DATE                NOT NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `reference`         VARCHAR(100)        NULL,
    `notes`             TEXT                NULL,
    `status`            ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    `journal_entry_id`  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 `status` (`status`),
    KEY `transfer_date` (`transfer_date`),
    KEY `from_type_id` (`from_type`, `from_id`),
    KEY `to_type_id` (`to_type`, `to_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bank_reconciliations` (
    `id`                        BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                    VARCHAR(40)         NOT NULL,
    `bank_account_id`           BIGINT UNSIGNED     NOT NULL,
    `statement_date`            DATE                NOT NULL,
    `statement_ending_balance`  DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `book_balance`              DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`                    ENUM('draft','completed','cancelled') NOT NULL DEFAULT 'draft',
    `notes`                     TEXT                NULL,
    `completed_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 `bank_account_id` (`bank_account_id`),
    KEY `status` (`status`),
    KEY `statement_date` (`statement_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bank_reconciliation_lines` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `reconciliation_id`     BIGINT UNSIGNED     NOT NULL,
    `journal_entry_line_id` BIGINT UNSIGNED     NULL,
    `txn_date`              DATE                NULL,
    `description`           VARCHAR(255)        NULL,
    `amount`                DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `is_cleared`            TINYINT(1)          NOT NULL DEFAULT 0,
    `created_at`            DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `reconciliation_id` (`reconciliation_id`),
    KEY `journal_entry_line_id` (`journal_entry_line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cheques` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `cheque_number`     VARCHAR(60)         NOT NULL,
    `direction`         ENUM('issued','received') NOT NULL,
    `party_name`        VARCHAR(200)        NOT NULL,
    `bank_account_id`   BIGINT UNSIGNED     NULL,
    `cash_account_id`   BIGINT UNSIGNED     NULL,
    `cheque_date`       DATE                NOT NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`            ENUM('registered','issued','cleared','bounced','cancelled') NOT NULL DEFAULT 'registered',
    `cleared_at`        DATETIME            NULL,
    `journal_entry_id`  BIGINT UNSIGNED     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 `cheque_number` (`cheque_number`),
    KEY `bank_account_id` (`bank_account_id`),
    KEY `status` (`status`),
    KEY `direction` (`direction`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Fixed Assets
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `asset_categories` (
    `id`                        BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `code`                      VARCHAR(30)         NOT NULL,
    `name`                      VARCHAR(150)        NOT NULL,
    `depreciation_method`       ENUM('straight_line') NOT NULL DEFAULT 'straight_line',
    `useful_life_months`        INT                 NOT NULL DEFAULT 60,
    `salvage_percent`           DECIMAL(5,2)        NOT NULL DEFAULT 0.00,
    `asset_account_id`          BIGINT UNSIGNED     NULL,
    `accum_depr_account_id`     BIGINT UNSIGNED     NULL,
    `depr_expense_account_id`   BIGINT UNSIGNED     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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fixed_assets` (
    `id`                        BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                    VARCHAR(40)         NOT NULL,
    `name`                      VARCHAR(200)        NOT NULL,
    `category_id`               BIGINT UNSIGNED     NOT NULL,
    `purchase_date`             DATE                NOT NULL,
    `in_service_date`           DATE                NULL,
    `cost`                      DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `salvage_value`             DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `useful_life_months`        INT                 NULL,
    `accum_depreciation`        DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `book_value`                DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`                    ENUM('active','disposed','fully_depreciated') NOT NULL DEFAULT 'active',
    `location`                  VARCHAR(200)        NULL,
    `asset_account_id`          BIGINT UNSIGNED     NULL,
    `accum_depr_account_id`     BIGINT UNSIGNED     NULL,
    `depr_expense_account_id`   BIGINT UNSIGNED     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 `category_id` (`category_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `depreciation_runs` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `period_start`      DATE                NOT NULL,
    `period_end`        DATE                NOT NULL,
    `run_date`          DATE                NOT NULL,
    `total_amount`      DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`            ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    `journal_entry_id`  BIGINT UNSIGNED     NULL,
    `posted_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 `status` (`status`),
    KEY `period_start` (`period_start`),
    KEY `period_end` (`period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `depreciation_run_lines` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `run_id`            BIGINT UNSIGNED     NOT NULL,
    `fixed_asset_id`    BIGINT UNSIGNED     NOT NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `book_value_after`  DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `created_at`        DATETIME            NULL,
    PRIMARY KEY (`id`),
    KEY `run_id` (`run_id`),
    KEY `fixed_asset_id` (`fixed_asset_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asset_disposals` (
    `id`                    BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`                VARCHAR(40)         NOT NULL,
    `fixed_asset_id`        BIGINT UNSIGNED     NOT NULL,
    `disposal_date`         DATE                NOT NULL,
    `proceeds`              DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `cost`                  DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `accum_depreciation`    DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `gain_loss`             DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `proceeds_account_id`   BIGINT UNSIGNED     NULL,
    `gain_loss_account_id`  BIGINT UNSIGNED     NULL,
    `status`                ENUM('draft','posted','cancelled') NOT NULL DEFAULT 'draft',
    `journal_entry_id`      BIGINT UNSIGNED     NULL,
    `posted_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 `fixed_asset_id` (`fixed_asset_id`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Taxes
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `tax_rates` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `code`              VARCHAR(30)         NOT NULL,
    `name`              VARCHAR(150)        NOT NULL,
    `rate`              DECIMAL(8,4)        NOT NULL DEFAULT 0.0000,
    `tax_type`          ENUM('vat','sales','withholding','other') NOT NULL DEFAULT 'vat',
    `account_id`        BIGINT UNSIGNED     NULL,
    `is_recoverable`    TINYINT(1)          NOT NULL DEFAULT 1,
    `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 `tax_type` (`tax_type`),
    KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vat_returns` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `period_from`       DATE                NOT NULL,
    `period_to`         DATE                NOT NULL,
    `output_vat`        DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `input_vat`         DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `net_vat`           DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`            ENUM('draft','submitted','filed','cancelled') NOT NULL DEFAULT 'draft',
    `submitted_at`      DATETIME            NULL,
    `filed_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 `status` (`status`),
    KEY `period_from` (`period_from`),
    KEY `period_to` (`period_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

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

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

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

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

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

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

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

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

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

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 'cash_accounts.%'
      OR p.slug LIKE 'bank_transfers.%'
      OR p.slug LIKE 'cheques.%'
      OR p.slug LIKE 'asset_categories.%'
      OR p.slug LIKE 'fixed_assets.%'
      OR p.slug LIKE 'depreciation.%'
      OR p.slug LIKE 'asset_disposals.%'
      OR p.slug LIKE 'tax_rates.%'
      OR p.slug LIKE 'vat_returns.%'
      OR p.slug LIKE 'tax_reports.%'
  )
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
