-- AI credit purchase packages + PayU order tracking, matching the
-- already-shipped frontend contract in
-- nxtgauge-frontend-solid/src/components/dashboard/CreditsPage.tsx
-- (GET /api/ai-credits, POST /api/ai-credits/order, POST /api/ai-credits/verify).
-- price_inr is stored in paise (matches pricing_packages.price_inr's
-- existing convention in this codebase), not rupees.

BEGIN;

CREATE TABLE ai_credit_packages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(150) NOT NULL,
    description TEXT,
    credits INT NOT NULL,
    price_inr INT NOT NULL, -- paise
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ai_credit_packages_active ON ai_credit_packages(is_active, price_inr);

INSERT INTO ai_credit_packages (name, description, credits, price_inr) VALUES
('Starter AI Credits', '50 AI credits for casual usage', 50, 9900),
('Pro AI Credits', '200 AI credits for power users', 200, 34900),
('Business AI Credits', '750 AI credits for teams', 750, 99900),
('Enterprise AI Credits', '2500 AI credits for heavy usage', 2500, 249900);

-- PayU order tracking for AI credit purchases, separate from the
-- TraceCoins `payments` table (crates/db/migrations/20260317190300_portfolio_payments.up.sql)
-- per the earlier decision to keep AI credits fully separate from TraceCoins.
CREATE TABLE 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
    verified_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ai_credit_orders_user_id ON ai_credit_orders(user_id);
CREATE INDEX idx_ai_credit_orders_txnid ON ai_credit_orders(txnid);

COMMIT;
