import { prisma } from "@/lib/prisma";
import {
  branches,
  brands,
  galleryImages,
  inventory,
  offers,
  parts,
  scooters,
} from "@/lib/mock-data";
import { defaultSiteProfile } from "@/lib/site-profile";

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;
  }
}

export async function getAdminDashboardData() {
  if (!hasDatabaseUrl) {
    return {
      stats: {
        products: scooters.length,
        brands: brands.length,
        testRides: 0,
        leads: 0,
      },
      recentBookings: [],
      recentLeads: [],
      inventory,
      branches,
      assetSummary: {
        published: scooters.filter((scooter) => scooter.model3dGlbUrl).length,
        pending: scooters.filter((scooter) => !scooter.model3dGlbUrl).length,
      },
    };
  }

  try {
    const [products, brandCount, testRides, leads, recentBookings, recentLeads, dbInventory, dbBranches, assets] =
      await Promise.all([
        prisma.scooter.count(),
        prisma.brand.count(),
        prisma.testRideBooking.count(),
        prisma.lead.count(),
        prisma.testRideBooking.findMany({
          orderBy: { createdAt: "desc" },
          take: 5,
          include: { branch: true, scooter: true },
        }),
        prisma.lead.findMany({ orderBy: { createdAt: "desc" }, take: 5 }),
        prisma.inventory.findMany({ include: { scooter: true, branch: true } }),
        prisma.branch.findMany({ orderBy: { city: "asc" } }),
        prisma.scooterAsset3D.findMany(),
      ]);

    return {
      stats: { products, brands: brandCount, testRides, leads },
      recentBookings,
      recentLeads,
      inventory: dbInventory,
      branches: dbBranches,
      assetSummary: {
        published: assets.filter((asset) => asset.status === "PUBLISHED").length,
        pending: assets.filter((asset) => asset.status !== "PUBLISHED").length,
      },
    };
  } catch (error) {
    console.warn("Admin dashboard fallback:", error);
    return getAdminDashboardDataWithoutDb();
  }
}

function getAdminDashboardDataWithoutDb() {
  return {
    stats: {
      products: scooters.length,
      brands: brands.length,
      testRides: 0,
      leads: 0,
    },
    recentBookings: [],
    recentLeads: [],
    inventory,
    branches,
    assetSummary: {
      published: scooters.filter((scooter) => scooter.model3dGlbUrl).length,
      pending: scooters.filter((scooter) => !scooter.model3dGlbUrl).length,
    },
  };
}

export async function getAdminProductsData() {
  if (!hasDatabaseUrl) {
    return { scooters, brands, inventory };
  }

  try {
    const [dbScooters, dbBrands, dbInventory] = await Promise.all([
      prisma.scooter.findMany({
        include: {
          brand: true,
          asset3d: true,
          inventory: true,
          images: { orderBy: { sortOrder: "asc" } },
        },
        orderBy: { name: "asc" },
      }),
      prisma.brand.findMany({ orderBy: { name: "asc" } }),
      prisma.inventory.findMany(),
    ]);
    return { scooters: dbScooters, brands: dbBrands, inventory: dbInventory };
  } catch (error) {
    console.warn("Admin products fallback:", error);
    return { scooters, brands, inventory };
  }
}

export async function getAdminReferenceData() {
  if (!hasDatabaseUrl) {
    return { brands, scooters, branches };
  }

  try {
    const [dbBrands, dbScooters, dbBranches] = await Promise.all([
      prisma.brand.findMany({ orderBy: { name: "asc" } }),
      prisma.scooter.findMany({ orderBy: { name: "asc" } }),
      prisma.branch.findMany({ orderBy: { city: "asc" } }),
    ]);

    return { brands: dbBrands, scooters: dbScooters, branches: dbBranches };
  } catch (error) {
    console.warn("Admin reference fallback:", error);
    return { brands, scooters, branches };
  }
}

export async function getAdminBrandsData() {
  if (!hasDatabaseUrl) {
    return brands.map((brand) => ({
      ...brand,
      _count: { scooters: scooters.filter((scooter) => scooter.brandId === brand.id).length },
    }));
  }

  try {
    return prisma.brand.findMany({
      orderBy: { name: "asc" },
      include: { _count: { select: { scooters: true } } },
    });
  } catch (error) {
    console.warn("Admin brands fallback:", error);
    return brands.map((brand) => ({
      ...brand,
      _count: { scooters: scooters.filter((scooter) => scooter.brandId === brand.id).length },
    }));
  }
}

