import { prisma } from "@/lib/prisma";
import {
  branches as mockBranches,
  brands as mockBrands,
  galleryImages as mockGalleryImages,
  inventory as mockInventory,
  offers as mockOffers,
  partCategories as mockPartCategories,
  parts as mockParts,
  scooters as mockScooters,
  testimonials as mockTestimonials,
  type Branch,
  type Brand,
  type GalleryImage,
  type InventoryItem,
  type Offer,
  type PartCategory,
  type PartItem,
  type Scooter,
  type ScooterSpec,
  type Testimonial,
} from "@/lib/mock-data";
import { defaultSiteProfile, type SiteProfile } from "@/lib/site-profile";

type ScooterRow = Awaited<ReturnType<typeof fetchDbScooters>>[number];
type BranchAvailability = InventoryItem & {
  branch: Branch;
};

export type PublicGalleryImage = Omit<GalleryImage, "category"> & {
  category: string;
};

const hasDatabaseUrl = Boolean(process.env.DATABASE_URL);

async function homepageBannerTableExists() {
  try {
    const rows = await prisma.$queryRaw<Array<{ exists: boolean }>>`
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_name = 'HomepageBanner'
      ) AS "exists"
    `;
    return Boolean(rows[0]?.exists);
  } catch {
    return false;
  }
}

async function cmsSettingTableExists() {
  try {
    const rows = await prisma.$queryRaw<Array<{ exists: boolean }>>`
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_name = 'CmsSetting'
      ) AS "exists"
    `;
    return Boolean(rows[0]?.exists);
  } catch {
    return false;
  }
}

function mapInventoryStatus(status: string): InventoryItem["status"] {
  if (
    status === "IN_STOCK" ||
    status === "LIMITED_STOCK" ||
    status === "OUT_OF_STOCK" ||
    status === "TEST_RIDE_ONLY" ||
    status === "COMING_SOON"
  ) {
    return status;
  }

  return "COMING_SOON";
}

async function fetchDbScooters() {
  return prisma.scooter.findMany({
    include: {
      images: {
        orderBy: {
          sortOrder: "asc",
        },
      },
      asset3d: true,
      inventory: true,
    },
    orderBy: {
      name: "asc",
    },
  });
}

function mapScooter(row: ScooterRow): Scooter {
  const specs = Array.isArray(row.specs) ? (row.specs as ScooterSpec[]) : [];

  return {
    id: row.id,
    slug: row.slug,
    brandId: row.brandId,
    name: row.name,
    shortDescription: row.shortDescription,
    longDescription: row.longDescription,
    price: row.price,
    offerPrice: row.offerPrice,
    rangeKm: row.rangeKm,
    topSpeedKmph: row.topSpeedKmph,
    chargingTime: row.chargingTime,
    battery: row.battery,
    motorPower: row.motorPower,
    warranty: row.warranty,
    colors: row.colors,
    heroImage: row.heroImage,
    thumbnailImage: row.thumbnailImage,
    posterImage: row.posterImage,
    galleryImages: row.images.map((image) => image.url),
    model3dGlbUrl: row.asset3d?.glbUrl ?? undefined,
    model3dUsdzUrl: row.asset3d?.usdzUrl ?? undefined,
    arEnabled: row.arEnabled,
    isFeatured: row.isFeatured,
    specs,
  };
}

function mapInventoryItem(item: ScooterRow["inventory"][number]): InventoryItem {
  return {
    id: item.id,
    scooterId: item.scooterId,
    branchId: item.branchId,
    status: mapInventoryStatus(item.status),
    quantity: item.quantity,
    testRideAvailable: item.testRideAvailable,
  };
}

function mapBranch(branch: {
  id: string;
  name: string;
  slug: string;
  type: string;
  city: string;
  district: string;
  phone: string;
  whatsapp: string;
  openingHours: string;
  mapLink: string;
  branchImage?: string;
}): Branch {
  return {
    id: branch.id,
    name: branch.name,
    slug: branch.slug,
    type: branch.type === "Main Branch" ? "Main Branch" : "Branch",
    city: branch.city,
    district: branch.district,
    phone: branch.phone,
    whatsapp: branch.whatsapp,
    openingHours: branch.openingHours,
    mapLink: branch.mapLink,
    branchImage: branch.branchImage ?? "",
  };
}

