BEGIN;

CREATE TABLE IF NOT EXISTS sales.sales_receipts (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    receipt_no varchar(80) NOT NULL,
    customer_id bigint NOT NULL,
    customer_name varchar(180),
    invoice_link_id bigint REFERENCES sales.sales_customer_invoice_links(id) ON DELETE SET NULL,
    invoice_no varchar(80),
    sales_order_id bigint REFERENCES sales.sales_orders(id) ON DELETE SET NULL,
    sales_order_no varchar(80),
    receipt_date date NOT NULL DEFAULT CURRENT_DATE,
    payment_method varchar(80) NOT NULL DEFAULT 'bank_transfer',
    reference_no varchar(120),
    currency_code varchar(10) NOT NULL DEFAULT 'GHS',
    amount numeric(18,2) NOT NULL DEFAULT 0,
    allocated_amount numeric(18,2) NOT NULL DEFAULT 0,
    unapplied_amount numeric(18,2) NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'posted',
    notes text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    is_deleted boolean NOT NULL DEFAULT false,
    deleted_at timestamp without time zone,
    deleted_by uuid,
    created_by uuid,
    updated_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sales.sales_receipt_allocations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id),
    receipt_id bigint NOT NULL REFERENCES sales.sales_receipts(id) ON DELETE CASCADE,
    invoice_link_id bigint NOT NULL REFERENCES sales.sales_customer_invoice_links(id) ON DELETE CASCADE,
    invoice_no varchar(80) NOT NULL,
    allocated_amount numeric(18,2) NOT NULL DEFAULT 0,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_sales_receipts_no
    ON sales.sales_receipts (tenant_id, receipt_no)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_sales_receipts_customer
    ON sales.sales_receipts (tenant_id, customer_id, receipt_date DESC)
    WHERE COALESCE(is_deleted, false) = false;

CREATE INDEX IF NOT EXISTS idx_sales_receipt_allocations_invoice
    ON sales.sales_receipt_allocations (tenant_id, invoice_link_id);

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'cutehorse_app') THEN
        GRANT SELECT, INSERT, UPDATE, DELETE ON sales.sales_receipts TO cutehorse_app;
        GRANT SELECT, INSERT, UPDATE, DELETE ON sales.sales_receipt_allocations TO cutehorse_app;
        GRANT USAGE, SELECT, UPDATE ON SEQUENCE sales.sales_receipts_id_seq TO cutehorse_app;
        GRANT USAGE, SELECT, UPDATE ON SEQUENCE sales.sales_receipt_allocations_id_seq TO cutehorse_app;
    END IF;
END $$;

COMMIT;