-- ---------------------------------------------------------------------------
-- Accounting Phase 3 — Operations
-- Cost Centers, Budgets, Expenses, Payment Vouchers, Receipt Vouchers
-- Run after accounting.sql and accounting_phase2.sql
-- ---------------------------------------------------------------------------

-- ---------------------------------------------------------------------------
-- Cost Centers
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `cost_centers` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `code`              VARCHAR(30)         NOT NULL,
    `name`              VARCHAR(150)        NOT NULL,
    `parent_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`),
    KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Budgets
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `budgets` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `name`              VARCHAR(150)        NOT NULL,
    `fiscal_year`       SMALLINT UNSIGNED   NOT NULL,
    `period_from`       DATE                NOT NULL,
    `period_to`         DATE                NOT NULL,
    `account_id`        BIGINT UNSIGNED     NOT NULL,
    `cost_center_id`    BIGINT UNSIGNED     NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `status`            ENUM('draft','active','closed') NOT NULL DEFAULT 'draft',
    `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 `fiscal_year` (`fiscal_year`),
    KEY `account_id` (`account_id`),
    KEY `cost_center_id` (`cost_center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Expenses
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `expenses` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `expense_date`      DATE                NOT NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `expense_account_id` BIGINT UNSIGNED    NOT NULL,
    `pay_from_type`     ENUM('bank','cash') NOT NULL DEFAULT 'bank',
    `pay_from_id`       BIGINT UNSIGNED     NOT NULL,
    `cost_center_id`    BIGINT UNSIGNED     NULL,
    `payee`             VARCHAR(150)        NULL,
    `reference`         VARCHAR(100)        NULL,
    `notes`             TEXT                NULL,
    `status`            ENUM('draft','approved','cancelled') NOT NULL DEFAULT 'draft',
    `journal_entry_id`  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 `status` (`status`),
    KEY `expense_date` (`expense_date`),
    KEY `expense_account_id` (`expense_account_id`),
    KEY `cost_center_id` (`cost_center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Payment Vouchers (cash/bank out)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `payment_vouchers` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `voucher_date`      DATE                NOT NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `account_id`        BIGINT UNSIGNED     NOT NULL,
    `pay_from_type`     ENUM('bank','cash') NOT NULL DEFAULT 'bank',
    `pay_from_id`       BIGINT UNSIGNED     NOT NULL,
    `cost_center_id`    BIGINT UNSIGNED     NULL,
    `payee`             VARCHAR(150)        NULL,
    `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 `voucher_date` (`voucher_date`),
    KEY `account_id` (`account_id`),
    KEY `cost_center_id` (`cost_center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Receipt Vouchers (cash/bank in)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `receipt_vouchers` (
    `id`                BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `number`            VARCHAR(40)         NOT NULL,
    `voucher_date`      DATE                NOT NULL,
    `amount`            DECIMAL(15,2)       NOT NULL DEFAULT 0.00,
    `account_id`        BIGINT UNSIGNED     NOT NULL,
    `receive_to_type`   ENUM('bank','cash') NOT NULL DEFAULT 'bank',
    `receive_to_id`     BIGINT UNSIGNED     NOT NULL,
    `cost_center_id`    BIGINT UNSIGNED     NULL,
    `payer`             VARCHAR(150)        NULL,
    `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 `voucher_date` (`voucher_date`),
    KEY `account_id` (`account_id`),
    KEY `cost_center_id` (`cost_center_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