function mapTestimonial(testimonial: {
  id: string;
  customerName: string;
  location: string;
  rating: number;
  comment: string;
  scooterName: string | null;
  image: string;
}): Testimonial {
  return {
    id: testimonial.id,
    customerName: testimonial.customerName,
    location: testimonial.location,
    rating: testimonial.rating,
    comment: testimonial.comment,
    scooterName: testimonial.scooterName ?? undefined,
    image: testimonial.image,
  };
}

function mapOffer(offer: {
  id: string;
  category: string;
  title: string;
  description: string;
  discountLabel: string;
  validUntil: Date;
  scooterId: string | null;
  branchId: string | null;
}): Offer {
  return {
    id: offer.id,
    category: offer.category,
    title: offer.title,
    description: offer.description,
    discountLabel: offer.discountLabel,
    validUntil: offer.validUntil.toISOString().slice(0, 10),
    scooterId: offer.scooterId ?? undefined,
    branchId: offer.branchId ?? undefined,
  };
}

function mapGalleryImage(image: {
  id: string;
  category: string;
  title: string;
  image: string;
  alt: string;
}): PublicGalleryImage {
  return {
    id: image.id,
    category: image.category,
    title: image.title,
    image: image.image,
    alt: image.alt,
  };
}

function partCategoryFromPart(part: PartItem): PartCategory {
  const existing = mockPartCategories.find((category) => category.name === part.category);
  const slug = part.category.toLowerCase().replace(/[^a-z0-9]+/g, "-").replace(/(^-|-$)/g, "");

  return (
    existing ?? {
      id: `parts-${slug}`,
      name: part.category,
      slug,
      description: `Genuine ${part.category.toLowerCase()} for compatible electric scooters.`,
      image: part.image,
    }
  );
}

export async function getScootersPageData(): Promise<{
  brands: Brand[];
  scooters: Scooter[];
  inventory: InventoryItem[];
}> {
  if (!hasDatabaseUrl) {
    return {
      brands: mockBrands,
      scooters: mockScooters,
      inventory: mockInventory,
    };
  }

  try {
    const [brands, scooterRows] = await Promise.all([
      prisma.brand.findMany({ orderBy: { name: "asc" } }),
      fetchDbScooters(),
    ]);

    return {
      brands,
      scooters: scooterRows.map(mapScooter),
      inventory: scooterRows.flatMap((scooter) => scooter.inventory.map(mapInventoryItem)),
    };
  } catch (error) {
    console.warn("Falling back to mock scooters data:", error);
    return {
      brands: mockBrands,
      scooters: mockScooters,
      inventory: mockInventory,
    };
  }
}

export async function getScooterDetailPageData(slug: string): Promise<{
  scooter: Scooter | null;
  brand?: Brand;
  availability: BranchAvailability[];
  testimonials: Testimonial[];
}> {
  if (!hasDatabaseUrl) {
    const scooter = mockScooters.find((item) => item.slug === slug) ?? null;
    return {
      scooter,
      brand: scooter ? mockBrands.find((item) => item.id === scooter.brandId) : undefined,
      availability: scooter
        ? mockInventory
            .filter((item) => item.scooterId === scooter.id)
            .map((item) => ({
              ...item,
              branch: mockBranches.find((branch) => branch.id === item.branchId) as Branch,
            }))
        : [],
      testimonials: mockTestimonials,
    };
  }

  try {
    const [scooterRow, testimonials] = await Promise.all([
      prisma.scooter.findUnique({
        where: { slug },
        include: {
          brand: true,
          images: { orderBy: { sortOrder: "asc" } },
          asset3d: true,
          inventory: {
            include: {
              branch: true,
            },
          },
        },
      }),
      prisma.testimonial.findMany({ orderBy: { createdAt: "desc" }, take: 6 }),
    ]);

    if (!scooterRow) {
      return {
        scooter: null,
        availability: [],
        testimonials: testimonials.map(mapTestimonial),
      };
    }

    return {
      scooter: mapScooter({
        ...scooterRow,
        inventory: scooterRow.inventory,
      }),
      brand: scooterRow.brand,
      availability: scooterRow.inventory.map((item) => ({
        ...mapInventoryItem(item),
        branch: mapBranch(item.branch),
      })),
      testimonials: testimonials.map(mapTestimonial),
    };
  } catch (error) {
    console.warn("Falling back to mock scooter detail data:", error);
    return getScooterDetailPageDataWithoutDb(slug);
  }
}

