-- DualCore ERP - Database Schema
-- Database: acc_manu_db (InnoDB, utf8mb4, ACID-compliant)

CREATE DATABASE IF NOT EXISTS acc_manu_db
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE acc_manu_db;

-- ---------------------------------------------------------------
-- Chart of Accounts
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS accounts (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_code  VARCHAR(20)  NOT NULL UNIQUE,
    account_name  VARCHAR(150) NOT NULL,
    account_type  ENUM('Asset','Liability','Income','Expense','Equity') NOT NULL,
    parent_id     INT UNSIGNED NULL,
    is_active     TINYINT(1) NOT NULL DEFAULT 1,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_accounts_parent FOREIGN KEY (parent_id) REFERENCES accounts(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Inventory Items (Raw Materials / WIP / Finished Goods)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS items (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_code      VARCHAR(30)  NOT NULL UNIQUE,
    item_name      VARCHAR(150) NOT NULL,
    item_type      ENUM('Raw Material','WIP','Finished Good') NOT NULL,
    unit           VARCHAR(20)  NOT NULL DEFAULT 'PCS',
    cost_price     DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    selling_price  DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    stock_qty      DECIMAL(15,3) NOT NULL DEFAULT 0.000,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Voucher master (Receipt / Payment / Journal / Sales / Purchase / Production)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS vouchers (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voucher_no    VARCHAR(30)  NOT NULL UNIQUE,
    voucher_type  ENUM('Receipt','Payment','Journal','Sales','Purchase','Production') NOT NULL,
    voucher_date  DATE NOT NULL,
    narration     VARCHAR(500) NULL,
    total_amount  DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Voucher line items (double-entry rows)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS voucher_entries (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    voucher_id  INT UNSIGNED NOT NULL,
    account_id  INT UNSIGNED NOT NULL,
    debit       DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    credit      DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    remarks     VARCHAR(255) NULL,
    CONSTRAINT fk_ve_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE CASCADE,
    CONSTRAINT fk_ve_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE RESTRICT,
    CONSTRAINT chk_ve_not_both CHECK (NOT (debit > 0 AND credit > 0))
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Bill of Materials: Finished Good -> Raw Materials
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS boms (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    finished_item_id  INT UNSIGNED NOT NULL,
    raw_item_id       INT UNSIGNED NOT NULL,
    qty_required      DECIMAL(15,3) NOT NULL,
    CONSTRAINT fk_bom_finished FOREIGN KEY (finished_item_id) REFERENCES items(id) ON DELETE CASCADE,
    CONSTRAINT fk_bom_raw FOREIGN KEY (raw_item_id) REFERENCES items(id) ON DELETE RESTRICT,
    UNIQUE KEY uq_bom_pair (finished_item_id, raw_item_id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------
-- Production Orders
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS production_orders (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no          VARCHAR(30) NOT NULL UNIQUE,
    finished_item_id  INT UNSIGNED NOT NULL,
    target_qty        DECIMAL(15,3) NOT NULL,
    actual_qty        DECIMAL(15,3) NULL,
    status            ENUM('Pending','In Progress','Completed','Cancelled') NOT NULL DEFAULT 'Pending',
    order_date        DATE NOT NULL,
    completed_at      TIMESTAMP NULL,
    voucher_id        INT UNSIGNED NULL,
    CONSTRAINT fk_po_item FOREIGN KEY (finished_item_id) REFERENCES items(id) ON DELETE RESTRICT,
    CONSTRAINT fk_po_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Seed a minimal chart of accounts to get started
INSERT INTO accounts (account_code, account_name, account_type) VALUES
('1000', 'Raw Materials Inventory', 'Asset'),
('1010', 'Finished Goods Inventory', 'Asset'),
('1020', 'Cash', 'Asset'),
('1030', 'Bank', 'Asset'),
('2000', 'Accounts Payable', 'Liability'),
('3000', 'Owner Equity', 'Equity'),
('4000', 'Sales Revenue', 'Income'),
('5000', 'Cost of Goods Sold', 'Expense'),
('5010', 'Manufacturing Overhead', 'Expense')
ON DUPLICATE KEY UPDATE account_name = VALUES(account_name);