import prisma from "../db.server";
import { PLAN_CONFIGS, type PlanDetails, type FeatureKey, type BillingType, type BillingIntervalType } from "../lib/billing-plans";
import type { PlanTier } from "@prisma/client";

export interface PlanConfigRecord {
  id: string;
  planKey: string;
  displayName: string;
  price: number;
  billingType: BillingType;
  billingInterval: BillingIntervalType;
  aiUsageEnabled: boolean;
  aiMonthlyLimit: number | null;
  monthlyLimit: number;
  description: string;
  featureFlags: Record<FeatureKey, boolean>;
  features: string[];
  sortOrder: number;
  isActive: boolean;
  createdAt: string;
  updatedAt: string;
}

export interface CreatePlanInput {
  planKey: string;
  displayName: string;
  price: number;
  billingType?: BillingType;
  billingInterval?: BillingIntervalType;
  aiUsageEnabled?: boolean;
  aiMonthlyLimit?: number | null;
  monthlyLimit?: number;
  description: string;
  featureFlags?: Partial<Record<FeatureKey, boolean>>;
  features: string[];
  sortOrder?: number;
  isActive?: boolean;
}

export interface UpdatePlanInput {
  displayName?: string;
  price?: number;
  billingType?: BillingType;
  billingInterval?: BillingIntervalType;
  aiUsageEnabled?: boolean;
  aiMonthlyLimit?: number | null;
  monthlyLimit?: number;
  description?: string;
  featureFlags?: Partial<Record<FeatureKey, boolean>>;
  features?: string[];
  sortOrder?: number;
  isActive?: boolean;
}

const DEFAULT_FEATURE_FLAGS: Record<FeatureKey, boolean> = {
  "shopify.products": true,
  "shopify.cart": false,
  "shopify.checkout": false,
  "shopify.customer_auth": false,
  openai: false,
  "ai.chatbot": false,
  "ai.recommendations": false,
  "ai.support": false,
};

function getPlanDelegate(): any {
  if (prisma) {
    if ((prisma as any).planConfig && typeof (prisma as any).planConfig.findMany === "function") {
      return (prisma as any).planConfig;
    }
    if ((prisma as any).plan_configs && typeof (prisma as any).plan_configs.findMany === "function") {
      return (prisma as any).plan_configs;
    }
    if ((prisma as any).PlanConfig && typeof (prisma as any).PlanConfig.findMany === "function") {
      return (prisma as any).PlanConfig;
    }
  }
  return null;
}

export async function ensurePlanTableExists(): Promise<void> {
  try {
    if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS \`plan_configs\` (
          \`id\` VARCHAR(64) NOT NULL PRIMARY KEY,
          \`planKey\` VARCHAR(64) NOT NULL UNIQUE,
          \`displayName\` VARCHAR(128) NOT NULL,
          \`price\` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
          \`billingType\` VARCHAR(32) NOT NULL DEFAULT 'RECURRING',
          \`billingInterval\` VARCHAR(32) NOT NULL DEFAULT 'MONTHLY',
          \`aiUsageEnabled\` TINYINT(1) NOT NULL DEFAULT 0,
          \`aiMonthlyLimit\` INT NULL,
          \`monthlyLimit\` INT NOT NULL DEFAULT 0,
          \`description\` TEXT NOT NULL,
          \`featureFlags\` JSON NULL,
          \`features\` JSON NULL,
          \`sortOrder\` INT NOT NULL DEFAULT 0,
          \`isActive\` TINYINT(1) NOT NULL DEFAULT 1,
          \`createdAt\` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
          \`updatedAt\` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
      `);
    }
  } catch (e) {
    // Table may already exist or running with limited permissions
  }
}

function parseBooleanActive(val: any): boolean {
  if (val === true || val === 1 || val === "1" || val === "true" || val === "TRUE") return true;
  if (val === false || val === 0 || val === "0" || val === "false" || val === "FALSE" || val === null || val === undefined) return false;
  if (typeof Buffer !== "undefined" && Buffer.isBuffer(val)) {
    return val.length > 0 && val[0] !== 0;
  }
  return Boolean(val);
}