function getScooterDetailPageDataWithoutDb(slug: string) {
  const scooter = mockScooters.find((item) => item.slug === slug) ?? null;

  return {
    scooter,
    brand: scooter ? mockBrands.find((item) => item.id === scooter.brandId) : undefined,
    availability: scooter
      ? mockInventory
          .filter((item) => item.scooterId === scooter.id)
          .map((item) => ({
            ...item,
            branch: mockBranches.find((branch) => branch.id === item.branchId) as Branch,
          }))
      : [],
    testimonials: mockTestimonials,
  };
}

export async function getBrandsPageData(): Promise<{
  brands: Brand[];
  scooters: Scooter[];
  branches: Branch[];
}> {
  if (!hasDatabaseUrl) {
    return {
      brands: mockBrands,
      scooters: mockScooters,
      branches: mockBranches,
    };
  }

  try {
    const [brands, scooterRows, branches] = await Promise.all([
      prisma.brand.findMany({ orderBy: { name: "asc" } }),
      fetchDbScooters(),
      prisma.branch.findMany({ orderBy: { city: "asc" } }),
    ]);

    return {
      brands,
      scooters: scooterRows.map(mapScooter),
      branches: branches.map(mapBranch),
    };
  } catch (error) {
    console.warn("Falling back to mock brands data:", error);
    return {
      brands: mockBrands,
      scooters: mockScooters,
      branches: mockBranches,
    };
  }
}

export async function getPartsPageData(): Promise<{
  partCategories: PartCategory[];
  parts: PartItem[];
  branches: Branch[];
  scooters: Scooter[];
}> {
  if (!hasDatabaseUrl) {
    return {
      partCategories: mockPartCategories,
      parts: mockParts,
      branches: mockBranches,
      scooters: mockScooters,
    };
  }

  try {
    const [parts, branches, scooterRows] = await Promise.all([
      prisma.part.findMany({ orderBy: { name: "asc" } }),
      prisma.branch.findMany({ orderBy: { city: "asc" } }),
      fetchDbScooters(),
    ]);
    const categoryMap = new Map<string, PartCategory>();

    parts.forEach((part) => {
      categoryMap.set(part.category, partCategoryFromPart(part));
    });

    return {
      partCategories: Array.from(categoryMap.values()),
      parts,
      branches: branches.map(mapBranch),
      scooters: scooterRows.map(mapScooter),
    };
  } catch (error) {
    console.warn("Falling back to mock parts data:", error);
    return {
      partCategories: mockPartCategories,
      parts: mockParts,
      branches: mockBranches,
      scooters: mockScooters,
    };
  }
}

export async function getOffersPageData(): Promise<{
  brands: Brand[];
  scooters: Scooter[];
  offers: Offer[];
}> {
  if (!hasDatabaseUrl) {
    return {
      brands: mockBrands,
      scooters: mockScooters,
      offers: mockOffers,
    };
  }

  try {
    const [brands, scooterRows, offers] = await Promise.all([
      prisma.brand.findMany({ orderBy: { name: "asc" } }),
      fetchDbScooters(),
      prisma.offer.findMany({
        where: { active: true },
        orderBy: { validUntil: "desc" },
      }),
    ]);

    return {
      brands,
      scooters: scooterRows.map(mapScooter),
      offers: offers.map(mapOffer),
    };
  } catch (error) {
    console.warn("Falling back to mock offers data:", error);
    return {
      brands: mockBrands,
      scooters: mockScooters,
      offers: mockOffers,
    };
  }
}