export async function getAdminBranchesData() {
  if (!hasDatabaseUrl) {
    return branches.map((branch) => ({
      ...branch,
      branchImage: "",
      _count: {
        inventory: inventory.filter((item) => item.branchId === branch.id).length,
        offers: offers.filter((item) => item.branchId === branch.id).length,
        bookings: 0,
        leads: 0,
      },
    }));
  }

  try {
    return prisma.branch.findMany({
      orderBy: { city: "asc" },
      include: {
        _count: {
          select: { inventory: true, offers: true, bookings: true, leads: true },
        },
      },
    });
  } catch (error) {
    console.warn("Admin branches fallback:", error);
    return branches.map((branch) => ({
      ...branch,
      branchImage: "",
      _count: {
        inventory: inventory.filter((item) => item.branchId === branch.id).length,
        offers: offers.filter((item) => item.branchId === branch.id).length,
        bookings: 0,
        leads: 0,
      },
    }));
  }
}

export async function getAdminPartsData() {
  if (!hasDatabaseUrl) {
    return parts;
  }

  try {
    return prisma.part.findMany({ orderBy: { name: "asc" } });
  } catch (error) {
    console.warn("Admin parts fallback:", error);
    return parts;
  }
}

export async function getAdminOffersData() {
  if (!hasDatabaseUrl) {
    return offers;
  }

  try {
    return prisma.offer.findMany({
      orderBy: { validUntil: "desc" },
      include: { scooter: true, branch: true, brand: true },
    });
  } catch (error) {
    console.warn("Admin offers fallback:", error);
    return offers;
  }
}

export async function getAdminGalleryData() {
  if (!hasDatabaseUrl) {
    return galleryImages;
  }

  try {
    return prisma.galleryImage.findMany({
      orderBy: { createdAt: "desc" },
      include: { branch: true, scooter: true },
    });
  } catch (error) {
    console.warn("Admin gallery fallback:", error);
    return galleryImages;
  }
}

export async function getAdminAssetsData() {
  if (!hasDatabaseUrl) {
    return scooters.map((scooter) => ({
      id: scooter.id,
      scooterId: scooter.id,
      scooter,
      glbUrl: scooter.model3dGlbUrl,
      usdzUrl: scooter.model3dUsdzUrl,
      status: scooter.model3dGlbUrl ? "PUBLISHED" : "PENDING",
      updatedAt: new Date(),
    }));
  }

  try {
    return prisma.scooterAsset3D.findMany({
      include: { scooter: { include: { brand: true } } },
      orderBy: { updatedAt: "desc" },
    });
  } catch (error) {
    console.warn("Admin assets fallback:", error);
    return scooters.map((scooter) => ({
      id: scooter.id,
      scooterId: scooter.id,
      scooter,
      glbUrl: scooter.model3dGlbUrl,
      usdzUrl: scooter.model3dUsdzUrl,
      status: scooter.model3dGlbUrl ? "PUBLISHED" : "PENDING",
      updatedAt: new Date(),
    }));
  }
}

export async function getAdminBookingsData() {
  if (!hasDatabaseUrl) {
    return [];
  }

  try {
    return prisma.testRideBooking.findMany({
      include: { branch: true, scooter: true },
      orderBy: { createdAt: "desc" },
    });
  } catch (error) {
    console.warn("Admin bookings fallback:", error);
    return [];
  }
}

export async function getAdminLeadsData() {
  if (!hasDatabaseUrl) {
    return [];
  }

  try {
    return prisma.lead.findMany({
      include: { branch: true, scooter: true },
      orderBy: { createdAt: "desc" },
    });
  } catch (error) {
    console.warn("Admin leads fallback:", error);
    return [];
  }
}

export async function getAdminNewsPopupsData() {
  if (!hasDatabaseUrl) {
    return [];
  }

  try {
    const newsPopupModel = (prisma as any).newsPopup;
    if (newsPopupModel) {
      return newsPopupModel.findMany({
        orderBy: [{ isActive: "desc" }, { updatedAt: "desc" }],
      });
    }

    const rows = await prisma.$queryRaw<Array<Record<string, unknown>>>`
      SELECT *
      FROM "NewsPopup"
      ORDER BY "isActive" DESC, "updatedAt" DESC
    `;
    return rows;
  } catch (error) {
    console.warn("Admin news popup fallback:", error);
    return [];
  }
}

