/**
 * db/payments.js — payment, subscription, and user_credits queries.
 *
 * Owns: payments, subscriptions, user_credits tables.
 * Does NOT own: user auth, oracle connections, health check logic.
 */

const pool = require('./index');

// Pricing constants and calculation logic live in a single config module.
// Import from there — never define prices here directly.
const pricing = require('../config/pricing');

// Re-export for callers that reach db/payments.js for these functions
const { calcPerConnAmount, getPricingSummary } = pricing;
const CONN_PRICES_USD = pricing.CONN_PRICES; // backward-compat alias

// ─── PLAN CONFIGURATION ────────────────────────────────────────────────────

// Checks quota per plan tier (monthly). -1 = unlimited.
const PLAN_CHECKS = {
  free: 5,
  starter: 50,
  growth: 200,
  scale: -1,    // unlimited
  custom: -1,   // unlimited + priority
  // Per-connection tiers: unlimited checks, capped by connection/seat count.
  // 'individual'/'team' are legacy aliases for 'starter'/'professional'.
  individual: -1,
  team: -1,
  professional: -1,
  business: -1,
  enterprise: -1,
  managed_services: -1
};

// Annual multiplier for legacy flat tiers (10 months price = 2 months free)
const ANNUAL_MULTIPLIER = 10;

// Named getPlanPricePaise for backwards-compat; now returns USD cents
function getPlanPricePaise(tier, billing = 'monthly') {
  const monthly = pricing.LEGACY_PRICES[tier];
  if (!monthly) return null;
  return billing === 'annual' ? monthly * ANNUAL_MULTIPLIER : monthly;
}

function getPlanChecks(tier) {
  const val = PLAN_CHECKS[tier];
  return (val === undefined) ? 0 : val;
}

// Returns true when the plan has unlimited checks (scale, custom)
function isUnlimitedPlan(tier) {
  return PLAN_CHECKS[tier] === -1;
}

// ─── PAYMENTS ──────────────────────────────────────────────────────────────

async function createPaymentRecord({ userId, razorpayOrderId, amountPaise, currency, planTier, paymentType, billingPeriod }) {
  const ins = await pool.query(
    `INSERT INTO payments
       (user_id, razorpay_order_id, amount_paise, currency, plan_tier, payment_type, billing_period, status)
     VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, 'created')
     RETURNING id INTO :out_id`,
    { p1: userId, p2: razorpayOrderId, p3: amountPaise, p4: currency || 'USD', p5: planTier,
      p6: paymentType, p7: billingPeriod || 'monthly', out_id: pool.outNumber() }
  );
  const result = await pool.query(`SELECT * FROM payments WHERE id = :p1`, { p1: ins.outBinds.out_id[0] });
  return result.rows[0];
}

// Atomic idempotency guard. `WHERE status != 'captured'` means exactly ONE caller
// (verify-payment vs the webhook, in either order; or a retried webhook) transitions the
// row to 'captured'. Returns { row, newlyCaptured }:
//   newlyCaptured=true  → THIS call flipped it → caller should provision (activatePlan) ONCE
//   newlyCaptured=false → already captured OR order not found → caller must NOT re-provision
// The post-SELECT still returns the row (or null if the order truly doesn't exist), so a
// caller can tell "already processed" (row && !newlyCaptured) from "not found" (!row).
// On the already-captured path the UPDATE is a no-op, so the winning capture's
// razorpay_payment_id/signature are preserved (a duplicate never overwrites them).
async function markPaymentCaptured({ razorpayOrderId, razorpayPaymentId, razorpaySignature }) {
  const upd = await pool.query(
    `UPDATE payments
     SET razorpay_payment_id = :p1,
         razorpay_signature  = :p2,
         status              = 'captured',
         updated_at          = CURRENT_TIMESTAMP
     WHERE razorpay_order_id = :p3 AND status != 'captured'`,
    { p1: razorpayPaymentId, p2: razorpaySignature, p3: razorpayOrderId }
  );
  const result = await pool.query(`SELECT * FROM payments WHERE razorpay_order_id = :p1`, { p1: razorpayOrderId });
  return { row: result.rows[0] || null, newlyCaptured: upd.rowCount > 0 };
}

