docs/database/POSTGRES_DB.md

-- ============================================================================
-- Database Schema for Sales, Returns & Credit Notes
-- PostgreSQL 14+
-- ============================================================================

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

CREATE SCHEMA IF NOT EXISTS trans;

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
);

-- =========================================================================
-- SALES ORDERS MODULE
-- ============================================================================

-- Sales Orders Table
CREATE TABLE 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 Information
    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),
    
    -- Financial Summary
    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 Information
    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 Information
    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 Information
    invoice_id VARCHAR(26),
    invoice_number VARCHAR(50),
    invoice_date TIMESTAMP WITH TIME ZONE,
    invoice_pdf_url TEXT,
    
    -- Additional Information
    notes TEXT,
    internal_notes TEXT,
    
    -- Audit Fields
    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,
    
    -- Metadata
    source VARCHAR(20) DEFAULT 'web',
    channel VARCHAR(50),
    tags TEXT[],
    version INTEGER NOT NULL DEFAULT 1,
    
    -- Foreign Keys
    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)
);

-- Sales Order Items Table
CREATE TABLE trans.sales_order_items (
    id VARCHAR(26) PRIMARY KEY,
    sales_order_id VARCHAR(26) NOT NULL,
    
    -- Product Information
    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 and Pricing
    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,
    
    -- Tax Information
    hsn_code VARCHAR(10),
    uom VARCHAR(20) NOT NULL,
    
    -- Traceability
    batch_no VARCHAR(50),
    serials TEXT[],
    
    -- Service Specific
    staff_id VARCHAR(26),
    staff_name VARCHAR(255),
    
    -- Additional
    remarks TEXT,
    
    -- Audit
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    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)
);

-- Sales Order Addresses Table
CREATE TABLE 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
);

-- Sales Order Shipments Table
CREATE TABLE 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
);

-- Sales Order Documents Table
CREATE TABLE 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
);