function formatPlanRecord(row: any): PlanConfigRecord {
  if (!row) {
    throw new Error("Cannot format empty plan record");
  }

  const price = typeof row.price === "object" && row.price !== null && "toNumber" in row.price
    ? row.price.toNumber()
    : parseFloat(String(row.price || 0));

  let featureFlags: Record<FeatureKey, boolean> = { ...DEFAULT_FEATURE_FLAGS };
  if (row.featureFlags) {
    if (typeof row.featureFlags === "string") {
      try {
        featureFlags = { ...featureFlags, ...JSON.parse(row.featureFlags) };
      } catch (e) {}
    } else if (typeof row.featureFlags === "object") {
      featureFlags = { ...featureFlags, ...(row.featureFlags as any) };
    }
  }

  let features: string[] = [];
  if (row.features) {
    if (typeof row.features === "string") {
      try {
        const parsed = JSON.parse(row.features);
        features = Array.isArray(parsed) ? parsed : [row.features];
      } catch (e) {
        features = row.features.split("\n").map((s: string) => s.trim()).filter(Boolean);
      }
    } else if (Array.isArray(row.features)) {
      features = row.features as string[];
    }
  }

  const aiMonthlyLimit = row.aiMonthlyLimit !== null && row.aiMonthlyLimit !== undefined
    ? Number(row.aiMonthlyLimit)
    : null;

  const monthlyLimit = row.monthlyLimit !== undefined
    ? Number(row.monthlyLimit)
    : (aiMonthlyLimit || 0);

  return {
    id: String(row.id || row.planKey),
    planKey: String(row.planKey || "").toUpperCase().trim(),
    displayName: row.displayName || row.planKey || "Subscription Plan",
    price,
    billingType: (row.billingType || (row.billingInterval === "ONE_TIME" ? "ONE_TIME" : "RECURRING")) as BillingType,
    billingInterval: (row.billingInterval || "MONTHLY") as BillingIntervalType,
    aiUsageEnabled: Boolean(row.aiUsageEnabled),
    aiMonthlyLimit,
    monthlyLimit,
    description: row.description || "",
    featureFlags,
    features,
    sortOrder: Number(row.sortOrder || 0),
    isActive: parseBooleanActive(row.isActive),
    createdAt: row.createdAt ? new Date(row.createdAt).toISOString() : new Date().toISOString(),
    updatedAt: row.updatedAt ? new Date(row.updatedAt).toISOString() : new Date().toISOString(),
  };
}

/**
 * Ensures initial default plans (FREE, STARTER, PRO) exist in the database and match current Shopify App Pricing.
 */
