-- 005_payments_and_withdrawals.sql
-- Payment Gateways, Mid-Match Chip Purchases, and Encrypted Withdrawal Requests

CREATE TABLE IF NOT EXISTS payment_gateways (
  provider varchar(24) PRIMARY KEY CHECK (provider IN ('razorpay','cashfree','payu','phonepe','paytm','manual_upi')),
  display_name varchar(60) NOT NULL,
  enabled boolean NOT NULL DEFAULT false,
  mode varchar(12) NOT NULL DEFAULT 'test' CHECK (mode IN ('test','live')),
  public_key varchar(200) NOT NULL DEFAULT '',
  secret_enc text NOT NULL DEFAULT '',
  webhook_secret_enc text NOT NULL DEFAULT '',
  upi_id varchar(120) NOT NULL DEFAULT '',
  merchant_name varchar(120) NOT NULL DEFAULT 'Royal Teen Patti',
  chips_per_inr int NOT NULL DEFAULT 10 CHECK (chips_per_inr BETWEEN 1 AND 10000),
  min_inr int NOT NULL DEFAULT 10 CHECK (min_inr >= 1),
  max_inr int NOT NULL DEFAULT 50000 CHECK (max_inr >= min_inr),
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO payment_gateways (provider, display_name, enabled, mode, chips_per_inr, min_inr, max_inr)
VALUES
  ('razorpay', 'Razorpay (UPI / Cards / NetBanking)', true, 'test', 10, 10, 50000),
  ('cashfree', 'Cashfree Payments (UPI Intent / Cards)', true, 'test', 10, 10, 50000),
  ('payu', 'PayU India', false, 'test', 10, 10, 50000),
  ('phonepe', 'PhonePe PG', false, 'test', 10, 10, 50000),
  ('paytm', 'Paytm PG', false, 'test', 10, 10, 50000),
  ('manual_upi', 'Direct UPI QR / Intent (GPay, PhonePe, Paytm)', true, 'live', 10, 10, 50000)
ON CONFLICT (provider) DO NOTHING;

CREATE TABLE IF NOT EXISTS buy_orders (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  provider varchar(24) NOT NULL REFERENCES payment_gateways(provider),
  amount_inr int NOT NULL CHECK (amount_inr BETWEEN 1 AND 100000),
  chips_amount bigint NOT NULL CHECK (chips_amount BETWEEN 1 AND 100000000),
  room_id uuid REFERENCES rooms(id) ON DELETE SET NULL,
  auto_add_to_table boolean NOT NULL DEFAULT false,
  status varchar(16) NOT NULL DEFAULT 'created' CHECK (status IN ('created','pending_review','paid','failed','rejected')),
  gateway_order_id varchar(140),
  gateway_payment_id varchar(140),
  utr_number varchar(60),
  reviewer_id uuid REFERENCES users(id),
  review_note varchar(250),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_buy_orders_unique_payment
  ON buy_orders(provider, gateway_payment_id)
  WHERE gateway_payment_id IS NOT NULL AND gateway_payment_id <> '';

CREATE UNIQUE INDEX IF NOT EXISTS idx_buy_orders_unique_utr
  ON buy_orders(utr_number)
  WHERE utr_number IS NOT NULL AND utr_number <> '';

CREATE INDEX IF NOT EXISTS idx_buy_orders_user ON buy_orders(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_buy_orders_status ON buy_orders(status, created_at DESC);

CREATE TABLE IF NOT EXISTS withdrawal_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  chips_amount bigint NOT NULL CHECK (chips_amount BETWEEN 100 AND 100000000),
  amount_inr int NOT NULL CHECK (amount_inr BETWEEN 10 AND 1000000),
  payout_method varchar(12) NOT NULL CHECK (payout_method IN ('upi','bank')),
  payout_details_enc text NOT NULL,
  payout_summary_masked varchar(160) NOT NULL,
  status varchar(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','cancelled')),
  reviewer_id uuid REFERENCES users(id),
  review_note varchar(250),
  payout_utr varchar(100),
  created_at timestamptz NOT NULL DEFAULT now(),
  reviewed_at timestamptz
);

CREATE INDEX IF NOT EXISTS idx_withdrawals_user ON withdrawal_requests(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_withdrawals_status ON withdrawal_requests(status, created_at DESC);