-- pricing_packages and payments back the whole Tracecoin/job-slot purchase
-- flow (apps/payments, apps/users/src/handlers/pricing.rs). Like
-- verifications, neither was ever created by any active migration — only
-- documented in scripts/init-db.sql, which is never actually executed (see
-- 20260718200000_create_verifications_and_approvals for the full
-- explanation). This is the earliest point in the chain that needed
-- `payments` to exist: 20260317202300_coupons_discounts.up.sql (already
-- deployed) references payments(id) via FK a few hours after this file's
-- timestamp.
--
-- Schema matches apps/payments/src/main.rs's actual usage — payu_txnid/
-- payu_mihpayid directly (the PayU column names), not razorpay_order_id/
-- razorpay_payment_id like init-db.sql has; those were renamed by
-- 20260626000000_payu_rename_columns, which only runs correctly against a
-- table that has the *old* Razorpay names to rename FROM. Since this table
-- never existed for that migration to act on, starting directly with the
-- final PayU names is correct and makes that rename migration a no-op here
-- (guarded by information_schema existence checks, so that's safe).
CREATE TABLE IF NOT EXISTS pricing_packages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    role_key VARCHAR(50) NOT NULL,
    package_type VARCHAR(50) NOT NULL,
    tracecoins_amount INTEGER NOT NULL DEFAULT 0,
    price_inr INTEGER NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    package_id UUID REFERENCES pricing_packages(id),
    payu_txnid VARCHAR(100),
    payu_mihpayid VARCHAR(100),
    amount_inr INTEGER NOT NULL,
    tracecoins_credited INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    verified_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_payments_user_id ON payments(user_id);
CREATE INDEX IF NOT EXISTS idx_payments_payu_txnid ON payments(payu_txnid);
CREATE INDEX IF NOT EXISTS idx_payments_payu_mihpayid ON payments(payu_mihpayid);
