database_scripts/01_schema.sql

-- ============================================================================
-- Database Schema for SCM Microservice
-- Includes: Trans Schema, SCM Tables, and Sales Order Tables
-- ============================================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create Role if not exists
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'trans_owner') THEN
    CREATE ROLE trans_owner WITH LOGIN PASSWORD 'trans_password'; -- Set a default password or leave it nologin
  END IF;
END
$$;

-- Create Schema
CREATE SCHEMA IF NOT EXISTS trans;
ALTER SCHEMA trans OWNER TO trans_owner;

-- ============================================================================
-- Core Reference Tables
-- ============================================================================

-- Catalogue Reference
CREATE TABLE IF NOT EXISTS trans.catalogue_ref (
    catalogue_id TEXT PRIMARY KEY,
    catalogue_code TEXT,
    catalogue_type TEXT NOT NULL,
    catalogue_name TEXT NOT NULL,
    sku TEXT,
    barcode_number TEXT,
    hsn_code TEXT,
    gst_rate NUMERIC(5,2),
    mrp NUMERIC(12,2),
    base_price NUMERIC(12,2),
    track_inventory BOOLEAN DEFAULT FALSE,
    batch_managed BOOLEAN DEFAULT FALSE,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    merchant_id TEXT[],
    pricing_levels JSON
);

CREATE INDEX IF NOT EXISTS idx_catalogue_ref_catalogue_code ON trans.catalogue_ref (catalogue_code);
CREATE INDEX IF NOT EXISTS idx_catalogue_ref_catalogue_type ON trans.catalogue_ref (catalogue_type);
CREATE INDEX IF NOT EXISTS idx_catalogue_ref_sku ON trans.catalogue_ref (sku);
CREATE INDEX IF NOT EXISTS idx_catalogue_ref_status ON trans.catalogue_ref (status);
CREATE INDEX IF NOT EXISTS idx_catalogue_ref_pricing_levels_currency ON trans.catalogue_ref USING GIN ((pricing_levels->>'currency'));

-- UOM Groups
CREATE TABLE IF NOT EXISTS trans.scm_uom_group_ref (
    uom_group_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    uom_group_code VARCHAR(50) UNIQUE,
    name VARCHAR(100) NOT NULL,
    base_unit VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    units JSON NOT NULL DEFAULT '[]',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_uom_group_code ON trans.scm_uom_group_ref (uom_group_code);
CREATE INDEX IF NOT EXISTS idx_uom_group_name ON trans.scm_uom_group_ref (name);

-- Stored Objects (Documents)
CREATE TABLE IF NOT EXISTS trans.stored_objects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id TEXT NOT NULL,
    domain TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    bucket_name TEXT NOT NULL,
    object_key TEXT NOT NULL,
    category TEXT NOT NULL,
    file_name TEXT NOT NULL,
    mime_type TEXT NOT NULL,
    file_size BIGINT,
    checksum_sha256 TEXT,
    visibility VARCHAR(16) NOT NULL DEFAULT 'private',
    created_by TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    legal_hold BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT uq_stored_object_active_key UNIQUE (tenant_id, domain, entity_id, object_key, deleted_at)
);

