import prisma from "../db.server";

export interface HelplineConfigRecord {
  id: string;
  department: string;
  phoneNumber: string;
  email: string;
  hours: string;
  description: string;
  sortOrder: number;
  isActive: boolean;
  createdAt: string;
  updatedAt: string;
}

export interface CreateHelplineInput {
  department: string;
  phoneNumber: string;
  email: string;
  hours?: string;
  description?: string;
  sortOrder?: number;
  isActive?: boolean;
}

export interface UpdateHelplineInput {
  department?: string;
  phoneNumber?: string;
  email?: string;
  hours?: string;
  description?: string;
  sortOrder?: number;
  isActive?: boolean;
}

export const DEFAULT_HELPLINES: HelplineConfigRecord[] = [
  {
    id: "helpline_general",
    department: "Customer Support Helpline",
    phoneNumber: "+91 8000361445",
    email: "ashvin@digihorizon.in",
    hours: "24/7 Priority Support",
    description: "Customer helpline for order assistance, shipping queries, product inquiries, and store support.",
    sortOrder: 1,
    isActive: true,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  },
  {
    id: "helpline_tech",
    department: "Technical & Billing Desk",
    phoneNumber: "+1 (888) 435-7762",
    email: "billing@shoppilot.ai",
    hours: "Mon - Fri: 9:00 AM - 6:00 PM EST",
    description: "Dedicated escalation helpline for technical integrations, AI custom prompts, and billing management.",
    sortOrder: 2,
    isActive: true,
    createdAt: new Date().toISOString(),
    updatedAt: new Date().toISOString(),
  },
];

let memoryHelplines: HelplineConfigRecord[] = JSON.parse(JSON.stringify(DEFAULT_HELPLINES));

function getHelplineDelegate(): any {
  if (prisma) {
    if ((prisma as any).helplineConfig && typeof (prisma as any).helplineConfig.findMany === "function") {
      return (prisma as any).helplineConfig;
    }
    if ((prisma as any).helpline_configs && typeof (prisma as any).helpline_configs.findMany === "function") {
      return (prisma as any).helpline_configs;
    }
    if ((prisma as any).HelplineConfig && typeof (prisma as any).HelplineConfig.findMany === "function") {
      return (prisma as any).HelplineConfig;
    }
  }
  return null;
}

export async function ensureHelplineTableExists(): Promise<void> {
  try {
    if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS \`helpline_configs\` (
          \`id\` VARCHAR(64) NOT NULL PRIMARY KEY,
          \`department\` VARCHAR(128) NOT NULL,
          \`phoneNumber\` VARCHAR(64) NOT NULL,
          \`email\` VARCHAR(128) NOT NULL,
          \`hours\` VARCHAR(128) NOT NULL DEFAULT '24/7 Support',
          \`description\` TEXT 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)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
      `).catch(() => {});

      await prisma.$executeRawUnsafe(
        "UPDATE `helpline_configs` SET `email` = 'ashvin@digihorizon.in' WHERE `email` = 'support@shoppilot.ai'"
      ).catch(() => {});
      await prisma.$executeRawUnsafe(
        "UPDATE `helpline_configs` SET `phoneNumber` = '+91 8000361445' WHERE `phoneNumber` LIKE '%555-0199%'"
      ).catch(() => {});
    }

    const delegate = getHelplineDelegate();
    if (delegate && typeof delegate.updateMany === "function") {
      await delegate.updateMany({
        where: { email: "support@shoppilot.ai" },
        data: { email: "ashvin@digihorizon.in" },
      }).catch(() => {});
      await delegate.updateMany({
        where: { phoneNumber: { contains: "555-0199" } },
        data: { phoneNumber: "+91 8000361445" },
      }).catch(() => {});
    }
  } catch (e) {
    // Ignore in tests or SQLite environments
  }
}

function mapDbToHelplineRecord(row: any): HelplineConfigRecord {
  return {
    id: String(row.id),
    department: String(row.department || "General Support"),
    phoneNumber: String(row.phoneNumber || ""),
    email: String(row.email || ""),
    hours: String(row.hours || "24/7 Support"),
    description: String(row.description || ""),
    sortOrder: typeof row.sortOrder === "number" ? row.sortOrder : 0,
    isActive: Boolean(row.isActive === 1 || row.isActive === true),
    createdAt: row.createdAt ? new Date(row.createdAt).toISOString() : new Date().toISOString(),
    updatedAt: row.updatedAt ? new Date(row.updatedAt).toISOString() : new Date().toISOString(),
  };
}

export async function getAllHelplines(): Promise<HelplineConfigRecord[]> {
  try {
    const delegate = getHelplineDelegate();
    if (delegate) {
      const records = await delegate.findMany({
        orderBy: [{ sortOrder: "asc" }, { createdAt: "asc" }],
      });
      if (records && records.length > 0) {
        return records.map(mapDbToHelplineRecord);
      }
    }

    if (prisma && typeof prisma.$queryRawUnsafe === "function") {
      await ensureHelplineTableExists();
      const rawRows = await prisma.$queryRawUnsafe<any[]>(
        "SELECT * FROM `helpline_configs` ORDER BY `sortOrder` ASC, `createdAt` ASC"
      ).catch(() => []);

      if (rawRows && rawRows.length > 0) {
        return rawRows.map(mapDbToHelplineRecord);
      }
    }
  } catch (err) {
    console.warn("[helpline-config.server] Failed to query database for helplines, using memory fallback:", err);
  }

  // Fallback to in-memory store
  return [...memoryHelplines].sort((a, b) => a.sortOrder - b.sortOrder);
}

