-- Fixes a tangled pair of migrations discovered while wiring up role-scoped
-- AI credit packages:
--
--   20260615060600_ai_credit_packages.up.sql (active) created
--   ai_credit_packages and seeded it with price_inr = 99/349/999/2499 -
--   values that look like whole rupees, but every consumer of this column
--   (apps/payments/src/payu.rs's paise_to_rupee_string, and this repo's own
--   pricing_packages.price_inr convention) treats price_inr as PAISE. So a
--   "Starter AI Credits" purchase would have charged ₹0.99, not ₹99.
--
--   20260705110000_ai_credit_packages.up.sql.skip (disabled) was the
--   correction - right paise values (9900/34900/99900/249900) AND the
--   ai_credit_orders table apps/payments/src/ai_credits.rs's create_order
--   actually inserts into - but it re-issued CREATE TABLE ai_credit_packages
--   without IF NOT EXISTS, so it errored on "relation already exists" and
--   got skipped in the same bulk cleanup as everything else this session
--   has been un-tangling, taking ai_credit_orders down with it.
--
-- Net effect fixed here: the purchase flow was 100% broken (create_order
-- would 500 immediately - no ai_credit_orders table to insert into) and,
-- had it worked, would have wildly underscharged. Confirmed zero rows in
-- ai_credit_orders anywhere (the table didn't exist), so there is no real
-- purchase history to reconcile - this is a pre-launch fix, not a refund.

BEGIN;

UPDATE ai_credit_packages SET price_inr = 9900   WHERE name = 'Starter AI Credits'    AND price_inr = 99;
UPDATE ai_credit_packages SET price_inr = 34900  WHERE name = 'Pro AI Credits'        AND price_inr = 349;
UPDATE ai_credit_packages SET price_inr = 99900  WHERE name = 'Business AI Credits'   AND price_inr = 999;
UPDATE ai_credit_packages SET price_inr = 249900 WHERE name = 'Enterprise AI Credits' AND price_inr = 2499;

CREATE TABLE IF NOT EXISTS ai_credit_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    package_id UUID NOT NULL REFERENCES ai_credit_packages(id),
    txnid VARCHAR(100) UNIQUE NOT NULL,
    payu_payment_id VARCHAR(100),
    amount_inr INT NOT NULL, -- paise
    credits INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING', -- PENDING, SUCCESS, FAILED
    coupon_code VARCHAR(100),
    discount_applied INT NOT NULL DEFAULT 0, -- paise
    verified_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ai_credit_orders_user_id ON ai_credit_orders(user_id);
CREATE INDEX IF NOT EXISTS idx_ai_credit_orders_txnid ON ai_credit_orders(txnid);

COMMIT;