export async function getAdminHomepageBannerData() {
  if (!hasDatabaseUrl) {
    return null;
  }

  try {
    const homepageBannerModel = (prisma as any).homepageBanner;
    if (homepageBannerModel) {
      return homepageBannerModel.findFirst({
        orderBy: [{ updatedAt: "desc" }],
      });
    }

    const exists = await homepageBannerTableExists();
    if (!exists) return null;

    const rows = await prisma.$queryRaw<Array<Record<string, unknown>>>`
      SELECT *
      FROM "HomepageBanner"
      ORDER BY "updatedAt" DESC
      LIMIT 1
    `;
    return rows[0] ?? null;
  } catch (error) {
    console.warn("Admin homepage banner fallback:", error);
    return null;
  }
}

export async function getAdminCmsData() {
  const pages = ["scooters", "brands", "offers", "about", "parts", "gallery", "contact"] as const;
  const defaultHeroImages: Record<(typeof pages)[number], string> = {
    scooters: "/images/backgrounds/hero-bg-general.png",
    brands: "/images/backgrounds/hero-bg-general.png",
    offers: "/images/backgrounds/hero-bg-offers.png",
    about: "/images/backgrounds/hero-bg-general.png",
    parts: "/images/backgrounds/hero-bg-general.png",
    gallery: "/images/backgrounds/hero-bg-gallery.png",
    contact: "/images/backgrounds/hero-bg-contact.png",
  };
  const defaultPageImageControls = {
    scooters_banner_product_image: "/images/scooters/hero-scooter.png",
    brands_banner_product_image: "/images/scooters/hero-scooter-v2.png",
    brands_cta_product_image: "/images/scooters/hero-scooter-v2.png",
    offers_banner_product_image: "/images/scooters/hero-scooter-v2.png",
    about_banner_product_image: "/images/scooters/hero-scooter-v2.png",
    about_story_image: "/images/backgrounds/hero-bg-general.png",
    about_cta_image: "/images/scooters/hero-scooter-v2.png",
    parts_banner_product_image: "/images/scooters/hero-scooter-v2.png",
    parts_support_image: "/images/backgrounds/hero-bg-general.png",
    gallery_banner_product_image: "/images/backgrounds/hero-bg-gallery.png",
    contact_banner_product_image: "/images/scooters/hero-scooter-v2.png",
    contact_cta_image: "/images/scooters/hero-scooter-v2.png",
  } as const;
  const defaultPageContents = {
    scooters: { heroTitle: "Explore Electric Scooters", heroSubtitle: "", heroDescription: "Discover the perfect electric scooter for your daily ride. Smart. Stylish. Sustainable. Ride better, ride electric." },
    brands: { heroTitle: "Brands We Offer", heroSubtitle: "", heroDescription: "At Bashista Auto, we partner with trusted names in electric mobility to bring you the best in performance, reliability, and innovation. Explore our top electric scooter brands and find the perfect ride for your journey." },
    offers: { heroTitle: "Season of Savings, Future of Mobility", heroSubtitle: "Offers & Discounts", heroDescription: "Exclusive offers on electric scooters. Limited period only." },
    about: { heroTitle: "About Bashista Auto", heroSubtitle: "Empowering Nepal's Mobility with Trust, Innovation & Care", heroDescription: "We are more than a showroom. We are your mobility partner for life. From premium electric scooters to unmatched after-sales support, we drive a cleaner, smarter, and better tomorrow." },
    parts: { heroTitle: "Genuine Parts & Accessories", heroSubtitle: "Parts & Accessories", heroDescription: "Original spare parts. Perfect performance. Complete after-sales support you can trust." },
    gallery: { heroTitle: "Explore Bashista Auto Gallery", heroSubtitle: "Gallery", heroDescription: "A glimpse of our showroom, premium electric scooters, happy customers, service care and our growing family." },
    contact: { heroTitle: "Contact Bashista Auto", heroSubtitle: "Contact Us", heroDescription: "We're here to help with questions about our electric scooters, services, showrooms and support." },
  } as const;
  const defaultPageSections = {
    scooters: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
    brands: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
    offers: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
    about: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
    parts: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
    gallery: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
    contact: { section1: true, section2: true, section3: true, section4: true, section5: true, section6: true },
  } as const;

  if (!hasDatabaseUrl) {
    return {
      pages,
      defaultHeroImages,
      heroImages: {},
      financeEnabled: false,
      siteProfile: defaultSiteProfile,
      homeBannerScooterId: null,
      homeBannerProductImage: null,
      pageImageControls: defaultPageImageControls,
      pageContents: defaultPageContents,
      pageSections: defaultPageSections,
    };
  }

  try {
    const cmsSettingModel = (prisma as any).cmsSetting;
    if (cmsSettingModel) {
      const settings = await cmsSettingModel.findMany({
        where: {
          OR: [
            { key: { startsWith: "hero_image_" } },
            { key: "finance_enabled" },
            { key: "site_profile" },
            { key: "home_banner_scooter_id" },
            { key: "home_banner_product_image" },
            { key: { in: Object.keys(defaultPageImageControls) } },
            { key: { startsWith: "page_content_" } },
            { key: { startsWith: "page_sections_" } },
          ],
        },
      });

      const heroImages: Record<string, string> = {};
      let financeEnabled = false;

      settings.forEach((setting: any) => {
        if (setting.key.startsWith("hero_image_")) {
          const page = String(setting.key).replace("hero_image_", "");
          const value = setting.value;
          const imageUrl =
            value && typeof value === "object" && typeof value.imageUrl === "string"
              ? value.imageUrl
              : "";
          if (imageUrl) heroImages[page] = imageUrl;
        }
        if (setting.key === "finance_enabled") {
          const value = setting.value;
          financeEnabled =
            typeof value === "boolean"
              ? value
              : Boolean(value && typeof value === "object" && (value as any).enabled === true);
        }
      });

      const profileSetting = settings.find((item: any) => item.key === "site_profile");
      const homeBannerScooterSetting = settings.find((item: any) => item.key === "home_banner_scooter_id");
      const homeBannerProductImageSetting = settings.find((item: any) => item.key === "home_banner_product_image");
      const siteProfile =
        profileSetting?.value && typeof profileSetting.value === "object"
          ? { ...defaultSiteProfile, ...(profileSetting.value as Record<string, string>) }
          : defaultSiteProfile;
      const homeBannerScooterId =
        homeBannerScooterSetting?.value &&
        typeof homeBannerScooterSetting.value === "object" &&
        typeof (homeBannerScooterSetting.value as any).scooterId === "string"
          ? (homeBannerScooterSetting.value as any).scooterId
          : null;
      const homeBannerProductImage =
        homeBannerProductImageSetting?.value &&
        typeof homeBannerProductImageSetting.value === "object" &&
        typeof (homeBannerProductImageSetting.value as any).imageUrl === "string"
          ? (homeBannerProductImageSetting.value as any).imageUrl
          : null;
      const pageImageControls = { ...defaultPageImageControls } as Record<string, string>;
      settings.forEach((setting: any) => {
        if (!Object.prototype.hasOwnProperty.call(defaultPageImageControls, setting.key)) return;
        const value = setting.value;
        const imageUrl =
          value && typeof value === "object" && typeof value.imageUrl === "string"
            ? value.imageUrl
            : "";
        if (imageUrl) pageImageControls[setting.key] = imageUrl;
      });
      const pageContents = { ...defaultPageContents } as Record<string, any>;
      const pageSections = { ...defaultPageSections } as Record<string, any>;
      settings.forEach((setting: any) => {
        const key = String(setting.key ?? "");
        if (key.startsWith("page_content_")) {
          const page = key.replace("page_content_", "");
          if (pageContents[page] && setting.value && typeof setting.value === "object") {
            pageContents[page] = { ...pageContents[page], ...(setting.value as Record<string, string>) };
          }
        }
        if (key.startsWith("page_sections_")) {
          const page = key.replace("page_sections_", "");
          if (pageSections[page] && setting.value && typeof setting.value === "object") {
            pageSections[page] = { ...pageSections[page], ...(setting.value as Record<string, boolean>) };
          }
        }
      });

      return {
        pages,
        defaultHeroImages,
        heroImages,
        financeEnabled,
        siteProfile,
        homeBannerScooterId,
        homeBannerProductImage,
        pageImageControls,
        pageContents,
        pageSections,
      };
    }

    const exists = await cmsSettingTableExists();
    if (!exists) {
      return {
        pages,
        defaultHeroImages,
        heroImages: {},
        financeEnabled: false,
        siteProfile: defaultSiteProfile,
        homeBannerScooterId: null,
        homeBannerProductImage: null,
        pageImageControls: defaultPageImageControls,
        pageContents: defaultPageContents,
        pageSections: defaultPageSections,
      };
    }

    const settings = await prisma.$queryRaw<Array<Record<string, any>>>`
      SELECT *
      FROM "CmsSetting"
      WHERE "key" LIKE 'hero\\_image\\_%' ESCAPE '\\'
         OR "key" = 'finance_enabled'
         OR "key" = 'site_profile'
         OR "key" = 'home_banner_scooter_id'
         OR "key" = 'home_banner_product_image'
         OR "key" = 'scooters_banner_product_image'
         OR "key" = 'brands_banner_product_image'
         OR "key" = 'brands_cta_product_image'
         OR "key" = 'offers_banner_product_image'
         OR "key" = 'about_banner_product_image'
         OR "key" = 'about_story_image'
         OR "key" = 'about_cta_image'
         OR "key" = 'parts_banner_product_image'
         OR "key" = 'parts_support_image'
         OR "key" = 'gallery_banner_product_image'
         OR "key" = 'contact_banner_product_image'
         OR "key" = 'contact_cta_image'
         OR "key" LIKE 'page_content_%'
         OR "key" LIKE 'page_sections_%'
    `;

    const heroImages: Record<string, string> = {};
    let financeEnabled = false;
    settings.forEach((setting) => {
      const key = String(setting.key ?? "");
      if (key.startsWith("hero_image_")) {
        const page = key.replace("hero_image_", "");
        const value = setting.value;
        const imageUrl =
          value && typeof value === "object" && typeof value.imageUrl === "string"
            ? value.imageUrl
            : "";
        if (imageUrl) heroImages[page] = imageUrl;
      } else if (key === "finance_enabled") {
        const value = setting.value;
        financeEnabled =
          typeof value === "boolean"
            ? value
            : Boolean(value && typeof value === "object" && value.enabled === true);
      }
    });

    const profileSetting = settings.find((item) => String(item.key) === "site_profile");
    const homeBannerScooterSetting = settings.find((item) => String(item.key) === "home_banner_scooter_id");
    const homeBannerProductImageSetting = settings.find((item) => String(item.key) === "home_banner_product_image");
    const siteProfile =
      profileSetting?.value && typeof profileSetting.value === "object"
        ? { ...defaultSiteProfile, ...(profileSetting.value as Record<string, string>) }
        : defaultSiteProfile;
    const homeBannerScooterId =
      homeBannerScooterSetting?.value &&
      typeof homeBannerScooterSetting.value === "object" &&
      typeof (homeBannerScooterSetting.value as any).scooterId === "string"
        ? (homeBannerScooterSetting.value as any).scooterId
        : null;
    const homeBannerProductImage =
      homeBannerProductImageSetting?.value &&
      typeof homeBannerProductImageSetting.value === "object" &&
      typeof (homeBannerProductImageSetting.value as any).imageUrl === "string"
        ? (homeBannerProductImageSetting.value as any).imageUrl
        : null;
    const pageImageControls = { ...defaultPageImageControls } as Record<string, string>;
    settings.forEach((setting) => {
      const key = String(setting.key ?? "");
      if (!Object.prototype.hasOwnProperty.call(defaultPageImageControls, key)) return;
      const value = setting.value;
      const imageUrl =
        value && typeof value === "object" && typeof value.imageUrl === "string"
          ? value.imageUrl
          : "";
      if (imageUrl) pageImageControls[key] = imageUrl;
    });
    const pageContents = { ...defaultPageContents } as Record<string, any>;
    const pageSections = { ...defaultPageSections } as Record<string, any>;
    settings.forEach((setting) => {
      const key = String(setting.key ?? "");
      if (key.startsWith("page_content_")) {
        const page = key.replace("page_content_", "");
        if (pageContents[page] && setting.value && typeof setting.value === "object") {
          pageContents[page] = { ...pageContents[page], ...(setting.value as Record<string, string>) };
        }
      }
      if (key.startsWith("page_sections_")) {
        const page = key.replace("page_sections_", "");
        if (pageSections[page] && setting.value && typeof setting.value === "object") {
          pageSections[page] = { ...pageSections[page], ...(setting.value as Record<string, boolean>) };
        }
      }
    });

    return {
      pages,
      defaultHeroImages,
      heroImages,
      financeEnabled,
      siteProfile,
      homeBannerScooterId,
      homeBannerProductImage,
      pageImageControls,
      pageContents,
      pageSections,
    };
  } catch {
    return {
      pages,
      defaultHeroImages,
      heroImages: {},
      financeEnabled: false,
      siteProfile: defaultSiteProfile,
      homeBannerScooterId: null,
      homeBannerProductImage: null,
      pageImageControls: defaultPageImageControls,
      pageContents: defaultPageContents,
      pageSections: defaultPageSections,
    };
  }
}