export async function getActiveHelplines(): Promise<HelplineConfigRecord[]> {
  const all = await getAllHelplines();
  return all.filter((h) => h.isActive === true);
}

export async function getHelplineById(id: string): Promise<HelplineConfigRecord | null> {
  const all = await getAllHelplines();
  return all.find((h) => h.id === id) || null;
}

export async function createHelpline(input: CreateHelplineInput): Promise<HelplineConfigRecord> {
  const newId = `hl_${Date.now()}_${Math.random().toString(36).substr(2, 6)}`;
  const now = new Date().toISOString();
  const record: HelplineConfigRecord = {
    id: newId,
    department: input.department.trim(),
    phoneNumber: input.phoneNumber.trim(),
    email: input.email.trim(),
    hours: (input.hours || "24/7 Support").trim(),
    description: (input.description || "").trim(),
    sortOrder: typeof input.sortOrder === "number" ? input.sortOrder : memoryHelplines.length + 1,
    isActive: input.isActive ?? true,
    createdAt: now,
    updatedAt: now,
  };

  try {
    const delegate = getHelplineDelegate();
    if (delegate) {
      const created = await delegate.create({
        data: {
          id: record.id,
          department: record.department,
          phoneNumber: record.phoneNumber,
          email: record.email,
          hours: record.hours,
          description: record.description,
          sortOrder: record.sortOrder,
          isActive: record.isActive,
        },
      });
      return mapDbToHelplineRecord(created);
    }

    if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      await ensureHelplineTableExists();
      await prisma.$executeRawUnsafe(
        "INSERT INTO `helpline_configs` (`id`, `department`, `phoneNumber`, `email`, `hours`, `description`, `sortOrder`, `isActive`, `createdAt`, `updatedAt`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(3), NOW(3))",
        record.id,
        record.department,
        record.phoneNumber,
        record.email,
        record.hours,
        record.description,
        record.sortOrder,
        record.isActive ? 1 : 0
      );
    }
  } catch (err) {
    console.warn("[helpline-config.server] DB insert failed, storing in memory:", err);
  }

  memoryHelplines.push(record);
  return record;
}

export async function updateHelpline(id: string, input: UpdateHelplineInput): Promise<HelplineConfigRecord | null> {
  const existing = await getHelplineById(id);
  if (!existing) {
    const fallback = memoryHelplines.find((h) => h.id === id);
    if (!fallback) return null;
  }

  const updated: HelplineConfigRecord = {
    ...(existing || memoryHelplines.find((h) => h.id === id)!),
    ...(input.department !== undefined ? { department: input.department.trim() } : {}),
    ...(input.phoneNumber !== undefined ? { phoneNumber: input.phoneNumber.trim() } : {}),
    ...(input.email !== undefined ? { email: input.email.trim() } : {}),
    ...(input.hours !== undefined ? { hours: input.hours.trim() } : {}),
    ...(input.description !== undefined ? { description: input.description.trim() } : {}),
    ...(input.sortOrder !== undefined ? { sortOrder: input.sortOrder } : {}),
    ...(input.isActive !== undefined ? { isActive: input.isActive } : {}),
    updatedAt: new Date().toISOString(),
  };

  try {
    const delegate = getHelplineDelegate();
    if (delegate) {
      const dbUpdated = await delegate.update({
        where: { id },
        data: {
          ...(input.department !== undefined ? { department: updated.department } : {}),
          ...(input.phoneNumber !== undefined ? { phoneNumber: updated.phoneNumber } : {}),
          ...(input.email !== undefined ? { email: updated.email } : {}),
          ...(input.hours !== undefined ? { hours: updated.hours } : {}),
          ...(input.description !== undefined ? { description: updated.description } : {}),
          ...(input.sortOrder !== undefined ? { sortOrder: updated.sortOrder } : {}),
          ...(input.isActive !== undefined ? { isActive: updated.isActive } : {}),
        },
      });
      return mapDbToHelplineRecord(dbUpdated);
    }

    if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      await ensureHelplineTableExists();
      await prisma.$executeRawUnsafe(
        "UPDATE `helpline_configs` SET `department` = ?, `phoneNumber` = ?, `email` = ?, `hours` = ?, `description` = ?, `sortOrder` = ?, `isActive` = ?, `updatedAt` = NOW(3) WHERE `id` = ?",
        updated.department,
        updated.phoneNumber,
        updated.email,
        updated.hours,
        updated.description,
        updated.sortOrder,
        updated.isActive ? 1 : 0,
        id
      );
    }
  } catch (err) {
    console.warn("[helpline-config.server] DB update failed, saving in memory:", err);
  }

  const idx = memoryHelplines.findIndex((h) => h.id === id);
  if (idx !== -1) {
    memoryHelplines[idx] = updated;
  } else {
    memoryHelplines.push(updated);
  }

  return updated;
}

export async function deleteHelpline(id: string): Promise<boolean> {
  try {
    const delegate = getHelplineDelegate();
    if (delegate) {
      await delegate.delete({ where: { id } });
    } else if (prisma && typeof prisma.$executeRawUnsafe === "function") {
      await ensureHelplineTableExists();
      await prisma.$executeRawUnsafe("DELETE FROM `helpline_configs` WHERE `id` = ?", id);
    }
  } catch (err) {
    console.warn("[helpline-config.server] DB delete failed:", err);
  }

  const prevLen = memoryHelplines.length;
  memoryHelplines = memoryHelplines.filter((h) => h.id !== id);
  return memoryHelplines.length < prevLen || true;
}

export async function toggleHelplineStatus(id: string, isActive: boolean): Promise<HelplineConfigRecord | null> {
  return await updateHelpline(id, { isActive });
}