CREATE INDEX IF NOT EXISTS ix_stored_objects_active ON trans.stored_objects (tenant_id, domain, entity_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS ix_stored_objects_checksum ON trans.stored_objects (tenant_id, checksum_sha256) WHERE deleted_at IS NULL;

-- ============================================================================
-- SCM Purchase Orders
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_po (
    po_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    po_no VARCHAR(50) UNIQUE NOT NULL,
    buyer_id UUID NOT NULL,
    buyer_type VARCHAR(20) NOT NULL,
    supplier_id UUID NOT NULL,
    supplier_type VARCHAR(20) NOT NULL,
    po_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    exp_delivery_dt DATE,
    currency VARCHAR(3) NOT NULL DEFAULT 'INR',
    total_amt NUMERIC(14, 2) NOT NULL,
    tax_amt NUMERIC(14, 2) DEFAULT 0,
    net_amt NUMERIC(14, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    remarks TEXT,
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.scm_po_item (
    po_item_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    po_id UUID NOT NULL REFERENCES trans.scm_po(po_id) ON DELETE CASCADE,
    catalogue_id UUID NOT NULL,
    sku VARCHAR(64) NOT NULL,
    batch_managed BOOLEAN,
    ord_qty NUMERIC(12, 3) NOT NULL,
    ord_uom_qty NUMERIC(12, 3) NOT NULL,
    rcvd_qty NUMERIC(12, 3) DEFAULT 0,
    invoiced_qty NUMERIC(12, 3) DEFAULT 0,
    returned_qty NUMERIC(12, 3) DEFAULT 0,
    dispatched_qty NUMERIC(12, 3) DEFAULT 0,
    rejected_qty NUMERIC(12, 3) DEFAULT 0,
    uom VARCHAR(10) NOT NULL,
    ord_uom VARCHAR(10) NOT NULL,
    unit_price NUMERIC(12, 2) NOT NULL,
    line_amt NUMERIC(14, 2) NOT NULL,
    tax_rate NUMERIC(5, 2) DEFAULT 0,
    tax_amt NUMERIC(12, 2) DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.scm_po_status_log (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    po_id UUID NOT NULL REFERENCES trans.scm_po(po_id),
    status VARCHAR(20) NOT NULL,
    changed_by VARCHAR(64),
    changed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT
);

-- ============================================================================
-- SCM PO Returns
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_po_return (
    po_return_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    po_return_no VARCHAR(40) UNIQUE NOT NULL,
    po_id UUID NOT NULL,
    supplier_id VARCHAR(64) NOT NULL,
    client_id VARCHAR(64) NOT NULL,
    warehouse_id VARCHAR(64),
    return_date TIMESTAMP NOT NULL,
    status VARCHAR(20) NOT NULL,
    reason_code VARCHAR(50),
    remarks TEXT,
    created_by VARCHAR(64),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_po_return_po_id ON trans.scm_po_return(po_id);
CREATE INDEX IF NOT EXISTS idx_po_return_supplier_id ON trans.scm_po_return(supplier_id);
CREATE INDEX IF NOT EXISTS idx_po_return_client_id ON trans.scm_po_return(client_id);

CREATE TABLE IF NOT EXISTS trans.scm_po_return_item (
    po_return_item_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    po_return_id UUID NOT NULL REFERENCES trans.scm_po_return(po_return_id) ON DELETE CASCADE,
    po_item_id UUID NOT NULL,
    catalogue_id UUID NOT NULL,
    batch_no VARCHAR(50) NOT NULL,
    expiry_date TIMESTAMP,
    return_qty NUMERIC(12, 3) NOT NULL,
    uom VARCHAR(20) NOT NULL DEFAULT 'PCS',
    cost_price NUMERIC(12, 2) NOT NULL,
    return_value NUMERIC(14, 2) GENERATED ALWAYS AS (return_qty * cost_price) STORED,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_po_return_item_po_item_id ON trans.scm_po_return_item(po_item_id);

-- ============================================================================
-- SCM Goods Receipt Notes (GRN)
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_grn (
    grn_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    grn_no VARCHAR(50) UNIQUE NOT NULL,
    po_id UUID REFERENCES trans.scm_po(po_id),
    shipment_id UUID,
    receiver_id VARCHAR(64),
    supplier_id UUID NOT NULL,
    received_by VARCHAR(64),
    transporter VARCHAR(64),
    recv_dt TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    wh_location VARCHAR(64),
    status VARCHAR(20) NOT NULL,
    total_qty NUMERIC(14, 3),
    remarks TEXT,
    created_by VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.scm_grn_item (
    grn_item_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    grn_id UUID NOT NULL REFERENCES trans.scm_grn(grn_id) ON DELETE CASCADE,
    po_item_id UUID REFERENCES trans.scm_po_item(po_item_id),
    catalogue_id UUID,
    sku VARCHAR(64) NOT NULL,
    recv_qty NUMERIC(12, 3) NOT NULL,
    acc_qty NUMERIC(12, 3) NOT NULL,
    rej_qty NUMERIC(12, 3) DEFAULT 0,
    uom VARCHAR(10) NOT NULL,
    batch_no VARCHAR(50),
    mfg_dt DATE,
    exp_dt DATE,
    qc_status VARCHAR(20) DEFAULT 'accepted',
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.scm_grn_issue (
    issue_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    grn_item_id UUID NOT NULL REFERENCES trans.scm_grn_item(grn_item_id),
    issue_type VARCHAR(50) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL,
    created_by VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- SCM Inventory / Stock
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_stock_ledger (
    ledger_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    merchant_id VARCHAR(64) NOT NULL,
    warehouse_id VARCHAR(64) NOT NULL,
    catalogue_id VARCHAR(64),
    sku VARCHAR(64) NOT NULL,
    batch_no VARCHAR(50),
    txn_type VARCHAR(20) NOT NULL,
    qty NUMERIC(12, 3) NOT NULL,
    uom VARCHAR(20),
    ref_type VARCHAR(20) NOT NULL,
    ref_id UUID NOT NULL,
    ref_no VARCHAR(50),
    remarks TEXT,
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.scm_stock (
    stock_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    merchant_id VARCHAR(64) NOT NULL,
    warehouse_id VARCHAR(64) NOT NULL,
    sku VARCHAR(64) NOT NULL,
    batch_no VARCHAR(50),
    catalogue_id VARCHAR(64),
    uom VARCHAR(20) DEFAULT 'PCS',
    qty_on_hand NUMERIC(12, 3) DEFAULT 0 CHECK (qty_on_hand >= 0),
    qty_reserved NUMERIC(12, 3) DEFAULT 0 CHECK (qty_reserved >= 0),
    qty_available NUMERIC(12, 3) DEFAULT 0 CHECK (qty_available >= 0),
    cost_price NUMERIC(12, 2),
    expiry_date DATE,
    ledger_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_scm_stock_sku_batch UNIQUE (merchant_id, warehouse_id, catalogue_id, batch_no)
);

-- Stock Adjustment (Master/Detail)
CREATE TABLE IF NOT EXISTS trans.scm_stock_adjustment_master (
    adjustment_master_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    adjustment_number VARCHAR(50) NOT NULL,
    merchant_id VARCHAR(64) NOT NULL,
    warehouse_id VARCHAR(64) NOT NULL,
    adjustment_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    description TEXT,
    additional_notes TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'pending', 'approved', 'rejected', 'applied', 'cancelled')),
    total_items INTEGER DEFAULT 0,
    total_adjustment_value NUMERIC(15, 2) DEFAULT 0,
    requires_approval BOOLEAN DEFAULT TRUE,
    approval_threshold NUMERIC(15, 2),
    created_by VARCHAR(64) NOT NULL,
    approved_by VARCHAR(64),
    rejected_by VARCHAR(64),
    applied_by VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP WITH TIME ZONE,
    rejected_at TIMESTAMP WITH TIME ZONE,
    applied_at TIMESTAMP WITH TIME ZONE,
    rejection_reason TEXT
);

CREATE TABLE IF NOT EXISTS trans.scm_stock_adjustment_details (
    adjustment_detail_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    adjustment_master_id UUID NOT NULL REFERENCES trans.scm_stock_adjustment_master(adjustment_master_id),
    sku VARCHAR(64) NOT NULL,
    batch_no VARCHAR(50),
    adj_type VARCHAR(20) NOT NULL CHECK (adj_type IN ('damage', 'expired', 'shrinkage', 'cycle_count')),
    qty NUMERIC(12, 3) NOT NULL CHECK (qty > 0),
    system_qty NUMERIC(12, 3) NOT NULL DEFAULT 0 CHECK (system_qty >= 0),
    direction VARCHAR(10) NOT NULL DEFAULT 'OUT' CHECK (direction IN ('IN', 'OUT')),
    reason TEXT NOT NULL,
    unit_cost NUMERIC(12, 4),
    adjustment_value NUMERIC(15, 2),
    line_status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (line_status IN ('pending', 'approved', 'rejected', 'applied')),
    ledger_id UUID,
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Stock Take (Master/Detail)
CREATE TABLE IF NOT EXISTS trans.scm_stock_take_master (
    stock_take_master_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    stock_take_number VARCHAR(50) NOT NULL,
    merchant_id VARCHAR(64) NOT NULL,
    warehouse_id VARCHAR(64) NOT NULL,
    stock_take_date TIMESTAMP WITH TIME ZONE NOT NULL,
    description TEXT,
    additional_notes TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'in_progress', 'completed', 'submitted', 'approved', 'rejected', 'cancelled')),
    total_items INTEGER DEFAULT 0,
    total_variance_value NUMERIC(15, 2) DEFAULT 0,
    created_by VARCHAR(64) NOT NULL,
    approved_by VARCHAR(64),
    rejected_by VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP WITH TIME ZONE,
    rejected_at TIMESTAMP WITH TIME ZONE,
    rejection_reason TEXT
);

CREATE TABLE IF NOT EXISTS trans.scm_stock_take_details (
    stock_take_detail_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    stock_take_master_id UUID NOT NULL REFERENCES trans.scm_stock_take_master(stock_take_master_id),
    sku VARCHAR(64) NOT NULL,
    batch_no VARCHAR(50),
    system_qty NUMERIC(12, 3) NOT NULL CHECK (system_qty >= 0),
    physical_qty NUMERIC(12, 3) NOT NULL CHECK (physical_qty >= 0),
    variance_qty NUMERIC(12, 3) NOT NULL,
    unit_cost NUMERIC(12, 4),
    variance_value NUMERIC(15, 2),
    line_status VARCHAR(20) NOT NULL DEFAULT 'counted' CHECK (line_status IN ('counted', 'verified', 'adjusted')),
    adjustment_id UUID,
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.scm_stock_take_status_log (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    stock_take_master_id UUID NOT NULL REFERENCES trans.scm_stock_take_master(stock_take_master_id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL,
    changed_by VARCHAR(255),
    changed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT
);

-- ============================================================================
-- Trade Relationships
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_trade_relationship (
    relationship_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    from_merchant_id UUID NOT NULL,
    to_merchant_id UUID NOT NULL,
    relationship_type VARCHAR(30) NOT NULL DEFAULT 'procurement',
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    valid_from DATE NOT NULL DEFAULT CURRENT_DATE,
    valid_to DATE,
    credit_allowed BOOLEAN DEFAULT FALSE,
    credit_limit NUMERIC(14, 2),
    payment_terms VARCHAR(50),
    price_list_id VARCHAR(50),
    allowed_regions TEXT[],
    allowed_categories TEXT[],
    created_by VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_different_merchants CHECK (from_merchant_id != to_merchant_id),
    CONSTRAINT chk_validity_range CHECK (valid_to IS NULL OR valid_to >= valid_from),
    CONSTRAINT chk_credit_configuration CHECK (credit_allowed = false OR (credit_allowed = true AND credit_limit IS NOT NULL AND credit_limit > 0))
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_relationship ON trans.scm_trade_relationship (from_merchant_id, to_merchant_id);
CREATE INDEX IF NOT EXISTS idx_trade_from_merchant ON trans.scm_trade_relationship (from_merchant_id);
CREATE INDEX IF NOT EXISTS idx_trade_to_merchant ON trans.scm_trade_relationship (to_merchant_id);

-- ============================================================================
-- Trade Schemes
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.trade_schemes (
    scheme_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scheme_code VARCHAR(50) UNIQUE NOT NULL,
    scheme_name VARCHAR(200) NOT NULL,
    description TEXT,
    scheme_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
    applicable_level VARCHAR(20) NOT NULL,
    merchant_id UUID,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    settlement_type VARCHAR(20) NOT NULL,
    settlement_frequency VARCHAR(20) NOT NULL,
    inherit_to_children BOOLEAN DEFAULT TRUE,
    allow_child_override BOOLEAN DEFAULT TRUE,
    allow_better_only BOOLEAN DEFAULT TRUE,
    currency VARCHAR(3) DEFAULT 'INR',
    created_by VARCHAR(100),
    approved_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP,
    version INTEGER DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_trade_schemes_type_status ON trans.trade_schemes (scheme_type, status);
CREATE INDEX IF NOT EXISTS idx_trade_schemes_validity ON trans.trade_schemes (valid_from, valid_to);

CREATE TABLE IF NOT EXISTS trans.trade_scheme_conditions (
    condition_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scheme_id UUID NOT NULL REFERENCES trans.trade_schemes(scheme_id),
    min_quantity NUMERIC(15, 3),
    max_quantity NUMERIC(15, 3),
    qualifying_sku VARCHAR(50),
    min_order_value NUMERIC(15, 2),
    max_order_value NUMERIC(15, 2),
    sku_list JSON,
    category_list JSON,
    brand_list JSON,
    excluded_skus JSON,
    excluded_categories JSON,
    channel VARCHAR(20),
    aggregate_basis VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.trade_scheme_benefits (
    benefit_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scheme_id UUID NOT NULL REFERENCES trans.trade_schemes(scheme_id),
    discount_type VARCHAR(20),
    discount_percentage NUMERIC(5, 2),
    discount_amount NUMERIC(15, 2),
    free_sku VARCHAR(50),
    free_quantity NUMERIC(15, 3),
    rebate_type VARCHAR(20),
    rebate_percentage NUMERIC(5, 2),
    rebate_amount NUMERIC(15, 2),
    apply_on VARCHAR(20) DEFAULT 'TOTAL',
    max_benefit_amount NUMERIC(15, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.trade_scheme_accruals (
    accrual_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scheme_id UUID NOT NULL REFERENCES trans.trade_schemes(scheme_id),
    merchant_id UUID NOT NULL,
    invoice_id VARCHAR(50),
    invoice_date DATE,
    accrual_amount NUMERIC(15, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'INR',
    settlement_period VARCHAR(20),
    status VARCHAR(20) DEFAULT 'PENDING',
    credit_note_id VARCHAR(50),
    settled_at TIMESTAMP,
    settled_amount NUMERIC(15, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_accruals_scheme_merchant ON trans.trade_scheme_accruals (scheme_id, merchant_id);

CREATE TABLE IF NOT EXISTS trans.trade_scheme_applications (
    application_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scheme_id UUID NOT NULL REFERENCES trans.trade_schemes(scheme_id),
    merchant_id UUID NOT NULL,
    invoice_id UUID NOT NULL,
    applied_amount NUMERIC(15, 2) NOT NULL,
    benefit_type VARCHAR(20),
    status VARCHAR(20) DEFAULT 'APPLIED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_applications_invoice ON trans.trade_scheme_applications (invoice_id);

-- ============================================================================
-- Trade Shipment (Sales)
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_trade_shipment (
    shipment_id     UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    shipment_no     VARCHAR(40) UNIQUE NOT NULL,
    order_id        UUID NOT NULL REFERENCES trans.scm_po(po_id),
    supplier_id     UUID NOT NULL,
    client_id       UUID NOT NULL,
    warehouse_id    UUID NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'shipped', 'closed')),
    shipment_date   DATE NOT NULL,
    transporter     VARCHAR(100),
    lr_no           VARCHAR(50),
    vehicle_no      VARCHAR(20),
    created_by      VARCHAR(64),
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    total_amt       NUMERIC(14, 2) NOT NULL DEFAULT 0.00,
    tax_amt         NUMERIC(14, 2) NOT NULL DEFAULT 0.00,
    net_amt         NUMERIC(14, 2) NOT NULL DEFAULT 0.00
);

CREATE INDEX IF NOT EXISTS idx_trade_shipment_order_id ON trans.scm_trade_shipment(order_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_supplier_id ON trans.scm_trade_shipment(supplier_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_client_id ON trans.scm_trade_shipment(client_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_date ON trans.scm_trade_shipment(shipment_date);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_status ON trans.scm_trade_shipment(status);

CREATE TABLE IF NOT EXISTS trans.scm_trade_shipment_item (
    item_id         UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    shipment_id     UUID NOT NULL REFERENCES trans.scm_trade_shipment(shipment_id) ON DELETE CASCADE,
    po_item_id      UUID NOT NULL REFERENCES trans.scm_po_item(po_item_id),
    sku             VARCHAR(64) NOT NULL,
    batch_no        VARCHAR(50),
    ordered_qty     NUMERIC(12,3) NOT NULL,
    shipped_qty     NUMERIC(12,3) NOT NULL,
    balance_qty     NUMERIC(12,3) NOT NULL,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    catalogue_id    UUID,
    exp_dt          DATE,
    unit_price      NUMERIC(14, 2) NOT NULL,
    line_amt        NUMERIC(14, 2) NOT NULL,
    tax_amt         NUMERIC(14, 2),
    tax_rate        NUMERIC(5, 2),
    CONSTRAINT chk_trade_shipment_item_qty CHECK (
        shipped_qty >= 0 AND 
        ordered_qty >= 0 AND 
        balance_qty >= 0 AND
        shipped_qty <= ordered_qty
    )
);

CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_shipment_id ON trans.scm_trade_shipment_item(shipment_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_po_item_id ON trans.scm_trade_shipment_item(po_item_id);
CREATE INDEX IF NOT EXISTS idx_trade_shipment_item_sku ON trans.scm_trade_shipment_item(sku);

-- ============================================================================
-- Trade Invoices
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_invoice (
    invoice_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_no VARCHAR(50) UNIQUE NOT NULL,
    po_id UUID NOT NULL,
    supplier_id UUID NOT NULL,
    buyer_id UUID NOT NULL,
    invoice_date DATE NOT NULL,
    payment_terms VARCHAR(30),
    due_date DATE,
    currency VARCHAR(3) NOT NULL DEFAULT 'INR',
    subtotal_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    discount_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    taxable_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    cgst_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    sgst_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    igst_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    total_tax_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    grand_total_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    reverse_charge BOOLEAN NOT NULL DEFAULT FALSE,
    remarks TEXT,
    freight_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    packing_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    other_charges_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    round_off_amt NUMERIC(14, 2) NOT NULL DEFAULT 0,
    created_by VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_scm_invoice_supplier_id ON trans.scm_invoice(supplier_id);
CREATE INDEX IF NOT EXISTS idx_scm_invoice_buyer_id ON trans.scm_invoice(buyer_id);
CREATE INDEX IF NOT EXISTS idx_scm_invoice_po_id ON trans.scm_invoice(po_id);
CREATE INDEX IF NOT EXISTS idx_scm_invoice_status ON trans.scm_invoice(status);
CREATE INDEX IF NOT EXISTS idx_scm_invoice_date ON trans.scm_invoice(invoice_date);

CREATE TABLE IF NOT EXISTS trans.scm_invoice_item (
    invoice_item_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id UUID NOT NULL REFERENCES trans.scm_invoice(invoice_id) ON DELETE CASCADE,
    po_item_id UUID NOT NULL,
    catalogue_id UUID NOT NULL,
    sku VARCHAR(64) NOT NULL,
    hsn_code VARCHAR(10),
    invoice_qty NUMERIC(12, 3) NOT NULL,
    unit_price NUMERIC(12, 2) NOT NULL,
    discount_pct NUMERIC(5, 2),
    discount_amt NUMERIC(12, 2),
    tax_rate NUMERIC(5, 2),
    tax_amt NUMERIC(12, 2),
    line_total NUMERIC(14, 2) NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_scm_invoice_item_invoice_id ON trans.scm_invoice_item(invoice_id);
CREATE INDEX IF NOT EXISTS idx_scm_invoice_item_po_item_id ON trans.scm_invoice_item(po_item_id);

CREATE TABLE IF NOT EXISTS trans.scm_invoice_status_log (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    invoice_id UUID NOT NULL REFERENCES trans.scm_invoice(invoice_id) ON DELETE CASCADE,
    action VARCHAR(20),
    from_status VARCHAR(20),
    to_status VARCHAR(20),
    remarks TEXT,
    performed_by VARCHAR(64),
    performed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- Credit/Debit Notes (Invoice Adjustments)
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_invoice_adjustment_note (
    note_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    note_no VARCHAR(50) UNIQUE NOT NULL,
    invoice_id UUID NOT NULL,
    supplier_id UUID NOT NULL,
    buyer_id UUID NOT NULL,
    category_code VARCHAR(30) NOT NULL,
    reason_code VARCHAR(50) NOT NULL,
    note_type VARCHAR(10) NOT NULL,
    note_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    original_amount NUMERIC(14, 2),
    revised_amount NUMERIC(14, 2),
    adjustment_amount NUMERIC(14, 2),
    taxable_amount NUMERIC(14, 2),
    original_tax_rate NUMERIC(5, 2),
    correct_tax_rate NUMERIC(5, 2),
    tax_adjustment_amt NUMERIC(14, 2),
    description TEXT NOT NULL,
    remarks TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    created_by VARCHAR(64),
    created_by_username VARCHAR(64),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(64),
    updated_by_username VARCHAR(64),
    submitted_by VARCHAR(64),
    submitted_at TIMESTAMP WITH TIME ZONE,
    approved_by VARCHAR(64),
    approved_at TIMESTAMP WITH TIME ZONE,
    rejected_by VARCHAR(64),
    rejected_at TIMESTAMP WITH TIME ZONE,
    applied_by VARCHAR(64),
    applied_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_invoice_id ON trans.scm_invoice_adjustment_note(invoice_id);
CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_supplier_id ON trans.scm_invoice_adjustment_note(supplier_id);
CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_buyer_id ON trans.scm_invoice_adjustment_note(buyer_id);

CREATE TABLE IF NOT EXISTS trans.scm_invoice_adjustment_status_log (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    note_id UUID NOT NULL REFERENCES trans.scm_invoice_adjustment_note(note_id) ON DELETE CASCADE,
    action VARCHAR(20),
    from_status VARCHAR(20),
    to_status VARCHAR(20),
    remarks TEXT,
    performed_by VARCHAR(64),
    performed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- Spa Partner Orders
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.spa_partner_orders (
    order_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_number VARCHAR(50) UNIQUE NOT NULL,
    partner_id VARCHAR(50) NOT NULL,
    merchant_id VARCHAR(50) NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    tax_amount NUMERIC(12, 2),
    discount_amount NUMERIC(12, 2),
    net_amount NUMERIC(12, 2),
    payment_status VARCHAR(30),
    payment_mode VARCHAR(30),
    delivery_address TEXT,
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS trans.spa_partner_order_items (
    id SERIAL PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES trans.spa_partner_orders(order_id),
    product_id VARCHAR(50) NOT NULL,
    product_name VARCHAR(200),
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    tax_percent NUMERIC(5, 2),
    total_price NUMERIC(12, 2)
);

-- ============================================================================
-- Trade Returns
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.scm_return (
    return_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    return_no VARCHAR(50) UNIQUE NOT NULL,
    return_type VARCHAR(20) NOT NULL CHECK (return_type IN ('PURCHASE', 'SALES')),
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'SUBMITTED', 'APPROVED', 'COMPLETED', 'REJECTED')),
    invoice_id UUID NOT NULL,
    invoice_no VARCHAR(50) NOT NULL,
    buyer_id VARCHAR(64) NOT NULL,
    buyer_name VARCHAR(200) NOT NULL,
    supplier_id VARCHAR(64) NOT NULL,
    supplier_name VARCHAR(200) NOT NULL,
    reason_code VARCHAR(50) NOT NULL CHECK (reason_code IN ('DAMAGED_GOODS', 'EXPIRED_STOCK', 'QUALITY_ISSUE', 'WRONG_ITEM', 'EXCESS_STOCK', 'DEFECTIVE', 'RECALL', 'OTHER')),
    remarks TEXT,
    dispatch_date TIMESTAMP WITH TIME ZONE,
    dispatch_reference VARCHAR(100),
    received_date TIMESTAMP WITH TIME ZONE,
    received_by VARCHAR(100),
    qc_completed_date TIMESTAMP WITH TIME ZONE,
    qc_performed_by VARCHAR(100),
    created_by VARCHAR(64) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(64),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_scm_return_invoice_id ON trans.scm_return(invoice_id);
CREATE INDEX IF NOT EXISTS idx_scm_return_buyer_id ON trans.scm_return(buyer_id);
CREATE INDEX IF NOT EXISTS idx_scm_return_supplier_id ON trans.scm_return(supplier_id);
CREATE INDEX IF NOT EXISTS idx_scm_return_status ON trans.scm_return(status);

CREATE TABLE IF NOT EXISTS trans.scm_return_item (
    return_item_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    return_id UUID NOT NULL REFERENCES trans.scm_return(return_id) ON DELETE CASCADE,
    invoice_item_id UUID NOT NULL,
    catalogue_id UUID NOT NULL,
    sku VARCHAR(64) NOT NULL,
    description VARCHAR(500) NOT NULL,
    batch_no VARCHAR(50) NOT NULL,
    expiry_date TIMESTAMP WITH TIME ZONE,
    return_qty NUMERIC(12, 3) NOT NULL,
    qc_status VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (qc_status IN ('PENDING', 'ACCEPTED', 'REJECTED')),
    qc_qty NUMERIC(12, 3),
    qc_rejected_qty NUMERIC(12, 3),
    qc_remarks TEXT,
    qc_performed_by VARCHAR(100),
    qc_performed_at TIMESTAMP WITH TIME ZONE,
    unit_price NUMERIC(10, 2),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_scm_return_item_return_id ON trans.scm_return_item(return_id);
CREATE INDEX IF NOT EXISTS idx_scm_return_item_invoice_item_id ON trans.scm_return_item(invoice_item_id);
CREATE INDEX IF NOT EXISTS idx_scm_return_item_sku ON trans.scm_return_item(sku);

CREATE TABLE IF NOT EXISTS trans.scm_return_status_log (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    return_id UUID NOT NULL REFERENCES trans.scm_return(return_id) ON DELETE CASCADE,
    action VARCHAR(20) NOT NULL,
    from_status VARCHAR(20),
    to_status VARCHAR(20) NOT NULL,
    performed_by VARCHAR(64) NOT NULL,
    performed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT,
    reference_no VARCHAR(100)
);

CREATE INDEX IF NOT EXISTS idx_scm_return_status_log_return_id ON trans.scm_return_status_log(return_id);

-- ============================================================================
-- Legacy Sales Orders (B2C)
-- ============================================================================

CREATE TABLE IF NOT EXISTS trans.branches (
    branch_id VARCHAR(26) PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS trans.merchants (
    merchant_id VARCHAR(26) PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS trans.customers (
    customer_id VARCHAR(26) PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS trans.users (
    user_id VARCHAR(26) PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS trans.catalogues (
    catalogue_id VARCHAR(26) PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS trans.sales_orders (
    sales_order_id VARCHAR(26) PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    branch_id VARCHAR(26) NOT NULL,
    merchant_id VARCHAR(26) NOT NULL,
    order_date TIMESTAMP WITH TIME ZONE NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('draft', 'confirmed', 'processing', 'fulfilled', 'partially_fulfilled', 'cancelled', 'on_hold')),
    customer_id VARCHAR(26) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_type VARCHAR(10) NOT NULL CHECK (customer_type IN ('b2b', 'b2c')),
    customer_phone VARCHAR(20),
    customer_email VARCHAR(255),
    customer_gstin VARCHAR(15),
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_discount DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_tax DECIMAL(12,2) NOT NULL DEFAULT 0,
    shipping_charges DECIMAL(12,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    cgst DECIMAL(12,2) DEFAULT 0,
    sgst DECIMAL(12,2) DEFAULT 0,
    igst DECIMAL(12,2) DEFAULT 0,
    payment_type VARCHAR(20) NOT NULL CHECK (payment_type IN ('prepaid', 'cod', 'credit', 'partial')),
    payment_status VARCHAR(20) NOT NULL CHECK (payment_status IN ('unpaid', 'partial', 'paid', 'refunded', 'overdue')),
    payment_method VARCHAR(50),
    payment_date TIMESTAMP WITH TIME ZONE,
    payment_reference VARCHAR(100),
    amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
    amount_due DECIMAL(12,2) NOT NULL DEFAULT 0,
    credit_terms VARCHAR(50),
    credit_limit DECIMAL(12,2),
    fulfillment_status VARCHAR(20) NOT NULL CHECK (fulfillment_status IN ('pending', 'allocated', 'picked', 'packed', 'shipped', 'delivered')),
    expected_delivery_date DATE,
    actual_delivery_date DATE,
    invoice_id VARCHAR(26),
    invoice_number VARCHAR(50),
    invoice_date TIMESTAMP WITH TIME ZONE,
    invoice_pdf_url TEXT,
    notes TEXT,
    internal_notes TEXT,
    created_by VARCHAR(26) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_by VARCHAR(26),
    approved_at TIMESTAMP WITH TIME ZONE,
    source VARCHAR(20) DEFAULT 'web',
    channel VARCHAR(50),
    tags TEXT[],
    version INTEGER NOT NULL DEFAULT 1,
    CONSTRAINT fk_sales_order_branch FOREIGN KEY (branch_id) REFERENCES trans.branches(branch_id),
    CONSTRAINT fk_sales_order_merchant FOREIGN KEY (merchant_id) REFERENCES trans.merchants(merchant_id),
    CONSTRAINT fk_sales_order_customer FOREIGN KEY (customer_id) REFERENCES trans.customers(customer_id),
    CONSTRAINT fk_sales_order_created_by FOREIGN KEY (created_by) REFERENCES trans.users(user_id)
);

CREATE TABLE IF NOT EXISTS trans.sales_order_items (
    id VARCHAR(26) PRIMARY KEY,
    sales_order_id VARCHAR(26) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    product_id VARCHAR(26) NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    item_type VARCHAR(20) NOT NULL CHECK (item_type IN ('product', 'service')),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    tax_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    line_total DECIMAL(12,2) NOT NULL,
    hsn_code VARCHAR(10),
    uom VARCHAR(20) NOT NULL,
    batch_no VARCHAR(50),
    serials TEXT[],
    staff_id VARCHAR(26),
    staff_name VARCHAR(255),
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sales_order_item_order FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE,
    CONSTRAINT fk_sales_order_item_product FOREIGN KEY (product_id) REFERENCES trans.catalogues(catalogue_id)
);

CREATE TABLE IF NOT EXISTS trans.sales_order_addresses (
    id VARCHAR(26) PRIMARY KEY,
    sales_order_id VARCHAR(26) NOT NULL,
    address_type VARCHAR(20) NOT NULL CHECK (address_type IN ('billing', 'shipping')),
    line1 VARCHAR(255) NOT NULL,
    line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL DEFAULT 'India',
    landmark VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sales_order_address FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS trans.sales_order_shipments (
    shipment_id VARCHAR(26) PRIMARY KEY,
    sales_order_id VARCHAR(26) NOT NULL,
    carrier VARCHAR(100),
    tracking_number VARCHAR(100),
    awb_number VARCHAR(100),
    shipping_method VARCHAR(50),
    dispatch_date TIMESTAMP WITH TIME ZONE,
    expected_delivery_date DATE,
    actual_delivery_date DATE,
    status VARCHAR(20) CHECK (status IN ('pending', 'picked', 'in_transit', 'out_for_delivery', 'delivered', 'failed')),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sales_order_shipment FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS trans.sales_order_documents (
    id VARCHAR(26) PRIMARY KEY,
    sales_order_id VARCHAR(26) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    file_url TEXT NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    file_size INTEGER,
    mime_type VARCHAR(100),
    uploaded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    uploaded_by VARCHAR(26),
    CONSTRAINT fk_sales_order_document FOREIGN KEY (sales_order_id) REFERENCES trans.sales_orders(sales_order_id) ON DELETE CASCADE
);

-- ============================================================================
-- Additional Indexes for Performance Optimization
-- ============================================================================

-- Invoice Adjustment Note Indexes
CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_status ON trans.scm_invoice_adjustment_note(status);
CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_type ON trans.scm_invoice_adjustment_note(note_type);
CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_category ON trans.scm_invoice_adjustment_note(category_code);
CREATE INDEX IF NOT EXISTS idx_scm_adjustment_note_date ON trans.scm_invoice_adjustment_note(note_date);

-- Trade Return Composite Indexes
CREATE INDEX IF NOT EXISTS idx_returns_type_status ON trans.scm_return (return_type, status);
CREATE INDEX IF NOT EXISTS idx_returns_buyer_status ON trans.scm_return (buyer_id, status);
CREATE INDEX IF NOT EXISTS idx_returns_supplier_status ON trans.scm_return (supplier_id, status);
CREATE INDEX IF NOT EXISTS idx_returns_invoice_type ON trans.scm_return (invoice_id, return_type);
CREATE INDEX IF NOT EXISTS idx_returns_created_status ON trans.scm_return (created_at, status);

-- Trade Return Item Indexes
CREATE INDEX IF NOT EXISTS idx_return_items_sku_batch ON trans.scm_return_item (sku, batch_no);
CREATE INDEX IF NOT EXISTS idx_return_items_qc_status ON trans.scm_return_item (qc_status);
CREATE INDEX IF NOT EXISTS idx_return_items_catalogue_batch ON trans.scm_return_item (catalogue_id, batch_no);

-- Trade Return Log Indexes
CREATE INDEX IF NOT EXISTS idx_return_logs_action_date ON trans.scm_return_status_log (action, performed_at);
CREATE INDEX IF NOT EXISTS idx_return_logs_status_date ON trans.scm_return_status_log (to_status, performed_at);