import { NextRequest, NextResponse } from 'next/server';
import { db } from '@/db/client';
import { AdminStoreItem } from '@/lib/saas-admin/demoData';

export const dynamic = 'force-dynamic';

export async function GET(req: NextRequest) {
  try {
    const { searchParams } = new URL(req.url);
    const search = (searchParams.get('search') || '').toLowerCase().trim();
    const statusFilter = searchParams.get('status') || 'ALL'; // 'ALL' | 'installed' | 'uninstalled'
    const planFilter = searchParams.get('plan') || 'ALL'; // 'ALL' | 'basic' | 'growth' | 'accelerate'
    const countryFilter = searchParams.get('country') || 'ALL';
    const dateRangeFilter = searchParams.get('dateRange') || 'ALL'; // '7d' | '30d' | '90d' | '1y' | 'ALL'
    const sortBy = searchParams.get('sortBy') || 'installed_at';
    const sortOrder = searchParams.get('sortOrder') || 'desc';
    const page = Math.max(1, parseInt(searchParams.get('page') || '1'));
    const pageSize = Math.max(1, parseInt(searchParams.get('pageSize') || '10'));
    const format = searchParams.get('format') || 'json';

    // 1. Fetch live shops directly from MySQL database
    const [allShops, dbPlans] = await Promise.all([
      db.getAllShops(),
      db.getPlans(),
    ]);

    const liveStores: AdminStoreItem[] = await Promise.all(
      allShops.map(async (shop) => {
        let metaConn = null;
        let pixelsCount = 0;
        let productsCount = 0;
        let eventsCount = 0;

        try { metaConn = await db.getMetaConnectionByShopId(shop.id); } catch {}
        try { const p = await db.getPixelsByShopId(shop.id); pixelsCount = p.length; } catch {}
        try { const pr = await db.getProductsByShopId(shop.id); productsCount = pr.length; } catch {}
        try { const ev = await db.getCapiEvents(shop.id, 100); eventsCount = ev.length; } catch {}

        const planObj = dbPlans.find((p) => p.id.toLowerCase() === (shop.plan || 'basic').toLowerCase());
        const planPrice = planObj ? planObj.price : shop.plan === 'accelerate' ? 99 : shop.plan === 'growth' ? 13 : 0;
        const subStatus = shop.subscription_status || (planPrice > 0 ? 'active' : 'free');
        const isPaid = (shop.status === 'installed' && subStatus === 'active' && planPrice > 0);
        const monthlyRevenue = isPaid ? (shop.monthly_revenue || planPrice) : 0;

        return {
          id: shop.id,
          shopify_domain: shop.shopify_domain,
          shopify_shop_id: shop.shopify_shop_id || `gid://shopify/Shop/${shop.id.replace('shop_', '')}`,
          shop_name: shop.shop_name || shop.shopify_domain.replace('.myshopify.com', ''),
          owner_email: shop.owner_email || `admin@${shop.shopify_domain.replace('.myshopify.com', '')}.com`,
          owner_name: shop.owner_name || 'Store Owner',
          country: shop.country || 'United States',
          country_code: (shop.country && shop.country.length === 2) ? shop.country : 'US',
          currency: shop.currency || 'USD',
          status: (shop.status === 'uninstalled' ? 'uninstalled' : 'installed') as 'installed' | 'uninstalled',
          plan: (shop.plan || 'growth') as any,
          plan_name: planObj ? planObj.name : `${(shop.plan || 'growth').toUpperCase()} Plan`,
          plan_price: planPrice,
          billing_cycle: shop.billing_cycle || 'monthly',
          subscription_status: subStatus as any,
          monthly_revenue: monthlyRevenue,
          installed_at: shop.installed_at || shop.created_at || new Date().toISOString(),
          uninstalled_at: shop.uninstalled_at || null,
          last_active_at: shop.last_active_at || shop.updated_at || shop.installed_at || new Date().toISOString(),
          is_demo: false,
          meta: {
            connected: Boolean(metaConn),
            business_name: metaConn?.business_name || null,
            pixel_name: metaConn?.pixel_name || null,
            pixel_id: metaConn?.pixel_id || null,
            catalog_name: metaConn?.catalog_name || null,
          },
          stats: {
            productsCount,
            pixelsCount,
            eventsCount,
            lastSyncTime: 'Recently active',
            syncHealth: productsCount > 0 ? 'healthy' : 'synced',
          },
          permissions: shop.scopes || ['read_products', 'write_products', 'read_orders'],
          billingHistory: isPaid ? [
            {
              id: `bill_${shop.id}_latest`,
              date: shop.installed_at || new Date().toISOString(),
              amount: monthlyRevenue,
              currency: shop.currency || 'USD',
              status: 'verified',
              plan: planObj?.name || 'Growth',
              chargeId: shop.subscription_id || `sub_${shop.id}`,
            }
          ] : [],
          activityHistory: [
            {
              id: `act_${shop.id}_1`,
              date: shop.installed_at || new Date().toISOString(),
              action: 'app_installed',
              description: `Store installed on ${shop.shopify_domain}`,
              actor: 'merchant',
            }
          ],
        };
      })
    );

    // Apply Search Filter (Store Name, Shopify Domain, Owner Email)
    let filtered = liveStores.filter((s) => {
      if (search) {
        const matchesName = s.shop_name.toLowerCase().includes(search);
        const matchesDomain = s.shopify_domain.toLowerCase().includes(search);
        const matchesEmail = s.owner_email.toLowerCase().includes(search);
        const matchesShopId = s.shopify_shop_id.toLowerCase().includes(search);
        if (!matchesName && !matchesDomain && !matchesEmail && !matchesShopId) return false;
      }

      // Status filter
      if (statusFilter !== 'ALL' && s.status.toLowerCase() !== statusFilter.toLowerCase()) {
        return false;
      }

      // Plan filter
      if (planFilter !== 'ALL' && s.plan.toLowerCase() !== planFilter.toLowerCase()) {
        return false;
      }

      // Country filter
      if (countryFilter !== 'ALL' && s.country_code.toLowerCase() !== countryFilter.toLowerCase() && s.country.toLowerCase() !== countryFilter.toLowerCase()) {
        return false;
      }

      // Date Range Filter
      if (dateRangeFilter !== 'ALL') {
        const installMs = new Date(s.installed_at).getTime();
        const nowMs = Date.now();
        if (dateRangeFilter === '7d' && nowMs - installMs > 7 * 24 * 60 * 60 * 1000) return false;
        if (dateRangeFilter === '30d' && nowMs - installMs > 30 * 24 * 60 * 60 * 1000) return false;
        if (dateRangeFilter === '90d' && nowMs - installMs > 90 * 24 * 60 * 60 * 1000) return false;
        if (dateRangeFilter === '1y' && nowMs - installMs > 365 * 24 * 60 * 60 * 1000) return false;
      }

      return true;
    });

    // Apply Sorting
    filtered.sort((a: any, b: any) => {
      let aVal = a[sortBy];
      let bVal = b[sortBy];

      if (sortBy === 'installed_at' || sortBy === 'last_active_at') {
        aVal = new Date(aVal).getTime() || 0;
        bVal = new Date(bVal).getTime() || 0;
      } else if (sortBy === 'monthly_revenue') {
        aVal = parseFloat(aVal) || 0;
        bVal = parseFloat(bVal) || 0;
      } else if (typeof aVal === 'string') {
        aVal = aVal.toLowerCase();
        bVal = (bVal || '').toLowerCase();
      }

      if (aVal < bVal) return sortOrder === 'asc' ? -1 : 1;
      if (aVal > bVal) return sortOrder === 'asc' ? 1 : -1;
      return 0;
    });

    // Handle CSV Export
    if (format === 'csv') {
      const headers = [
        'Store ID',
        'Store Name',
        'Shopify Domain',
        'Owner Email',
        'Country',
        'Status',
        'Subscription Plan',
        'Subscription Status',
        'Billing Cycle',
        'Monthly Revenue (USD)',
        'Installed Date',
        'Last Active Date',
      ];

      const rows = filtered.map((s) => [
        `"${s.id}"`,
        `"${s.shop_name.replace(/"/g, '""')}"`,
        `"${s.shopify_domain}"`,
        `"${s.owner_email}"`,
        `"${s.country}"`,
        `"${s.status}"`,
        `"${s.plan_name}"`,
        `"${s.subscription_status}"`,
        `"${s.billing_cycle}"`,
        `"${s.monthly_revenue.toFixed(2)}"`,
        `"${s.installed_at}"`,
        `"${s.last_active_at}"`,
      ]);

      const csvContent = [headers.join(','), ...rows.map((r) => r.join(','))].join('\n');

      return new NextResponse(csvContent, {
        status: 200,
        headers: {
          'Content-Type': 'text/csv; charset=utf-8',
          'Content-Disposition': `attachment; filename="shopify_stores_export_${new Date().toISOString().slice(0, 10)}.csv"`,
        },
      });
    }

    // Pagination
    const total = filtered.length;
    const totalPages = Math.ceil(total / pageSize) || 1;
    const startIndex = (page - 1) * pageSize;
    const paginatedStores = filtered.slice(startIndex, startIndex + pageSize);

    // Available filter lists derived from real live data
    const allCountries = Array.from(new Set(liveStores.map((s) => s.country_code || s.country))).filter(Boolean);

    return NextResponse.json({
      success: true,
      stores: paginatedStores,
      pagination: {
        page,
        pageSize,
        total,
        totalPages,
      },
      filters: {
        countries: allCountries,
        plans: dbPlans.map((p) => ({ id: p.id, name: p.name, price: p.price })),
      },
    });
  } catch (err: any) {
    return NextResponse.json(
      { success: false, error: err?.message || 'Failed to fetch stores' },
      { status: 500 }
    );
  }
}