async function markPaymentFailed({ razorpayOrderId }) {
  await pool.query(
    `UPDATE payments SET status = 'failed', updated_at = CURRENT_TIMESTAMP WHERE razorpay_order_id = :p1`,
    { p1: razorpayOrderId }
  );
}

async function getPaymentByOrderId(razorpayOrderId) {
  const result = await pool.query(
    `SELECT * FROM payments WHERE razorpay_order_id = :p1`,
    { p1: razorpayOrderId }
  );
  return result.rows[0] || null;
}

async function getPaymentsByUserId(userId, limit = 20) {
  const result = await pool.query(
    `SELECT * FROM payments WHERE user_id = :p1 ORDER BY created_at DESC FETCH FIRST :p2 ROWS ONLY`,
    { p1: userId, p2: limit }
  );
  return result.rows;
}

// ─── SUBSCRIPTIONS ─────────────────────────────────────────────────────────

async function createSubscriptionRecord({ userId, razorpaySubscriptionId, razorpayPlanId, planTier, billingPeriod }) {
  await pool.query(
    `MERGE INTO subscriptions t
     USING (SELECT :p2 AS razorpay_subscription_id FROM DUAL) s
        ON (t.razorpay_subscription_id = s.razorpay_subscription_id)
     WHEN MATCHED THEN UPDATE SET status = 'created', updated_at = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (user_id, razorpay_subscription_id, razorpay_plan_id, plan_tier, billing_period, status)
       VALUES (:p1, :p2, :p3, :p4, :p5, 'created')`,
    { p1: userId, p2: razorpaySubscriptionId, p3: razorpayPlanId, p4: planTier, p5: billingPeriod || 'monthly' }
  );
  const result = await pool.query(
    `SELECT * FROM subscriptions WHERE razorpay_subscription_id = :p1`, { p1: razorpaySubscriptionId });
  return result.rows[0];
}

async function updateSubscriptionStatus({ razorpaySubscriptionId, status, periodStart, periodEnd, cancelledAt }) {
  const upd = await pool.query(
    `UPDATE subscriptions
     SET status               = :p1,
         current_period_start = COALESCE(:p2, current_period_start),
         current_period_end   = COALESCE(:p3, current_period_end),
         cancelled_at         = COALESCE(:p4, cancelled_at),
         updated_at           = CURRENT_TIMESTAMP
     WHERE razorpay_subscription_id = :p5`,
    { p1: status, p2: pool.tstz(periodStart || null), p3: pool.tstz(periodEnd || null),
      p4: pool.tstz(cancelledAt || null), p5: razorpaySubscriptionId }
  );
  if (!upd.rowCount) return null;
  const result = await pool.query(
    `SELECT * FROM subscriptions WHERE razorpay_subscription_id = :p1`, { p1: razorpaySubscriptionId });
  return result.rows[0] || null;
}

async function getSubscriptionByRazorpayId(razorpaySubscriptionId) {
  const result = await pool.query(
    `SELECT * FROM subscriptions WHERE razorpay_subscription_id = :p1`,
    { p1: razorpaySubscriptionId }
  );
  return result.rows[0] || null;
}

async function getActiveSubscriptionByUserId(userId) {
  const result = await pool.query(
    `SELECT * FROM subscriptions
     WHERE user_id = :p1 AND status IN ('authenticated','active','pending')
     ORDER BY created_at DESC FETCH FIRST 1 ROWS ONLY`,
    { p1: userId }
  );
  return result.rows[0] || null;
}

// ─── USER CREDITS ──────────────────────────────────────────────────────────

async function getUserCredits(userId) {
  const result = await pool.query(
    `SELECT * FROM user_credits WHERE user_id = :p1`,
    { p1: userId }
  );
  return result.rows[0] || null;
}

// UNLIMITED_SENTINEL: stored in DB for unlimited plans (scale/custom) so that
// checks_remaining > 0 comparisons work without special-casing everywhere.
const UNLIMITED_SENTINEL = 999999999;

