-- ============================================
-- BREAK INN POS - FULL SCHEMA
-- ============================================

-- ORG / ACCESS
CREATE TABLE branches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(30),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT INTO roles (id, name) VALUES (1, 'Cashier'), (2, 'Manager'), (3, 'CEO/Admin');

-- CATALOG / INVENTORY
CREATE TABLE units (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

INSERT INTO units (name) VALUES ('Per Piece'), ('Per Kg'), ('Per Liter'), ('Per Box'), ('Per Pack');

CREATE TABLE categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    barcode VARCHAR(100),
    name VARCHAR(150) NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    unit_id BIGINT UNSIGNED NULL,
    cost_usd DECIMAL(18,2) DEFAULT 0,
    price_usd DECIMAL(18,2) DEFAULT 0,
    image_path VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_products_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE branch_stock (
    branch_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
    low_stock_threshold DECIMAL(18,3) NOT NULL DEFAULT 0,
    PRIMARY KEY (branch_id, product_id),
    CONSTRAINT fk_stock_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
    CONSTRAINT fk_stock_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE stock_adjustments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_change DECIMAL(18,3) NOT NULL,
    reason VARCHAR(255),
    employee_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_adj_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_adj_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_adj_employee FOREIGN KEY (employee_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE stock_transfers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    from_branch_id BIGINT UNSIGNED NOT NULL,
    to_branch_id BIGINT UNSIGNED NOT NULL,
    qty DECIMAL(18,3) NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_transfer_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_transfer_from FOREIGN KEY (from_branch_id) REFERENCES branches(id),
    CONSTRAINT fk_transfer_to FOREIGN KEY (to_branch_id) REFERENCES branches(id),
    CONSTRAINT fk_transfer_employee FOREIGN KEY (employee_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- CONTACTS
CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30),
    address VARCHAR(255),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE suppliers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30),
    address VARCHAR(255),
    website VARCHAR(255),
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

-- SHIFTS
CREATE TABLE shifts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    opening_ll DECIMAL(18,2) DEFAULT 0,
    opening_usd DECIMAL(18,2) DEFAULT 0,
    closing_ll DECIMAL(18,2) NULL,
    closing_usd DECIMAL(18,2) NULL,
    expected_ll DECIMAL(18,2) NULL,
    expected_usd DECIMAL(18,2) NULL,
    variance_ll DECIMAL(18,2) NULL,
    variance_usd DECIMAL(18,2) NULL,
    was_bypassed TINYINT(1) NOT NULL DEFAULT 0,
    opened_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    CONSTRAINT fk_shift_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_shift_employee FOREIGN KEY (employee_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- POS / DRAFTS
CREATE TABLE drafts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_draft_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_draft_employee FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT fk_draft_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE draft_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    draft_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    custom_name VARCHAR(150) NULL,
    qty DECIMAL(18,3) NOT NULL DEFAULT 1,
    unit_price_usd DECIMAL(18,2) NOT NULL DEFAULT 0,
    discount_type VARCHAR(10) NULL,
    discount_value DECIMAL(18,2) NULL DEFAULT 0,
    CONSTRAINT fk_ditem_draft FOREIGN KEY (draft_id) REFERENCES drafts(id) ON DELETE CASCADE,
    CONSTRAINT fk_ditem_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- SALES
CREATE TABLE sales_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    branch_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    shift_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NULL,
    is_delivery TINYINT(1) NOT NULL DEFAULT 0,
    subtotal_ll DECIMAL(18,2) DEFAULT 0,
    subtotal_usd DECIMAL(18,2) DEFAULT 0,
    item_discount_ll DECIMAL(18,2) DEFAULT 0,
    item_discount_usd DECIMAL(18,2) DEFAULT 0,
    invoice_discount_ll DECIMAL(18,2) DEFAULT 0,
    invoice_discount_usd DECIMAL(18,2) DEFAULT 0,
    tax_total_ll DECIMAL(18,2) DEFAULT 0,
    tax_total_usd DECIMAL(18,2) DEFAULT 0,
    grand_total_ll DECIMAL(18,2) DEFAULT 0,
    grand_total_usd DECIMAL(18,2) DEFAULT 0,
    payment_status VARCHAR(10) NOT NULL DEFAULT 'debit',
    status VARCHAR(20) NOT NULL DEFAULT 'completed',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_sinv_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_sinv_employee FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT fk_sinv_shift FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE SET NULL,
    CONSTRAINT fk_sinv_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE sales_invoice_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    custom_name VARCHAR(150) NULL,
    qty DECIMAL(18,3) NOT NULL DEFAULT 1,
    unit_price_ll DECIMAL(18,2) DEFAULT 0,
    unit_price_usd DECIMAL(18,2) DEFAULT 0,
    discount_type VARCHAR(10) NULL,
    discount_ll DECIMAL(18,2) DEFAULT 0,
    discount_usd DECIMAL(18,2) DEFAULT 0,
    line_total_ll DECIMAL(18,2) DEFAULT 0,
    line_total_usd DECIMAL(18,2) DEFAULT 0,
    CONSTRAINT fk_sitem_invoice FOREIGN KEY (invoice_id) REFERENCES sales_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_sitem_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- ACCOUNTS (GLOBAL)
CREATE TABLE accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'cash',
    balance_ll DECIMAL(18,2) DEFAULT 0,
    balance_usd DECIMAL(18,2) DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB;

CREATE TABLE account_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(20) NOT NULL,
    amount_ll DECIMAL(18,2) DEFAULT 0,
    amount_usd DECIMAL(18,2) DEFAULT 0,
    description VARCHAR(255),
    source_type VARCHAR(50) NULL,
    source_id BIGINT UNSIGNED NULL,
    balance_after_ll DECIMAL(18,2) DEFAULT 0,
    balance_after_usd DECIMAL(18,2) DEFAULT 0,
    employee_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_atx_account FOREIGN KEY (account_id) REFERENCES accounts(id),
    CONSTRAINT fk_atx_employee FOREIGN KEY (employee_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE sales_payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    amount_ll DECIMAL(18,2) DEFAULT 0,
    amount_usd DECIMAL(18,2) DEFAULT 0,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_spay_invoice FOREIGN KEY (invoice_id) REFERENCES sales_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_spay_account FOREIGN KEY (account_id) REFERENCES accounts(id)
) ENGINE=InnoDB;

-- LEDGERS
CREATE TABLE customer_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    invoice_id BIGINT UNSIGNED NULL,
    type VARCHAR(10) NOT NULL,
    amount_ll DECIMAL(18,2) DEFAULT 0,
    amount_usd DECIMAL(18,2) DEFAULT 0,
    balance_after_ll DECIMAL(18,2) DEFAULT 0,
    balance_after_usd DECIMAL(18,2) DEFAULT 0,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_cledger_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    CONSTRAINT fk_cledger_invoice FOREIGN KEY (invoice_id) REFERENCES sales_invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- PURCHASING
CREATE TABLE purchase_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50),
    branch_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    total_ll DECIMAL(18,2) DEFAULT 0,
    total_usd DECIMAL(18,2) DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    priority VARCHAR(10) NULL DEFAULT 'low',
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT fk_pinv_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_pinv_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    CONSTRAINT fk_pinv_employee FOREIGN KEY (employee_id) REFERENCES users(id),
    CONSTRAINT fk_pinv_approver FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE purchase_invoice_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_invoice_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty DECIMAL(18,3) NOT NULL,
    unit_cost_ll DECIMAL(18,2) DEFAULT 0,
    unit_cost_usd DECIMAL(18,2) DEFAULT 0,
    CONSTRAINT fk_pitem_invoice FOREIGN KEY (purchase_invoice_id) REFERENCES purchase_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_pitem_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE supplier_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_id BIGINT UNSIGNED NOT NULL,
    purchase_invoice_id BIGINT UNSIGNED NULL,
    type VARCHAR(10) NOT NULL,
    amount_ll DECIMAL(18,2) DEFAULT 0,
    amount_usd DECIMAL(18,2) DEFAULT 0,
    balance_after_ll DECIMAL(18,2) DEFAULT 0,
    balance_after_usd DECIMAL(18,2) DEFAULT 0,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_sledger_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    CONSTRAINT fk_sledger_invoice FOREIGN KEY (purchase_invoice_id) REFERENCES purchase_invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- EXPENSES
CREATE TABLE expenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    category VARCHAR(100),
    description VARCHAR(255),
    amount_ll DECIMAL(18,2) DEFAULT 0,
    amount_usd DECIMAL(18,2) DEFAULT 0,
    account_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_exp_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_exp_account FOREIGN KEY (account_id) REFERENCES accounts(id),
    CONSTRAINT fk_exp_employee FOREIGN KEY (employee_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- SYSTEM
CREATE TABLE activity_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NULL,
    branch_id BIGINT UNSIGNED NULL,
    endpoint VARCHAR(150),
    method VARCHAR(10),
    payload TEXT NULL,
    ip_address VARCHAR(45),
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_log_employee FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_log_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE backups (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(10) NOT NULL,
    file_path VARCHAR(255),
    employee_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL,
    CONSTRAINT fk_backup_employee FOREIGN KEY (employee_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;