-- ============================================================
-- POS MACS360 — FULL POSTGRESQL SCHEMA
-- ============================================================



-- ============================================================
-- 1. COMPANY PROFILE (single row)
-- ============================================================
CREATE TABLE IF NOT EXISTS company_profile (
    id              SERIAL          PRIMARY KEY,
    name            VARCHAR(200)    NOT NULL,
    tax_id          VARCHAR(50),
    currency_code   CHAR(3)         NOT NULL DEFAULT 'USD',
    timezone        VARCHAR(60)     NOT NULL DEFAULT 'UTC',
    address         TEXT,
    phone           VARCHAR(30),
    email           VARCHAR(150),
    logo_url        TEXT,
    -- legacy compat
    locale          VARCHAR(20)     DEFAULT 'en-US',
    industry_vertical VARCHAR(30)   DEFAULT 'retail',
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 2. ORGANIZATION
-- ============================================================
CREATE TABLE IF NOT EXISTS branches (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(150)    NOT NULL,
    code            VARCHAR(20)     NOT NULL UNIQUE,
    address         TEXT,
    city            VARCHAR(100),
    state           VARCHAR(100),
    postal_code     VARCHAR(20),
    country_code    CHAR(2),
    phone           VARCHAR(30),
    email           VARCHAR(150),
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS terminals (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    name            VARCHAR(80)     NOT NULL,
    terminal_code   VARCHAR(30)     NOT NULL,
    ip_address      VARCHAR(45),
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    last_ping_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    UNIQUE (branch_id, terminal_code)
);

-- ============================================================
-- 3. STAFF & ACCESS
-- ============================================================
CREATE TABLE IF NOT EXISTS staff (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER REFERENCES branches(id),
    name            VARCHAR(150)    NOT NULL,
    email           VARCHAR(150)    UNIQUE,
    phone           VARCHAR(30),
    pin_hash        TEXT,
    password_hash   TEXT,
    role            VARCHAR(40)     NOT NULL DEFAULT 'cashier',
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS pos_sessions (
    id              SERIAL PRIMARY KEY,
    terminal_id     INTEGER NOT NULL REFERENCES terminals(id),
    cashier_id      INTEGER NOT NULL REFERENCES staff(id),
    opened_at       TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    closed_at       TIMESTAMPTZ,
    opening_cash    DECIMAL   NOT NULL DEFAULT 0,
    closing_cash    DECIMAL,
    expected_cash   DECIMAL,
    cash_variance   DECIMAL,
    status          VARCHAR(20)     NOT NULL DEFAULT 'open',
    notes           TEXT
);

-- ============================================================
-- 4. MEDIA / IMAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS media (
    id              SERIAL PRIMARY KEY,
    file_name       VARCHAR(255)    NOT NULL,
    file_url        TEXT            NOT NULL,
    thumbnail_url   TEXT,
    mime_type       VARCHAR(80),
    file_size_kb    INT,
    width_px        INT,
    height_px       INT,
    alt_text        VARCHAR(255),
    uploaded_by     INTEGER REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 5. UNITS OF MEASURE
-- ============================================================
CREATE TABLE IF NOT EXISTS units (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(80)     NOT NULL,
    symbol          VARCHAR(20)     NOT NULL UNIQUE,
    unit_type       VARCHAR(30)     NOT NULL DEFAULT 'quantity',
    is_base_unit    BOOLEAN         NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS unit_conversions (
    id              SERIAL PRIMARY KEY,
    from_unit_id    INTEGER NOT NULL REFERENCES units(id),
    to_unit_id      INTEGER NOT NULL REFERENCES units(id),
    factor          DECIMAL   NOT NULL,
    UNIQUE (from_unit_id, to_unit_id),
    CHECK (from_unit_id <> to_unit_id)
);

-- ============================================================
-- 6. CATALOG — CATEGORIES & PRODUCTS
-- ============================================================
CREATE TABLE IF NOT EXISTS categories (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(150)    NOT NULL,
    slug            VARCHAR(150),
    description     TEXT,
    parent_id       INTEGER REFERENCES categories(id),
    image_id        INTEGER REFERENCES media(id),
    sort_order      INT             NOT NULL DEFAULT 0,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    -- legacy compat
    color           VARCHAR(200),
    icon            VARCHAR(100),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS taxes (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(80)     NOT NULL,
    rate            DECIMAL    NOT NULL DEFAULT 0,
    tax_type        VARCHAR(20)     NOT NULL DEFAULT 'percentage',
    is_inclusive    BOOLEAN         NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS products (
    id              SERIAL PRIMARY KEY,
    category_id     INTEGER REFERENCES categories(id),
    name            VARCHAR(200)    NOT NULL,
    sku             VARCHAR(80)     UNIQUE,
    barcode         VARCHAR(80),
    description     TEXT,
    product_type    VARCHAR(30)     NOT NULL DEFAULT 'standard',
    cost_price      DECIMAL   NOT NULL DEFAULT 0,
    selling_price   DECIMAL   NOT NULL DEFAULT 0,
    tax_id          INTEGER REFERENCES taxes(id),
    unit_id         INTEGER REFERENCES units(id),
    purchase_unit_id INTEGER REFERENCES units(id),
    purchase_unit_qty DECIMAL NOT NULL DEFAULT 1,
    track_stock     BOOLEAN         NOT NULL DEFAULT TRUE,
    allow_negative_stock BOOLEAN    NOT NULL DEFAULT FALSE,
    min_stock_alert DECIMAL   DEFAULT 0,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    -- legacy compat columns
    sale_price      DECIMAL,
    stock_quantity  DECIMAL   DEFAULT 100,
    minimum_stock   DECIMAL   DEFAULT 5,
    is_weighed      BOOLEAN         DEFAULT FALSE,
    is_restricted   BOOLEAN         DEFAULT FALSE,
    image           TEXT,
    modifiers       JSONB,
    ingredients     JSONB,
    brand_name      VARCHAR(150),
    plu_code        VARCHAR(50),
    weight_unit     VARCHAR(20)     DEFAULT 'unit',
    variants        JSONB,
    tax_rate_pct    DECIMAL    DEFAULT 0,
    unit_type       VARCHAR(20)     DEFAULT 'unit',
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS product_images (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    media_id        INTEGER NOT NULL REFERENCES media(id),
    is_primary      BOOLEAN         NOT NULL DEFAULT FALSE,
    sort_order      INT             NOT NULL DEFAULT 0
);

-- ============================================================
-- 7. PRODUCT VARIANTS
-- ============================================================
CREATE TABLE IF NOT EXISTS variant_option_types (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(80)     NOT NULL UNIQUE,
    display_order   INT             NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS variant_option_values (
    id              SERIAL PRIMARY KEY,
    option_type_id  INTEGER NOT NULL REFERENCES variant_option_types(id) ON DELETE CASCADE,
    value           VARCHAR(100)    NOT NULL,
    display_order   INT             NOT NULL DEFAULT 0,
    UNIQUE (option_type_id, value)
);

CREATE TABLE IF NOT EXISTS product_option_types (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    option_type_id  INTEGER NOT NULL REFERENCES variant_option_types(id),
    display_order   INT             NOT NULL DEFAULT 0,
    UNIQUE (product_id, option_type_id)
);

CREATE TABLE IF NOT EXISTS product_variants (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    name            VARCHAR(150),
    sku             VARCHAR(80)     UNIQUE,
    barcode         VARCHAR(80),
    cost_price      DECIMAL,
    selling_price   DECIMAL,
    image_id        INTEGER REFERENCES media(id),
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS product_variant_options (
    id              SERIAL PRIMARY KEY,
    variant_id      INTEGER NOT NULL REFERENCES product_variants(id) ON DELETE CASCADE,
    option_type_id  INTEGER NOT NULL REFERENCES variant_option_types(id),
    option_value_id INTEGER NOT NULL REFERENCES variant_option_values(id),
    UNIQUE (variant_id, option_type_id)
);

-- ============================================================
-- 8. COMBO / BUNDLE PRODUCTS
-- ============================================================
CREATE TABLE IF NOT EXISTS combo_items (
    id                  SERIAL PRIMARY KEY,
    combo_product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    component_product_id INTEGER NOT NULL REFERENCES products(id),
    component_variant_id INTEGER REFERENCES product_variants(id),
    qty                 DECIMAL NOT NULL DEFAULT 1,
    unit_id             INTEGER REFERENCES units(id),
    is_optional         BOOLEAN     NOT NULL DEFAULT FALSE,
    UNIQUE (combo_product_id, component_product_id, component_variant_id)
);

-- ============================================================
-- 14. SUPPLIERS (defined before product_suppliers)
-- ============================================================
CREATE TABLE IF NOT EXISTS suppliers (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(200)    NOT NULL,
    code            VARCHAR(30),
    contact_name    VARCHAR(150),
    email           VARCHAR(150),
    phone           VARCHAR(30),
    address         TEXT,
    tax_id          VARCHAR(50),
    payment_terms   INT             DEFAULT 30,
    currency_code   CHAR(3)         DEFAULT 'USD',
    notes           TEXT,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    -- legacy compat
    contact         VARCHAR(150),
    credit_limit    DECIMAL   DEFAULT 0,
    balance         DECIMAL   DEFAULT 0,
    rating          INT             DEFAULT 5,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 9. PRODUCT ↔ SUPPLIER CATALOGUE
-- ============================================================
CREATE TABLE IF NOT EXISTS product_suppliers (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    variant_id      INTEGER REFERENCES product_variants(id),
    supplier_id     INTEGER NOT NULL REFERENCES suppliers(id),
    supplier_sku    VARCHAR(80),
    unit_cost       DECIMAL   NOT NULL DEFAULT 0,
    min_order_qty   DECIMAL   NOT NULL DEFAULT 1,
    lead_time_days  INT             DEFAULT 0,
    is_preferred    BOOLEAN         NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    UNIQUE (product_id, variant_id, supplier_id)
);

-- ============================================================
-- 10. PRICING & DISCOUNTS
-- ============================================================
CREATE TABLE IF NOT EXISTS price_lists (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    currency_code   CHAR(3)         NOT NULL DEFAULT 'USD',
    is_default      BOOLEAN         NOT NULL DEFAULT FALSE,
    valid_from      DATE,
    valid_to        DATE,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS product_prices (
    id              SERIAL PRIMARY KEY,
    price_list_id   INTEGER NOT NULL REFERENCES price_lists(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    unit_price      DECIMAL   NOT NULL,
    min_qty         DECIMAL   NOT NULL DEFAULT 1,
    UNIQUE (price_list_id, product_id, variant_id, min_qty)
);

CREATE TABLE IF NOT EXISTS discounts (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(120)    NOT NULL,
    code            VARCHAR(50),
    discount_type   VARCHAR(20)     NOT NULL DEFAULT 'percentage',
    value           DECIMAL   NOT NULL DEFAULT 0,
    min_order_amount DECIMAL  DEFAULT 0,
    max_discount_cap DECIMAL,
    applies_to      VARCHAR(20)     NOT NULL DEFAULT 'order',
    target_id       INTEGER,
    valid_from      DATE,
    valid_to        DATE,
    max_uses        INT,
    used_count      INT             NOT NULL DEFAULT 0,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 11. LOYALTY RULES
-- ============================================================
CREATE TABLE IF NOT EXISTS loyalty_rules (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(120)    NOT NULL,
    rule_type       VARCHAR(30)     NOT NULL,
    points_value    DECIMAL   NOT NULL DEFAULT 0,
    currency_value  DECIMAL   NOT NULL DEFAULT 0,
    applies_to      VARCHAR(20)     NOT NULL DEFAULT 'all',
    target_id       INTEGER,
    valid_from      DATE,
    valid_to        DATE,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 12. INVENTORY
-- ============================================================
CREATE TABLE IF NOT EXISTS stock_levels (
    id              SERIAL PRIMARY KEY,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    qty_on_hand     DECIMAL   NOT NULL DEFAULT 0,
    qty_reserved    DECIMAL   NOT NULL DEFAULT 0,
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    UNIQUE (product_id, branch_id)
);

CREATE TABLE IF NOT EXISTS stock_adjustments (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    reason          VARCHAR(60)     NOT NULL DEFAULT 'manual_adjustment',
    qty_before      DECIMAL   NOT NULL,
    qty_change      DECIMAL   NOT NULL,
    qty_after       DECIMAL   NOT NULL,
    reference_type  VARCHAR(40),
    reference_id    INTEGER,
    notes           TEXT,
    done_by         INTEGER NOT NULL REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS stock_transfers (
    id              SERIAL PRIMARY KEY,
    transfer_number VARCHAR(40)     NOT NULL UNIQUE,
    from_branch_id  INTEGER NOT NULL REFERENCES branches(id),
    to_branch_id    INTEGER NOT NULL REFERENCES branches(id),
    status          VARCHAR(20)     NOT NULL DEFAULT 'draft',
    transfer_date   DATE            NOT NULL DEFAULT CURRENT_DATE,
    expected_date   DATE,
    notes           TEXT,
    created_by      INTEGER NOT NULL REFERENCES staff(id),
    received_by     INTEGER REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    received_at     TIMESTAMPTZ,
    CHECK (from_branch_id <> to_branch_id)
);

CREATE TABLE IF NOT EXISTS stock_transfer_items (
    id              SERIAL PRIMARY KEY,
    transfer_id     INTEGER NOT NULL REFERENCES stock_transfers(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    qty_requested   DECIMAL   NOT NULL,
    qty_sent        DECIMAL   NOT NULL DEFAULT 0,
    qty_received    DECIMAL   NOT NULL DEFAULT 0,
    unit_cost       DECIMAL,
    notes           TEXT
);

-- ============================================================
-- 13. CUSTOMERS
-- ============================================================
CREATE TABLE IF NOT EXISTS customers (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(200)    NOT NULL,
    email           VARCHAR(150),
    phone           VARCHAR(30),
    address         TEXT,
    city            VARCHAR(100),
    state           VARCHAR(100),
    country_code    CHAR(2),
    tax_id          VARCHAR(50),
    credit_limit    DECIMAL   NOT NULL DEFAULT 0,
    credit_balance  DECIMAL   NOT NULL DEFAULT 0,
    loyalty_points  INT             NOT NULL DEFAULT 0,
    price_list_id   INTEGER REFERENCES price_lists(id),
    notes           TEXT,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    -- legacy compat
    store_credit    DECIMAL   DEFAULT 0,
    balance_due     DECIMAL   DEFAULT 0,
    group_discount_pct DECIMAL DEFAULT 0,
    is_tax_exempt   BOOLEAN         DEFAULT FALSE,
    tax_exempt_number VARCHAR(80),
    customer_group_id VARCHAR(80),
    customer_group_name VARCHAR(100),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS loyalty_transactions (
    id              SERIAL PRIMARY KEY,
    customer_id     INTEGER NOT NULL REFERENCES customers(id),
    points          INT             NOT NULL,
    transaction_type VARCHAR(20)    NOT NULL,
    reference_id    INTEGER,
    notes           TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 15. PURCHASING
-- ============================================================
CREATE TABLE IF NOT EXISTS purchase_orders (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER REFERENCES branches(id),
    supplier_id     INTEGER REFERENCES suppliers(id),
    po_number       VARCHAR(40)     NOT NULL UNIQUE,
    po_date         DATE            NOT NULL DEFAULT CURRENT_DATE,
    expected_date   DATE,
    status          VARCHAR(20)     NOT NULL DEFAULT 'draft',
    subtotal        DECIMAL   NOT NULL DEFAULT 0,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    discount_amount DECIMAL   NOT NULL DEFAULT 0,
    shipping_cost   DECIMAL   NOT NULL DEFAULT 0,
    total_amount    DECIMAL   NOT NULL DEFAULT 0,
    paid_amount     DECIMAL   NOT NULL DEFAULT 0,
    notes           TEXT,
    created_by      INTEGER REFERENCES staff(id),
    -- legacy compat
    order_number    VARCHAR(40),
    supplier_name   VARCHAR(200),
    items           JSONB,
    received_at     TIMESTAMPTZ,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS purchase_order_items (
    id              SERIAL PRIMARY KEY,
    po_id           INTEGER NOT NULL REFERENCES purchase_orders(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    ordered_qty     DECIMAL   NOT NULL,
    received_qty    DECIMAL   NOT NULL DEFAULT 0,
    unit_cost       DECIMAL   NOT NULL,
    unit_id         INTEGER REFERENCES units(id),
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    line_total      DECIMAL   NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS goods_receipts (
    id              SERIAL PRIMARY KEY,
    po_id           INTEGER REFERENCES purchase_orders(id),
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    grn_number      VARCHAR(40)     NOT NULL,
    received_date   DATE            NOT NULL DEFAULT CURRENT_DATE,
    supplier_invoice_no VARCHAR(80),
    status          VARCHAR(20)     NOT NULL DEFAULT 'pending',
    notes           TEXT,
    received_by     INTEGER NOT NULL REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS goods_receipt_items (
    id              SERIAL PRIMARY KEY,
    grn_id          INTEGER NOT NULL REFERENCES goods_receipts(id) ON DELETE CASCADE,
    po_item_id      INTEGER REFERENCES purchase_order_items(id),
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    received_qty    DECIMAL   NOT NULL,
    unit_cost       DECIMAL   NOT NULL,
    batch_number    VARCHAR(80),
    expiry_date     DATE,
    line_total      DECIMAL   NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS supplier_invoices (
    id              SERIAL PRIMARY KEY,
    supplier_id     INTEGER NOT NULL REFERENCES suppliers(id),
    po_id           INTEGER REFERENCES purchase_orders(id),
    invoice_number  VARCHAR(80)     NOT NULL,
    invoice_date    DATE            NOT NULL,
    due_date        DATE,
    subtotal        DECIMAL   NOT NULL DEFAULT 0,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    total_amount    DECIMAL   NOT NULL DEFAULT 0,
    paid_amount     DECIMAL   NOT NULL DEFAULT 0,
    status          VARCHAR(20)     NOT NULL DEFAULT 'unpaid',
    notes           TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 16. PAYMENT METHODS
-- ============================================================
CREATE TABLE IF NOT EXISTS payment_methods (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(80)     NOT NULL,
    type            VARCHAR(30)     NOT NULL,
    icon_url        TEXT,
    processing_fee_pct   DECIMAL  DEFAULT 0,
    processing_fee_fixed DECIMAL DEFAULT 0,
    is_change_allowed    BOOLEAN       NOT NULL DEFAULT FALSE,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    sort_order      INT             NOT NULL DEFAULT 0
);

-- ============================================================
-- 17. HELD ORDERS
-- ============================================================
CREATE TABLE IF NOT EXISTS held_orders (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    terminal_id     INTEGER REFERENCES terminals(id),
    session_id      INTEGER REFERENCES pos_sessions(id),
    customer_id     INTEGER REFERENCES customers(id),
    cashier_id      INTEGER NOT NULL REFERENCES staff(id),
    label           VARCHAR(100),
    notes           TEXT,
    held_at         TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    recalled_at     TIMESTAMPTZ,
    status          VARCHAR(20)     NOT NULL DEFAULT 'held',
    expires_at      TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS held_order_items (
    id              SERIAL PRIMARY KEY,
    held_order_id   INTEGER NOT NULL REFERENCES held_orders(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    qty             DECIMAL   NOT NULL,
    unit_price      DECIMAL   NOT NULL,
    discount_pct    DECIMAL    NOT NULL DEFAULT 0,
    discount_amount DECIMAL   NOT NULL DEFAULT 0,
    notes           TEXT
);

-- ============================================================
-- 18. SALES ORDERS
-- ============================================================
CREATE TABLE IF NOT EXISTS sales_orders (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    terminal_id     INTEGER REFERENCES terminals(id),
    session_id      INTEGER REFERENCES pos_sessions(id),
    held_order_id   INTEGER REFERENCES held_orders(id),
    order_number    VARCHAR(40)     NOT NULL UNIQUE,
    order_date      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    customer_id     INTEGER REFERENCES customers(id),
    cashier_id      INTEGER NOT NULL REFERENCES staff(id),
    price_list_id   INTEGER REFERENCES price_lists(id),
    discount_id     INTEGER REFERENCES discounts(id),
    subtotal        DECIMAL   NOT NULL DEFAULT 0,
    discount_amount DECIMAL   NOT NULL DEFAULT 0,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    rounding_amount DECIMAL   NOT NULL DEFAULT 0,
    total_amount    DECIMAL   NOT NULL DEFAULT 0,
    paid_amount     DECIMAL   NOT NULL DEFAULT 0,
    change_amount   DECIMAL   NOT NULL DEFAULT 0,
    credit_used     DECIMAL   NOT NULL DEFAULT 0,
    loyalty_redeemed INT            NOT NULL DEFAULT 0,
    loyalty_earned  INT             NOT NULL DEFAULT 0,
    status          VARCHAR(20)     NOT NULL DEFAULT 'completed',
    channel         VARCHAR(20)     NOT NULL DEFAULT 'pos',
    notes           TEXT,
    -- legacy compat
    receipt_number  VARCHAR(60),
    total           DECIMAL,
    change_given    DECIMAL,
    payment_method  VARCHAR(30),
    customer_name   VARCHAR(200),
    staff_id        INTEGER REFERENCES staff(id),
    staff_name      VARCHAR(150),
    items           JSONB,
    vertical        VARCHAR(20),
    void_reason     TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS order_items (
    id              SERIAL PRIMARY KEY,
    order_id        INTEGER NOT NULL REFERENCES sales_orders(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    qty             DECIMAL   NOT NULL,
    unit_price      DECIMAL   NOT NULL,
    discount_pct    DECIMAL    NOT NULL DEFAULT 0,
    discount_amount DECIMAL   NOT NULL DEFAULT 0,
    tax_rate        DECIMAL    NOT NULL DEFAULT 0,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    line_total      DECIMAL   NOT NULL DEFAULT 0,
    cost_price      DECIMAL,
    notes           TEXT
);

-- ============================================================
-- 19. ORDER PAYMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS order_payments (
    id              SERIAL PRIMARY KEY,
    order_id        INTEGER NOT NULL REFERENCES sales_orders(id) ON DELETE CASCADE,
    payment_method_id INTEGER NOT NULL REFERENCES payment_methods(id),
    amount          DECIMAL   NOT NULL,
    tendered        DECIMAL,
    change_given    DECIMAL   NOT NULL DEFAULT 0,
    reference_no    VARCHAR(100),
    status          VARCHAR(20)     NOT NULL DEFAULT 'approved',
    processed_at    TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 20. CREDIT SALES & CUSTOMER CREDIT LEDGER
-- ============================================================
CREATE TABLE IF NOT EXISTS customer_credit_ledger (
    id              SERIAL PRIMARY KEY,
    customer_id     INTEGER NOT NULL REFERENCES customers(id),
    entry_type      VARCHAR(30)     NOT NULL,
    amount          DECIMAL   NOT NULL,
    direction       CHAR(2)         NOT NULL,
    reference_type  VARCHAR(30),
    reference_id    INTEGER,
    balance_after   DECIMAL   NOT NULL,
    notes           TEXT,
    created_by      INTEGER REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS credit_payments (
    id              SERIAL PRIMARY KEY,
    customer_id     INTEGER NOT NULL REFERENCES customers(id),
    payment_method_id INTEGER NOT NULL REFERENCES payment_methods(id),
    amount          DECIMAL   NOT NULL,
    payment_date    DATE            NOT NULL DEFAULT CURRENT_DATE,
    reference_no    VARCHAR(100),
    notes           TEXT,
    received_by     INTEGER NOT NULL REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 21. RETURNS
-- ============================================================
CREATE TABLE IF NOT EXISTS returns (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    return_number   VARCHAR(40)     NOT NULL UNIQUE,
    return_date     TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    original_order_id INTEGER REFERENCES sales_orders(id),
    customer_id     INTEGER REFERENCES customers(id),
    cashier_id      INTEGER NOT NULL REFERENCES staff(id),
    return_type     VARCHAR(20)     NOT NULL DEFAULT 'customer_return',
    reason          TEXT,
    subtotal        DECIMAL   NOT NULL DEFAULT 0,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    total_amount    DECIMAL   NOT NULL DEFAULT 0,
    refund_method   VARCHAR(30)     NOT NULL DEFAULT 'cash',
    restocked       BOOLEAN         NOT NULL DEFAULT TRUE,
    status          VARCHAR(20)     NOT NULL DEFAULT 'completed',
    notes           TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS return_items (
    id              SERIAL PRIMARY KEY,
    return_id       INTEGER NOT NULL REFERENCES returns(id) ON DELETE CASCADE,
    order_item_id   INTEGER REFERENCES order_items(id),
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    qty             DECIMAL   NOT NULL,
    unit_price      DECIMAL   NOT NULL,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    refund_amount   DECIMAL   NOT NULL DEFAULT 0,
    restock         BOOLEAN         NOT NULL DEFAULT TRUE,
    condition       VARCHAR(30)     DEFAULT 'resalable'
);

CREATE TABLE IF NOT EXISTS supplier_returns (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER NOT NULL REFERENCES branches(id),
    supplier_id     INTEGER NOT NULL REFERENCES suppliers(id),
    return_number   VARCHAR(40)     NOT NULL,
    return_date     DATE            NOT NULL DEFAULT CURRENT_DATE,
    reason          TEXT,
    total_amount    DECIMAL   NOT NULL DEFAULT 0,
    status          VARCHAR(20)     NOT NULL DEFAULT 'pending',
    created_by      INTEGER NOT NULL REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS supplier_return_items (
    id              SERIAL PRIMARY KEY,
    supplier_return_id INTEGER NOT NULL REFERENCES supplier_returns(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id),
    variant_id      INTEGER REFERENCES product_variants(id),
    qty             DECIMAL   NOT NULL,
    unit_cost       DECIMAL   NOT NULL,
    line_total      DECIMAL   NOT NULL DEFAULT 0
);

-- ============================================================
-- 22. EXPENSES
-- ============================================================
CREATE TABLE IF NOT EXISTS expense_categories (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100)    NOT NULL,
    parent_id       INTEGER REFERENCES expense_categories(id),
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS expenses (
    id              SERIAL PRIMARY KEY,
    branch_id       INTEGER REFERENCES branches(id),
    expense_category_id INTEGER REFERENCES expense_categories(id),
    title           VARCHAR(200)    NOT NULL,
    description     TEXT,
    amount          DECIMAL   NOT NULL,
    tax_amount      DECIMAL   NOT NULL DEFAULT 0,
    total_amount    DECIMAL   NOT NULL DEFAULT 0,
    expense_date    DATE            NOT NULL DEFAULT CURRENT_DATE,
    payment_method_id INTEGER REFERENCES payment_methods(id),
    paid_from_session_id INTEGER REFERENCES pos_sessions(id),
    receipt_media_id INTEGER REFERENCES media(id),
    reference_no    VARCHAR(100),
    status          VARCHAR(20)     NOT NULL DEFAULT 'approved',
    submitted_by    INTEGER REFERENCES staff(id),
    approved_by     INTEGER REFERENCES staff(id),
    -- legacy compat
    category        VARCHAR(100),
    notes           TEXT,
    payment_method  VARCHAR(60),
    attachment_url  TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 23. CASH DRAWER MOVEMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS cash_movements (
    id              SERIAL PRIMARY KEY,
    session_id      INTEGER NOT NULL REFERENCES pos_sessions(id),
    movement_type   VARCHAR(20)     NOT NULL,
    amount          DECIMAL   NOT NULL,
    reason          TEXT,
    done_by         INTEGER NOT NULL REFERENCES staff(id),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- 24. AUDIT LOG
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_logs (
    id              SERIAL PRIMARY KEY,
    staff_id        INTEGER REFERENCES staff(id),
    branch_id       INTEGER REFERENCES branches(id),
    action          VARCHAR(60)     NOT NULL,
    resource_type   VARCHAR(60)     NOT NULL,
    resource_id     INTEGER,
    old_values      JSONB,
    new_values      JSONB,
    ip_address      VARCHAR(45),
    notes           TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- ============================================================
-- LEGACY COMPAT TABLES (keep for frontend compatibility)
-- ============================================================
CREATE TABLE IF NOT EXISTS settings (
    key             VARCHAR(100)    PRIMARY KEY,
    value           TEXT
);

CREATE TABLE IF NOT EXISTS "tables" (
    id              VARCHAR(80)     PRIMARY KEY,
    label           VARCHAR(150),
    seats           INT,
    status          VARCHAR(30),
    current_order_id VARCHAR(80),
    current_cart    JSONB,
    x               DECIMAL,
    y               DECIMAL
);

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_terminals_branch       ON terminals(branch_id);
CREATE INDEX IF NOT EXISTS idx_sessions_terminal      ON pos_sessions(terminal_id);
CREATE INDEX IF NOT EXISTS idx_sessions_cashier       ON pos_sessions(cashier_id);
CREATE INDEX IF NOT EXISTS idx_products_category      ON products(category_id);
CREATE INDEX IF NOT EXISTS idx_products_sku           ON products(sku);
CREATE INDEX IF NOT EXISTS idx_products_barcode       ON products(barcode);
CREATE INDEX IF NOT EXISTS idx_variants_product       ON product_variants(product_id);
CREATE INDEX IF NOT EXISTS idx_stock_branch           ON stock_levels(branch_id);
CREATE INDEX IF NOT EXISTS idx_stock_product          ON stock_levels(product_id);
CREATE INDEX IF NOT EXISTS idx_customers_phone        ON customers(phone);
CREATE INDEX IF NOT EXISTS idx_customers_email        ON customers(email);
CREATE INDEX IF NOT EXISTS idx_orders_branch          ON sales_orders(branch_id);
CREATE INDEX IF NOT EXISTS idx_orders_customer        ON sales_orders(customer_id);
CREATE INDEX IF NOT EXISTS idx_orders_date            ON sales_orders(order_date);
CREATE INDEX IF NOT EXISTS idx_orders_status          ON sales_orders(status);
CREATE INDEX IF NOT EXISTS idx_order_items_order      ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_product    ON order_items(product_id);
CREATE INDEX IF NOT EXISTS idx_payments_order         ON order_payments(order_id);
CREATE INDEX IF NOT EXISTS idx_returns_branch         ON returns(branch_id);
CREATE INDEX IF NOT EXISTS idx_returns_order          ON returns(original_order_id);
CREATE INDEX IF NOT EXISTS idx_po_supplier            ON purchase_orders(supplier_id);
CREATE INDEX IF NOT EXISTS idx_po_status              ON purchase_orders(status);
CREATE INDEX IF NOT EXISTS idx_expenses_branch        ON expenses(branch_id);
CREATE INDEX IF NOT EXISTS idx_expenses_date          ON expenses(expense_date);
CREATE INDEX IF NOT EXISTS idx_held_orders_status     ON held_orders(status);
CREATE INDEX IF NOT EXISTS idx_credit_customer        ON customer_credit_ledger(customer_id);
CREATE INDEX IF NOT EXISTS idx_audit_resource         ON audit_logs(resource_type, resource_id);