export async function getGalleryPageData(): Promise<{
  galleryImages: PublicGalleryImage[];
}> {
  if (!hasDatabaseUrl) {
    return { galleryImages: mockGalleryImages };
  }

  try {
    const galleryImages = await prisma.galleryImage.findMany({
      orderBy: [{ featured: "desc" }, { createdAt: "desc" }],
    });

    return {
      galleryImages: galleryImages.map(mapGalleryImage),
    };
  } catch (error) {
    console.warn("Falling back to mock gallery data:", error);
    return { galleryImages: mockGalleryImages };
  }
}

export type NewsPopupData = {
  id: string;
  title: string;
  description: string;
  imageUrl?: string | null;
  ctaLabel?: string | null;
  ctaUrl?: string | null;
  showOnce: boolean;
};

export type HomepageBannerData = {
  id: string;
  imageUrl: string;
  altText: string;
  ctaUrl?: string | null;
  isActive: boolean;
};

export type CmsPageKey =
  | "scooters"
  | "brands"
  | "offers"
  | "about"
  | "parts"
  | "gallery"
  | "contact";

export type CmsImageControlKey =
  | "scooters_banner_product_image"
  | "brands_banner_product_image"
  | "brands_cta_product_image"
  | "offers_banner_product_image"
  | "about_banner_product_image"
  | "about_story_image"
  | "about_cta_image"
  | "parts_banner_product_image"
  | "parts_support_image"
  | "gallery_banner_product_image"
  | "contact_banner_product_image"
  | "contact_cta_image";

export type CmsContentPageKey =
  | "scooters"
  | "brands"
  | "offers"
  | "about"
  | "parts"
  | "gallery"
  | "contact";

export async function getActiveNewsPopup(): Promise<NewsPopupData | null> {
  if (!hasDatabaseUrl) {
    return null;
  }

  try {
    const now = new Date();
    const newsPopupModel = (prisma as any).newsPopup;
    const popup = newsPopupModel
      ? await newsPopupModel.findFirst({
          where: {
            isActive: true,
            OR: [{ startsAt: null }, { startsAt: { lte: now } }],
            AND: [{ OR: [{ endsAt: null }, { endsAt: { gte: now } }] }],
          },
          orderBy: [{ updatedAt: "desc" }],
        })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "NewsPopup"
            WHERE
              "isActive" = true
              AND ("startsAt" IS NULL OR "startsAt" <= ${now})
              AND ("endsAt" IS NULL OR "endsAt" >= ${now})
            ORDER BY "updatedAt" DESC
            LIMIT 1
          `
        )[0];

    if (!popup) return null;

    return {
      id: popup.id,
      title: popup.title,
      description: popup.description,
      imageUrl: popup.imageUrl,
      ctaLabel: popup.ctaLabel,
      ctaUrl: popup.ctaUrl,
      showOnce: popup.showOnce,
    };
  } catch (error) {
    console.warn("News popup fallback:", error);
    return null;
  }
}

export async function getActiveHomepageBanner(): Promise<HomepageBannerData | null> {
  if (!hasDatabaseUrl) {
    return null;
  }

  try {
    const homepageBannerModel = (prisma as any).homepageBanner;
    if (!homepageBannerModel) {
      const exists = await homepageBannerTableExists();
      if (!exists) return null;
    }

    const banner = homepageBannerModel
      ? await homepageBannerModel.findFirst({
          where: { isActive: true },
          orderBy: [{ updatedAt: "desc" }],
        })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "HomepageBanner"
            WHERE "isActive" = true
            ORDER BY "updatedAt" DESC
            LIMIT 1
          `
        )[0];

    if (!banner) return null;

    return {
      id: String(banner.id),
      imageUrl: String(banner.imageUrl),
      altText: String(banner.altText),
      ctaUrl: banner.ctaUrl ? String(banner.ctaUrl) : null,
      isActive: Boolean(banner.isActive),
    };
  } catch (error) {
    console.warn("Homepage banner fallback:", error);
    return null;
  }
}

