CREATE TABLE IF NOT EXISTS bazzar_pos.sales_reconciliation_adjustments (
    sale_id BIGINT PRIMARY KEY REFERENCES bazzar_pos.sales_orders(id) ON DELETE CASCADE,
    reason TEXT NOT NULL,
    discount_adjustment NUMERIC(18,2) NOT NULL DEFAULT 0,
    total_adjustment NUMERIC(18,2) NOT NULL DEFAULT 0,
    created_by BIGINT REFERENCES bazzar_pos.users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by BIGINT REFERENCES bazzar_pos.users(id) ON DELETE SET NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.sales_reconciliation_log (
    id BIGSERIAL PRIMARY KEY,
    sale_id BIGINT NOT NULL REFERENCES bazzar_pos.sales_orders(id) ON DELETE CASCADE,
    reason TEXT NOT NULL,
    before_snapshot JSONB NOT NULL,
    after_snapshot JSONB NOT NULL,
    corrected_by BIGINT REFERENCES bazzar_pos.users(id) ON DELETE SET NULL,
    corrected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (sale_id, reason)
);

CREATE INDEX IF NOT EXISTS idx_sales_reconciliation_log_sale_id
    ON bazzar_pos.sales_reconciliation_log(sale_id);