async function upsertUserCredits({ userId, planTier, checksTotal, subscriptionId, periodStart, periodEnd }) {
  // Use sentinel for unlimited plans so DB comparisons remain simple
  const effectiveTotal = checksTotal === -1 ? UNLIMITED_SENTINEL : checksTotal;
  // Reset checks_remaining to full quota on new payment
  await pool.query(
    `MERGE INTO user_credits t
     USING (SELECT :p1 AS user_id, :p2 AS plan_tier, :p3 AS checks, :p4 AS subscription_id,
                   :p5 AS period_start, :p6 AS period_end FROM DUAL) s
        ON (t.user_id = s.user_id)
     WHEN MATCHED THEN UPDATE SET
       plan_tier        = s.plan_tier,
       checks_remaining = s.checks,
       checks_total     = s.checks,
       subscription_id  = COALESCE(s.subscription_id, t.subscription_id),
       period_start     = COALESCE(s.period_start, t.period_start),
       period_end       = COALESCE(s.period_end, t.period_end),
       updated_at       = CURRENT_TIMESTAMP
     WHEN NOT MATCHED THEN
       INSERT (user_id, plan_tier, checks_remaining, checks_total, subscription_id, period_start, period_end)
       VALUES (s.user_id, s.plan_tier, s.checks, s.checks, s.subscription_id, s.period_start, s.period_end)`,
    { p1: userId, p2: planTier, p3: effectiveTotal, p4: pool.num(subscriptionId || null),
      p5: pool.tstz(periodStart || null), p6: pool.tstz(periodEnd || null) }
  );
  const result = await pool.query(`SELECT * FROM user_credits WHERE user_id = :p1`, { p1: userId });
  return result.rows[0];
}

// Reset a user's credits to zero. Used by the admin test harness to ensure
// each test run starts from a clean baseline so the 0 → planChecks transition
// is validated on every run. Not used in production payment flows.
async function resetUserCredits(userId) {
  await pool.query(
    `DELETE FROM user_credits WHERE user_id = :p1`,
    { p1: userId }
  );
}

// ─── USER LOOKUP (for post-payment notifications) ──────────────────────────

// Returns { id, email, name } for a user — used by welcome email sender
// to get the email address after a webhook-triggered plan unlock.
async function getUserById(userId) {
  const result = await pool.query(
    `SELECT id, email, name FROM users WHERE id = :p1`,
    { p1: userId }
  );
  return result.rows[0] || null;
}

// ─── RECONCILIATION LOG ─────────────────────────────────────────────────────

// Append-only error log for payment events that could not be fully processed.
// Called when plan unlock or welcome email fails — provides a record for
// manual review without blocking the webhook 200 response.
async function logReconciliation({ eventType, razorpayPaymentId, razorpayOrderId, userId, userEmail, planTier, failureStage, errorMessage, metadata }) {
  try {
    await pool.query(
      `INSERT INTO payment_reconciliation
         (event_type, razorpay_payment_id, razorpay_order_id, user_id, user_email, plan_tier, failure_stage, error_message, metadata)
       VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9)`,
      {
        p1: eventType,
        p2: razorpayPaymentId || null,
        p3: razorpayOrderId   || null,
        p4: userId            || null,
        p5: userEmail         || null,
        p6: planTier          || null,
        p7: failureStage,
        p8: errorMessage      || null,
        p9: JSON.stringify(metadata || {})
      }
    );
  } catch (logErr) {
    // Last-resort: if even the reconciliation log fails, emit to stdout
    // so it reaches Render logs / Datadog
    console.error('[payments] CRITICAL: reconciliation log write failed:', logErr.message);
  }
}

// ─── SCHEDULED PLAN CHANGES ────────────────────────────────────────────────

// Set a scheduled downgrade/cancel on the user_credits row.
// Does NOT immediately change plan_tier — takes effect on scheduled_plan_change_date.
async function scheduleDowngrade({ userId, targetTier, effectiveDate, cancelAtPeriodEnd }) {
  const upd = await pool.query(
    `UPDATE user_credits
     SET scheduled_plan_change      = :p1,
         scheduled_plan_change_date = :p2,
         cancel_at_period_end       = :p3,
         updated_at                 = CURRENT_TIMESTAMP
     WHERE user_id = :p4`,
    { p1: targetTier || null, p2: pool.tstz(effectiveDate || null),
      p3: pool.bool(cancelAtPeriodEnd || false), p4: userId }
  );
  if (!upd.rowCount) return null;
  const result = await pool.query(`SELECT * FROM user_credits WHERE user_id = :p1`, { p1: userId });
  return result.rows[0] || null;
}