export async function getCmsPageHeroImage(
  page: CmsPageKey,
  fallbackImage: string,
): Promise<string> {
  if (!hasDatabaseUrl) return fallbackImage;

  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return fallbackImage;
    }

    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key: `hero_image_${page}` } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = ${`hero_image_${page}`}
            LIMIT 1
          `
        )[0];

    const value = row?.value;
    const imageUrl =
      value && typeof value === "object" && typeof value.imageUrl === "string"
        ? value.imageUrl
        : null;

    return imageUrl || fallbackImage;
  } catch {
    return fallbackImage;
  }
}

export async function getCmsFinanceEnabled(): Promise<boolean> {
  if (!hasDatabaseUrl) return false;

  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return false;
    }

    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key: "finance_enabled" } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = 'finance_enabled'
            LIMIT 1
          `
        )[0];

    const value = row?.value;
    if (typeof value === "boolean") return value;
    if (value && typeof value === "object" && typeof value.enabled === "boolean") {
      return value.enabled;
    }
    return false;
  } catch {
    return false;
  }
}

export async function getCmsHomeBannerScooterId(): Promise<string | null> {
  if (!hasDatabaseUrl) return null;

  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return null;
    }

    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key: "home_banner_scooter_id" } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = 'home_banner_scooter_id'
            LIMIT 1
          `
        )[0];

    const value = row?.value;
    if (typeof value === "string") return value;
    if (value && typeof value === "object" && typeof value.scooterId === "string") {
      return value.scooterId;
    }
    return null;
  } catch {
    return null;
  }
}

export async function getCmsHomeBannerProductImage(): Promise<string | null> {
  if (!hasDatabaseUrl) return null;

  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return null;
    }

    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key: "home_banner_product_image" } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = 'home_banner_product_image'
            LIMIT 1
          `
        )[0];

    const value = row?.value;
    if (typeof value === "string") return value;
    if (value && typeof value === "object" && typeof value.imageUrl === "string") {
      return value.imageUrl;
    }
    return null;
  } catch {
    return null;
  }
}

export async function getCmsSiteProfile(): Promise<SiteProfile> {
  if (!hasDatabaseUrl) return defaultSiteProfile;
  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return defaultSiteProfile;
    }

    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key: "site_profile" } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = 'site_profile'
            LIMIT 1
          `
        )[0];

    const value = row?.value;
    if (!value || typeof value !== "object") return defaultSiteProfile;
    return { ...defaultSiteProfile, ...(value as Record<string, string>) };
  } catch {
    return defaultSiteProfile;
  }
}

export async function getCmsImageControl(
  key: CmsImageControlKey,
  fallbackImage: string,
): Promise<string> {
  if (!hasDatabaseUrl) return fallbackImage;
  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return fallbackImage;
    }

    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = ${key}
            LIMIT 1
          `
        )[0];

    const value = row?.value;
    const imageUrl =
      value && typeof value === "object" && typeof value.imageUrl === "string"
        ? value.imageUrl
        : null;
    return imageUrl || fallbackImage;
  } catch {
    return fallbackImage;
  }
}

async function getCmsSettingValue(key: string): Promise<Record<string, any> | null> {
  if (!hasDatabaseUrl) return null;
  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (!cmsSettingModel) {
      const exists = await cmsSettingTableExists();
      if (!exists) return null;
    }
    const row = cmsSettingModel
      ? await cmsSettingModel.findUnique({ where: { key } })
      : (
          await prisma.$queryRaw<Array<Record<string, any>>>`
            SELECT *
            FROM "CmsSetting"
            WHERE "key" = ${key}
            LIMIT 1
          `
        )[0];
    const value = row?.value;
    return value && typeof value === "object" ? (value as Record<string, any>) : null;
  } catch {
    return null;
  }
}

export async function getCmsPageContent(
  page: CmsContentPageKey,
  defaults: Record<string, string>,
): Promise<Record<string, string>> {
  const value = await getCmsSettingValue(`page_content_${page}`);
  if (!value) return defaults;
  return {
    ...defaults,
    ...Object.fromEntries(
      Object.entries(value).filter((entry) => typeof entry[1] === "string"),
    ),
  } as Record<string, string>;
}

export async function getCmsPageSections(
  page: CmsContentPageKey,
  defaults: Record<string, boolean>,
): Promise<Record<string, boolean>> {
  const value = await getCmsSettingValue(`page_sections_${page}`);
  if (!value) return defaults;
  const merged = { ...defaults };
  Object.entries(defaults).forEach(([key]) => {
    if (typeof value[key] === "boolean") merged[key] = value[key];
  });
  return merged;
}