CREATE TABLE IF NOT EXISTS bazzar_pos.return_sales (
    id BIGSERIAL PRIMARY KEY,
    sales_order_id BIGINT NOT NULL REFERENCES bazzar_pos.sales_orders(id),
    reason TEXT NOT NULL,
    returned_total NUMERIC(14,2) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'success',
    created_by BIGINT REFERENCES bazzar_pos.users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_return_sales_sales_order_id ON bazzar_pos.return_sales (sales_order_id);

CREATE TABLE IF NOT EXISTS bazzar_pos.return_sale_items (
    id BIGSERIAL PRIMARY KEY,
    return_sale_id BIGINT NOT NULL REFERENCES bazzar_pos.return_sales(id) ON DELETE CASCADE,
    sales_order_item_id BIGINT NOT NULL REFERENCES bazzar_pos.sales_order_items(id),
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    qty INTEGER NOT NULL CHECK (qty > 0),
    amount NUMERIC(14,2) NOT NULL CHECK (amount >= 0)
);

CREATE INDEX IF NOT EXISTS idx_return_sale_items_return_sale_id ON bazzar_pos.return_sale_items (return_sale_id);
CREATE INDEX IF NOT EXISTS idx_return_sale_items_sales_order_item_id ON bazzar_pos.return_sale_items (sales_order_item_id);