export async function seedDefaultPlansIfEmpty(): Promise<void> {
  try {
    await ensurePlanTableExists();
    const delegate = getPlanDelegate();

    // If plans already exist in the database, DO NOT OVERWRITE them!
    if (delegate && typeof delegate.count === "function") {
      const existingCount = await delegate.count().catch(() => 0);
      if (existingCount > 0) return;
    } else if (prisma && typeof prisma.$queryRawUnsafe === "function") {
      const countRes: any = await prisma.$queryRawUnsafe(`SELECT COUNT(*) as cnt FROM plan_configs`).catch(() => null);
      const cnt = countRes?.[0]?.cnt ? Number(countRes[0].cnt) : 0;
      if (cnt > 0) return;
    }

    const defaultPlans = [
      {
        id: "plan_free",
        planKey: "FREE",
        displayName: "Free",
        price: 0.0,
        billingType: "RECURRING" as BillingType,
        billingInterval: "MONTHLY" as BillingIntervalType,
        aiUsageEnabled: false,
        aiMonthlyLimit: null,
        monthlyLimit: 0,
        description: "Shopify API catalog integration & product search",
        featureFlags: PLAN_CONFIGS.FREE.featureFlags,
        features: [
          "Shopify API catalog integration",
          "Product search and queries",
          "Product details and specifications",
          "No paid subscription required",
        ],
        sortOrder: 1,
        isActive: true,
      },
      {
        id: "plan_starter",
        planKey: "STARTER",
        displayName: "Starter",
        price: 29.0,
        billingType: "RECURRING" as BillingType,
        billingInterval: "MONTHLY" as BillingIntervalType,
        aiUsageEnabled: false,
        aiMonthlyLimit: null,
        monthlyLimit: 0,
        description: "Shopify Storefront API, Cart Management & Checkout",
        featureFlags: PLAN_CONFIGS.STARTER.featureFlags,
        features: [
          "Everything in Free",
          "Shopify Storefront API",
          "Add to Cart & variant selection",
          "Cart management & updates",
          "Shopify Checkout creation",
          "Promo & discount code validation",
          "Live shipping rates calculation",
        ],
        sortOrder: 2,
        isActive: true,
      },
      {
        id: "plan_pro",
        planKey: "PRO",
        displayName: "Pro",
        price: 79.0,
        billingType: "RECURRING" as BillingType,
        billingInterval: "MONTHLY" as BillingIntervalType,
        aiUsageEnabled: true,
        aiMonthlyLimit: null,
        monthlyLimit: 0,
        description: "Full AI Sales Assistant, Customer Support & Knowledge Base",
        featureFlags: PLAN_CONFIGS.PRO.featureFlags,
        features: [
          "Everything in Starter",
          "AI sales assistant & AI-powered chatbot",
          "AI personalized product recommendations",
          "AI customer support & knowledge base",
          "OpenAI & Google Gemini integrations",
          "Store owner's own API keys (BYOK)",
          "Priority API throughput & analytics",
        ],
        sortOrder: 3,
        isActive: true,
      },
    ];

    if (delegate && typeof delegate.create === "function") {
      for (const plan of defaultPlans) {
        await delegate.create({
          data: {
            id: plan.id,
            planKey: plan.planKey,
            displayName: plan.displayName,
            price: plan.price,
            billingType: plan.billingType,
            billingInterval: plan.billingInterval,
            aiUsageEnabled: plan.aiUsageEnabled,
            aiMonthlyLimit: plan.aiMonthlyLimit,
            monthlyLimit: plan.monthlyLimit,
            description: plan.description,
            featureFlags: plan.featureFlags as any,
            features: plan.features as any,
            sortOrder: plan.sortOrder,
            isActive: plan.isActive,
          },
        }).catch(() => {});
      }
    } else if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      for (const plan of defaultPlans) {
        await prisma.$executeRawUnsafe(
          `INSERT IGNORE INTO plan_configs (id, planKey, displayName, price, billingType, billingInterval, aiUsageEnabled, aiMonthlyLimit, monthlyLimit, description, featureFlags, features, sortOrder, isActive, createdAt, updatedAt)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
          plan.id,
          plan.planKey,
          plan.displayName,
          plan.price,
          plan.billingType,
          plan.billingInterval,
          plan.aiUsageEnabled ? 1 : 0,
          plan.aiMonthlyLimit,
          plan.monthlyLimit,
          plan.description,
          JSON.stringify(plan.featureFlags),
          JSON.stringify(plan.features),
          plan.sortOrder,
          plan.isActive ? 1 : 0
        ).catch(() => {});
      }
    }
  } catch (err) {
    console.warn("[seedDefaultPlansIfEmpty] Warning during plan seeding:", err);
  }
}

/**
 * Returns all plans from the database (active and inactive).
 */
export async function getAllPlans(): Promise<PlanConfigRecord[]> {
  try {
    await seedDefaultPlansIfEmpty();
    const delegate = getPlanDelegate();

    if (delegate && typeof delegate.findMany === "function") {
      const rows = await delegate.findMany({
        orderBy: [{ sortOrder: "asc" }, { createdAt: "asc" }],
      });
      if (rows && rows.length > 0) {
        return rows.map(formatPlanRecord);
      }
    }

    if (prisma && typeof prisma.$queryRawUnsafe === "function") {
      const rows: any[] = await prisma.$queryRawUnsafe(`SELECT * FROM plan_configs ORDER BY sortOrder ASC, createdAt ASC`);
      if (rows && rows.length > 0) {
        return rows.map(formatPlanRecord);
      }
    }
  } catch (err) {
    console.warn("[getAllPlans] Database query failed, falling back to static config:", err);
  }

  return Object.values(PLAN_CONFIGS).map((cfg, idx) => ({
    id: `static_${cfg.name}`,
    planKey: cfg.name,
    displayName: cfg.displayName,
    price: cfg.price,
    billingType: cfg.billingType,
    billingInterval: cfg.billingInterval,
    aiUsageEnabled: cfg.aiUsageEnabled,
    aiMonthlyLimit: cfg.aiMonthlyLimit,
    monthlyLimit: cfg.monthlyLimit,
    description: cfg.description,
    featureFlags: cfg.featureFlags,
    features: cfg.features,
    sortOrder: idx + 1,
    isActive: true,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  }));
}

export async function getActivePlans(): Promise<PlanConfigRecord[]> {
  try {
    const all = await getAllPlans();
    const active = all.filter((p) => p.isActive === true);
    if (active.length > 0) {
      return active;
    }
  } catch (e) {
    console.warn("[getActivePlans] Error retrieving active plans, falling back to static config:", e);
  }

  return [
    {
      id: "plan_free",
      planKey: "FREE",
      displayName: "Free",
      price: 0.0,
      billingType: "RECURRING",
      billingInterval: "MONTHLY",
      aiUsageEnabled: false,
      aiMonthlyLimit: null,
      monthlyLimit: 0,
      description: "Shopify API catalog integration & product search",
      featureFlags: PLAN_CONFIGS.FREE.featureFlags,
      features: PLAN_CONFIGS.FREE.features,
      sortOrder: 1,
      isActive: true,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    },
    {
      id: "plan_starter",
      planKey: "STARTER",
      displayName: "Starter",
      price: 29.0,
      billingType: "RECURRING",
      billingInterval: "MONTHLY",
      aiUsageEnabled: false,
      aiMonthlyLimit: null,
      monthlyLimit: 0,
      description: "Full Commerce, Cart & Shopify Checkout",
      featureFlags: PLAN_CONFIGS.STARTER.featureFlags,
      features: PLAN_CONFIGS.STARTER.features,
      sortOrder: 2,
      isActive: true,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    },
    {
      id: "plan_pro",
      planKey: "PRO",
      displayName: "Pro",
      price: 79.0,
      billingType: "RECURRING",
      billingInterval: "MONTHLY",
      aiUsageEnabled: true,
      aiMonthlyLimit: null,
      monthlyLimit: 0,
      description: "AI Sales Assistant & BYOK (OpenAI / Gemini)",
      featureFlags: PLAN_CONFIGS.PRO.featureFlags,
      features: PLAN_CONFIGS.PRO.features,
      sortOrder: 3,
      isActive: true,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    },
  ];
}

/**
 * Resolves a dictionary mapping planKey -> PlanDetails for application-wide compatibility.
 */
export async function getPlansDictionary(): Promise<Record<string, PlanDetails>> {
  const allPlans = await getAllPlans();
  const dict: Record<string, PlanDetails> = { ...PLAN_CONFIGS };

  for (const p of allPlans) {
    dict[p.planKey] = {
      name: p.planKey as any,
      displayName: p.displayName,
      price: p.price,
      billingType: p.billingType,
      billingInterval: p.billingInterval,
      aiUsageEnabled: p.aiUsageEnabled,
      aiMonthlyLimit: p.aiMonthlyLimit,
      monthlyLimit: p.monthlyLimit,
      description: p.description,
      featureFlags: p.featureFlags,
      features: p.features,
    };
  }

  return dict;
}

/**
 * Fetches a single plan by planKey or ID.
 */
export async function getPlanByIdOrKey(idOrKey: string): Promise<PlanConfigRecord | null> {
  if (!idOrKey) return null;
  const cleanKey = String(idOrKey).trim();
  const upperKey = cleanKey.toUpperCase();
  const strippedKey = cleanKey.replace(/^(static|plan)_/i, "").toUpperCase();
  const prefixedPlanId = `plan_${strippedKey.toLowerCase()}`;

  const delegate = getPlanDelegate();
  if (delegate && typeof delegate.findFirst === "function") {
    try {
      const row = await delegate.findFirst({
        where: {
          OR: [
            { id: cleanKey },
            { id: prefixedPlanId },
            { planKey: cleanKey },
            { planKey: upperKey },
            { planKey: strippedKey },
          ],
        },
      });
      if (row) return formatPlanRecord(row);
    } catch (e) {
      console.warn("[getPlanByIdOrKey] Delegate query failed:", e);
    }
  }

  // Fallback to Raw SQL
  if (prisma && typeof prisma.$queryRawUnsafe === "function") {
    try {
      const rows: any[] = await prisma.$queryRawUnsafe(
        `SELECT * FROM plan_configs WHERE id = ? OR id = ? OR planKey = ? OR planKey = ? OR planKey = ? LIMIT 1`,
        cleanKey,
        prefixedPlanId,
        cleanKey,
        upperKey,
        strippedKey
      );
      if (rows && rows.length > 0) {
        return formatPlanRecord(rows[0]);
      }
    } catch (err) {
      console.warn("[getPlanByIdOrKey] Raw SQL query failed:", err);
    }
  }

  // Fallback to static plan configs
  const staticCfg = PLAN_CONFIGS[upperKey as keyof typeof PLAN_CONFIGS] || PLAN_CONFIGS[strippedKey as keyof typeof PLAN_CONFIGS];
  if (staticCfg) {
    return {
      id: `plan_${staticCfg.name.toLowerCase()}`,
      planKey: staticCfg.name,
      displayName: staticCfg.displayName,
      price: staticCfg.price,
      billingType: staticCfg.billingType,
      billingInterval: staticCfg.billingInterval,
      aiUsageEnabled: staticCfg.aiUsageEnabled,
      aiMonthlyLimit: staticCfg.aiMonthlyLimit,
      monthlyLimit: staticCfg.monthlyLimit,
      description: staticCfg.description,
      featureFlags: staticCfg.featureFlags,
      features: staticCfg.features,
      sortOrder: 1,
      isActive: true,
      createdAt: new Date().toISOString(),
      updatedAt: new Date().toISOString(),
    };
  }

  return null;
}

/**
 * Creates a new subscription plan in the database.
 */
export async function createPlan(input: CreatePlanInput): Promise<PlanConfigRecord> {
  const planKey = input.planKey.toUpperCase().trim().replace(/[^A-Z0-9_]/g, "_");
  if (!planKey) {
    throw new Error("Plan key is required and must be alphanumeric (e.g. STARTER, PRO, ENTERPRISE).");
  }

  const existing = await getPlanByIdOrKey(planKey);
  if (existing && !existing.id.startsWith("static_")) {
    throw new Error(`A plan with key '${planKey}' already exists. Please use a unique plan key.`);
  }

  const billingInterval = input.billingInterval || (input.billingType === "ONE_TIME" ? "ONE_TIME" : "MONTHLY");
  const billingType = input.billingType || (billingInterval === "ONE_TIME" ? "ONE_TIME" : "RECURRING");
  const price = Math.max(0, Number(input.price || 0));
  const aiUsageEnabled = Boolean(input.aiUsageEnabled);
  const aiMonthlyLimit = aiUsageEnabled ? (input.aiMonthlyLimit ? Number(input.aiMonthlyLimit) : 25000) : null;
  const monthlyLimit = input.monthlyLimit !== undefined ? Number(input.monthlyLimit) : (aiMonthlyLimit || 0);

  const featureFlags: Record<FeatureKey, boolean> = {
    ...DEFAULT_FEATURE_FLAGS,
    ...(input.featureFlags || {}),
    openai: aiUsageEnabled || Boolean(input.featureFlags?.openai),
    "ai.chatbot": aiUsageEnabled || Boolean(input.featureFlags?.["ai.chatbot"]),
    "ai.recommendations": aiUsageEnabled || Boolean(input.featureFlags?.["ai.recommendations"]),
    "ai.support": aiUsageEnabled || Boolean(input.featureFlags?.["ai.support"]),
  };

  const planId = `plan_${Date.now()}_${Math.random().toString(36).substring(2, 7)}`;
  const sortOrder = input.sortOrder !== undefined ? input.sortOrder : 99;
  const displayName = input.displayName.trim() || planKey;
  const description = input.description.trim();
  const features = input.features.map((f) => f.trim()).filter(Boolean);
  const isActive = input.isActive !== false;

  const delegate = getPlanDelegate();
  if (delegate && typeof delegate.create === "function") {
    try {
      const row = await delegate.create({
        data: {
          id: planId,
          planKey,
          displayName,
          price,
          billingType,
          billingInterval,
          aiUsageEnabled,
          aiMonthlyLimit,
          monthlyLimit,
          description,
          featureFlags: featureFlags as any,
          features: features as any,
          sortOrder,
          isActive,
        },
      });
      return formatPlanRecord(row);
    } catch (e) {
      console.warn("[createPlan] Delegate create failed, falling back to raw SQL:", e);
    }
  }

  // Fallback to raw SQL
  await ensurePlanTableExists();
  if (prisma && typeof prisma.$executeRawUnsafe === "function") {
    await prisma.$executeRawUnsafe(
      `INSERT INTO plan_configs (id, planKey, displayName, price, billingType, billingInterval, aiUsageEnabled, aiMonthlyLimit, monthlyLimit, description, featureFlags, features, sortOrder, isActive, createdAt, updatedAt)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      planId,
      planKey,
      displayName,
      price,
      billingType,
      billingInterval,
      aiUsageEnabled ? 1 : 0,
      aiMonthlyLimit,
      monthlyLimit,
      description,
      JSON.stringify(featureFlags),
      JSON.stringify(features),
      sortOrder,
      isActive ? 1 : 0
    );

    const created = await getPlanByIdOrKey(planId);
    if (created) return created;
  }

  return {
    id: planId,
    planKey,
    displayName,
    price,
    billingType,
    billingInterval,
    aiUsageEnabled,
    aiMonthlyLimit,
    monthlyLimit,
    description,
    featureFlags,
    features,
    sortOrder,
    isActive,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  };
}

/**
 * Updates an existing subscription plan in the database.
 */
export async function updatePlan(idOrKey: string, input: UpdatePlanInput): Promise<PlanConfigRecord> {
  const existing = await getPlanByIdOrKey(idOrKey);
  if (!existing) {
    throw new Error(`Plan '${idOrKey}' not found.`);
  }

  const updateData: any = {};
  if (input.displayName !== undefined) updateData.displayName = input.displayName.trim();
  if (input.price !== undefined) updateData.price = Math.max(0, Number(input.price));
  if (input.billingType !== undefined) updateData.billingType = input.billingType;
  if (input.billingInterval !== undefined) updateData.billingInterval = input.billingInterval;
  if (input.description !== undefined) updateData.description = input.description.trim();
  if (input.sortOrder !== undefined) updateData.sortOrder = Number(input.sortOrder);
  if (input.isActive !== undefined) updateData.isActive = Boolean(input.isActive);

  if (input.aiUsageEnabled !== undefined) {
    updateData.aiUsageEnabled = Boolean(input.aiUsageEnabled);
    if (!updateData.aiUsageEnabled) {
      updateData.aiMonthlyLimit = null;
      updateData.monthlyLimit = 0;
    } else if (input.aiMonthlyLimit !== undefined) {
      updateData.aiMonthlyLimit = input.aiMonthlyLimit ? Number(input.aiMonthlyLimit) : 25000;
      updateData.monthlyLimit = updateData.aiMonthlyLimit;
    }
  } else if (input.aiMonthlyLimit !== undefined) {
    updateData.aiMonthlyLimit = input.aiMonthlyLimit ? Number(input.aiMonthlyLimit) : null;
    updateData.monthlyLimit = updateData.aiMonthlyLimit || 0;
  }

  if (input.monthlyLimit !== undefined) {
    updateData.monthlyLimit = Number(input.monthlyLimit);
  }

  const mergedFlags: Record<FeatureKey, boolean> = { ...existing.featureFlags, ...(input.featureFlags || {}) };
  if (updateData.aiUsageEnabled !== undefined) {
    mergedFlags.openai = updateData.aiUsageEnabled;
    mergedFlags["ai.chatbot"] = updateData.aiUsageEnabled;
    mergedFlags["ai.recommendations"] = updateData.aiUsageEnabled;
    mergedFlags["ai.support"] = updateData.aiUsageEnabled;
  }
  updateData.featureFlags = mergedFlags as any;

  if (input.features) {
    updateData.features = input.features.map((f) => f.trim()).filter(Boolean) as any;
  }

  const delegate = getPlanDelegate();
  if (delegate && typeof delegate.upsert === "function") {
    try {
      const planId = existing.id.startsWith("static_") ? `plan_${existing.planKey.toLowerCase()}` : existing.id;
      const row = await delegate.upsert({
        where: { planKey: existing.planKey },
        update: updateData,
        create: {
          id: planId,
          planKey: existing.planKey,
          displayName: updateData.displayName || existing.displayName,
          price: updateData.price !== undefined ? updateData.price : existing.price,
          billingType: updateData.billingType || existing.billingType,
          billingInterval: updateData.billingInterval || existing.billingInterval,
          aiUsageEnabled: updateData.aiUsageEnabled !== undefined ? updateData.aiUsageEnabled : existing.aiUsageEnabled,
          aiMonthlyLimit: updateData.aiMonthlyLimit !== undefined ? updateData.aiMonthlyLimit : existing.aiMonthlyLimit,
          monthlyLimit: updateData.monthlyLimit !== undefined ? updateData.monthlyLimit : existing.monthlyLimit,
          description: updateData.description || existing.description,
          featureFlags: (updateData.featureFlags || existing.featureFlags) as any,
          features: (updateData.features || existing.features) as any,
          sortOrder: updateData.sortOrder !== undefined ? updateData.sortOrder : existing.sortOrder,
          isActive: updateData.isActive !== undefined ? updateData.isActive : existing.isActive,
        },
      });
      return formatPlanRecord(row);
    } catch (e) {
      console.warn("[updatePlan] Delegate upsert failed, trying raw SQL:", e);
    }
  } else if (delegate && typeof delegate.update === "function") {
    try {
      const row = await delegate.update({
        where: { id: existing.id },
        data: updateData,
      });
      return formatPlanRecord(row);
    } catch (e) {
      console.warn("[updatePlan] Delegate update failed, trying raw SQL:", e);
    }
  }

  // Fallback to Raw SQL Update / Upsert
  try {
    await ensurePlanTableExists();
    const displayName = updateData.displayName !== undefined ? updateData.displayName : existing.displayName;
    const price = updateData.price !== undefined ? updateData.price : existing.price;
    const billingType = updateData.billingType !== undefined ? updateData.billingType : existing.billingType;
    const billingInterval = updateData.billingInterval !== undefined ? updateData.billingInterval : existing.billingInterval;
    const aiUsageEnabled = updateData.aiUsageEnabled !== undefined ? (updateData.aiUsageEnabled ? 1 : 0) : (existing.aiUsageEnabled ? 1 : 0);
    const aiMonthlyLimit = updateData.aiMonthlyLimit !== undefined ? updateData.aiMonthlyLimit : existing.aiMonthlyLimit;
    const monthlyLimit = updateData.monthlyLimit !== undefined ? updateData.monthlyLimit : existing.monthlyLimit;
    const description = updateData.description !== undefined ? updateData.description : existing.description;
    const featureFlagsJson = JSON.stringify(updateData.featureFlags || existing.featureFlags);
    const featuresJson = JSON.stringify(updateData.features || existing.features);
    const sortOrder = updateData.sortOrder !== undefined ? updateData.sortOrder : existing.sortOrder;
    const isActive = updateData.isActive !== undefined ? (updateData.isActive ? 1 : 0) : (existing.isActive ? 1 : 0);
    const planId = existing.id.startsWith("static_") ? `plan_${existing.planKey.toLowerCase()}` : existing.id;

    if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      // First attempt direct UPDATE on matching id or planKey
      await prisma.$executeRawUnsafe(
        `UPDATE plan_configs SET 
          displayName = ?, 
          price = ?, 
          billingType = ?, 
          billingInterval = ?, 
          aiUsageEnabled = ?, 
          aiMonthlyLimit = ?, 
          monthlyLimit = ?, 
          description = ?, 
          featureFlags = ?, 
          features = ?, 
          sortOrder = ?, 
          isActive = ?, 
          updatedAt = NOW()
        WHERE id = ? OR planKey = ?`,
        displayName,
        price,
        billingType,
        billingInterval,
        aiUsageEnabled,
        aiMonthlyLimit,
        monthlyLimit,
        description,
        featureFlagsJson,
        featuresJson,
        sortOrder,
        isActive,
        existing.id,
        existing.planKey
      ).catch(() => {});

      // Also ensure the row exists via INSERT ... ON DUPLICATE KEY UPDATE
      await prisma.$executeRawUnsafe(
        `INSERT INTO plan_configs (id, planKey, displayName, price, billingType, billingInterval, aiUsageEnabled, aiMonthlyLimit, monthlyLimit, description, featureFlags, features, sortOrder, isActive, createdAt, updatedAt)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
         ON DUPLICATE KEY UPDATE 
          displayName = VALUES(displayName), 
          price = VALUES(price), 
          billingType = VALUES(billingType), 
          billingInterval = VALUES(billingInterval), 
          aiUsageEnabled = VALUES(aiUsageEnabled), 
          aiMonthlyLimit = VALUES(aiMonthlyLimit), 
          monthlyLimit = VALUES(monthlyLimit), 
          description = VALUES(description), 
          featureFlags = VALUES(featureFlags), 
          features = VALUES(features), 
          sortOrder = VALUES(sortOrder), 
          isActive = VALUES(isActive),
          updatedAt = NOW()`,
        planId,
        existing.planKey,
        displayName,
        price,
        billingType,
        billingInterval,
        aiUsageEnabled,
        aiMonthlyLimit,
        monthlyLimit,
        description,
        featureFlagsJson,
        featuresJson,
        sortOrder,
        isActive
      ).catch(() => {});

      const updated = await getPlanByIdOrKey(existing.planKey) || await getPlanByIdOrKey(planId);
      if (updated) return updated;
    }
  } catch (err) {
    console.error("[updatePlan] Raw SQL update failed:", err);
  }

  return {
    ...existing,
    ...updateData,
    updatedAt: new Date().toISOString(),
  };
}

/**
 * Deletes a subscription plan from the database.
 */
export async function deletePlan(idOrKey: string): Promise<{ success: boolean; planKey: string }> {
  const existing = await getPlanByIdOrKey(idOrKey);
  if (!existing) {
    throw new Error(`Plan '${idOrKey}' not found.`);
  }

  const activePlans = await getActivePlans();
  if (activePlans.length <= 1 && existing.isActive) {
    throw new Error("Cannot delete the only remaining active plan. At least one active plan must be maintained.");
  }

  const delegate = getPlanDelegate();
  if (delegate && typeof delegate.delete === "function") {
    try {
      await delegate.delete({
        where: { id: existing.id },
      });
      return { success: true, planKey: existing.planKey };
    } catch (e) {
      console.warn("[deletePlan] Delegate delete failed, trying raw SQL:", e);
    }
  }

  if (prisma && typeof prisma.$executeRawUnsafe === "function") {
    await prisma.$executeRawUnsafe(`DELETE FROM plan_configs WHERE id = ? OR planKey = ?`, existing.id, existing.planKey);
  }

  return { success: true, planKey: existing.planKey };
}