-- Manual payment reconciliation.
--
-- A UPI QR or phone number is a one-way channel: the money lands in the
-- editor's account and nothing tells this server it happened. There is no
-- callback to verify and no signature to check, so the only honest design is
-- that the author declares a payment and a human confirms it against the bank
-- statement. Everything below exists to make that exchange auditable.

ALTER TABLE payments
    -- Existing rows predate this column and were all Razorpay-shaped attempts.
    ADD COLUMN method payment_method NOT NULL DEFAULT 'MANUAL_UPI',

    -- The UTR: the 12-digit reference every UPI app shows on its success
    -- screen. It is the only string that exists on both sides of the
    -- transaction — in the author's app and on the editor's bank statement —
    -- which is what makes matching possible at all. Without it, reconciling
    -- "Rs.826 on 5 Sep" to a specific author is guesswork the moment two
    -- people pay the same amount on the same day.
    ADD COLUMN reference VARCHAR(64),
    ADD COLUMN declared_at TIMESTAMPTZ,
    -- Free text from the author: which app, whose account it came from. Often
    -- the thing that resolves an ambiguous statement line.
    ADD COLUMN payer_note VARCHAR(500),

    -- Who confirmed, and when. The system records a human's decision here, not
    -- a verified fact — the bank statement is the only ground truth, so the
    -- trail has to say whose judgement it was.
    ADD COLUMN confirmed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    ADD COLUMN confirmed_at TIMESTAMPTZ,
    ADD COLUMN rejection_reason VARCHAR(500);

-- One reference, one payment. This is the single cheapest fraud check
-- available here: without it a genuine UTR could be pasted into five
-- submissions and each would look equally plausible in the queue.
--
-- Partial, so rejected claims do not block the real payer from later using the
-- same reference, and so the many rows with no reference yet do not collide.
CREATE UNIQUE INDEX idx_payments_reference
    ON payments (reference)
    WHERE reference IS NOT NULL AND status <> 'REJECTED';

-- The admin queue's default view: oldest unconfirmed first.
CREATE INDEX idx_payments_status_declared ON payments (status, declared_at);

-- Payee details shown to the author. In the CMS rather than configuration so
-- the editor can change a UPI ID himself; blank by default because a wrong
-- payee address is worse than an obviously missing one — the payment form
-- refuses to render until these are filled in.
INSERT INTO cms_content (section, group_key, field_key, title_hi, title_en, value_hi, value_en, field_type, sort_order)
VALUES
  ('PAYMENT', 'upi', 'upi_id',
   'UPI आईडी', 'UPI ID', '', '', 'text', 1),
  ('PAYMENT', 'upi', 'payee_name',
   'खाता धारक का नाम', 'Payee name', '', '', 'text', 2),
  ('PAYMENT', 'upi', 'instructions',
   'भुगतान निर्देश', 'Payment instructions',
   '<p>ऊपर दिए गए QR को स्कैन करें या UPI आईडी पर भुगतान करें। भुगतान के बाद अपने UPI ऐप में दिखने वाला <strong>UTR / रेफ़रेंस नंबर</strong> नीचे दर्ज करें।</p>',
   '<p>Scan the QR above or pay to the UPI ID. After paying, enter the <strong>UTR / reference number</strong> shown in your UPI app below.</p>',
   'richtext', 3)
ON CONFLICT DO NOTHING;