CREATE TABLE IF NOT EXISTS sales_by_product (
    sales_by_product_id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    sale_date DATE NOT NULL,
    product_id uuid NOT NULL,
    pos_id VARCHAR(50) NOT NULL,
    cashier_id uuid NOT NULL,
    quantity NUMERIC(15, 4) NOT NULL DEFAULT 0,
    value NUMERIC(15, 2) NOT NULL DEFAULT 0,
    updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ord_id uuid NOT NULL
);

-- Indexes for sales_by_product table
-- Primary date-based queries (most common for sales reports)
CREATE INDEX IF NOT EXISTS idx_sales_by_product_sale_date 
    ON sales_by_product(sale_date DESC);

-- Product performance queries
CREATE INDEX IF NOT EXISTS idx_sales_by_product_product_id 
    ON sales_by_product(product_id, sale_date DESC);

-- POS/location-based analysis
CREATE INDEX IF NOT EXISTS idx_sales_by_product_pos_id 
    ON sales_by_product(pos_id, sale_date DESC);

-- Cashier performance tracking
CREATE INDEX IF NOT EXISTS idx_sales_by_product_cashier_id 
    ON sales_by_product(cashier_id, sale_date DESC);

-- Order reference lookup
CREATE INDEX IF NOT EXISTS idx_sales_by_product_ord_id 
    ON sales_by_product(ord_id);

-- Composite index for date range queries with product filtering
CREATE INDEX IF NOT EXISTS idx_sales_by_product_date_product 
    ON sales_by_product(sale_date DESC, product_id, value);

-- Composite index for POS + date range reporting
CREATE INDEX IF NOT EXISTS idx_sales_by_product_pos_date 
    ON sales_by_product(pos_id, sale_date DESC, value);

-- Sync/update tracking
CREATE INDEX IF NOT EXISTS idx_sales_by_product_updatedat 
    ON sales_by_product(updatedat DESC);