// Clear a pending scheduled change (e.g., user re-upgrades before period end).
async function clearScheduledChange(userId) {
  const upd = await pool.query(
    `UPDATE user_credits
     SET scheduled_plan_change      = NULL,
         scheduled_plan_change_date = NULL,
         cancel_at_period_end       = FALSE,
         updated_at                 = CURRENT_TIMESTAMP
     WHERE user_id = :p1`,
    { p1: userId }
  );
  if (!upd.rowCount) return null;
  const result = await pool.query(`SELECT * FROM user_credits WHERE user_id = :p1`, { p1: userId });
  return result.rows[0] || null;
}

// Apply any due scheduled changes for users whose change_date has passed.
// Called lazily on login / plan-state fetch; also safe to run on a cron.
async function applyDueScheduledChanges() {
  const upd = await pool.query(
    `UPDATE user_credits
     SET plan_tier                  = CASE
           WHEN cancel_at_period_end = TRUE OR scheduled_plan_change = 'free' THEN 'free'
           WHEN scheduled_plan_change IS NOT NULL THEN scheduled_plan_change
           ELSE plan_tier
         END,
         checks_remaining           = CASE
           WHEN cancel_at_period_end = TRUE OR scheduled_plan_change = 'free' THEN 5
           ELSE checks_remaining
         END,
         checks_total               = CASE
           WHEN cancel_at_period_end = TRUE OR scheduled_plan_change = 'free' THEN 5
           ELSE checks_total
         END,
         scheduled_plan_change      = NULL,
         scheduled_plan_change_date = NULL,
         cancel_at_period_end       = FALSE,
         updated_at                 = CURRENT_TIMESTAMP
     WHERE (scheduled_plan_change IS NOT NULL OR cancel_at_period_end = TRUE)
       AND scheduled_plan_change_date <= CURRENT_TIMESTAMP
     RETURNING user_id, plan_tier INTO :out_uid, :out_ptier`,
    { out_uid: pool.outNumber(), out_ptier: pool.outString(4000) }
  );
  const uids = (upd.outBinds && upd.outBinds.out_uid) || [];
  const tiers = (upd.outBinds && upd.outBinds.out_ptier) || [];
  return uids.map((user_id, i) => ({ user_id, plan_tier: tiers[i] }));
}

async function decrementUserCredit(userId) {
  // Returns updated credits row; caller checks checks_remaining before running a health check.
  // Unlimited plans use UNLIMITED_SENTINEL — we never decrement them past 0.
  const upd = await pool.query(
    `UPDATE user_credits
     SET checks_remaining = CASE
           WHEN checks_remaining >= :p2 THEN checks_remaining  -- unlimited sentinel: don't decrement
           ELSE GREATEST(checks_remaining - 1, 0)
         END,
         updated_at = CURRENT_TIMESTAMP
     WHERE user_id = :p1 AND checks_remaining > 0`,
    { p1: userId, p2: UNLIMITED_SENTINEL }
  );
  if (!upd.rowCount) return null;
  const result = await pool.query(`SELECT * FROM user_credits WHERE user_id = :p1`, { p1: userId });
  return result.rows[0] || null;
}

module.exports = {
  getPlanPricePaise,
  calcPerConnAmount,
  getPricingSummary,
  getPlanChecks,
  isUnlimitedPlan,
  PLAN_PRICES_USD: pricing.LEGACY_PRICES,  // backward-compat alias
  CONN_PRICES_USD,
  PLAN_CHECKS,
  UNLIMITED_SENTINEL,
  // payments
  createPaymentRecord,
  markPaymentCaptured,
  markPaymentFailed,
  getPaymentByOrderId,
  getPaymentsByUserId,
  // subscriptions
  createSubscriptionRecord,
  updateSubscriptionStatus,
  getSubscriptionByRazorpayId,
  getActiveSubscriptionByUserId,
  // user credits
  getUserCredits,
  upsertUserCredits,
  decrementUserCredit,
  resetUserCredits,
  // scheduled plan changes
  scheduleDowngrade,
  clearScheduledChange,
  applyDueScheduledChanges,
  // post-payment helpers
  getUserById,
  logReconciliation
};
