Untitled

'use server';
import { prisma } from '@/lib/prisma';
import { Category } from '@/types/category';
import { Prisma } from '@prisma/client';
import axios from 'axios';

type Platform = 'GOOGLE' | 'META';
type SheetPlatform = 'Taboola' | 'Twitter';

function getOverAllSpendQuery(category: Category, from: string, to: string) {
  switch (category) {
    case 'Finance':
      return Prisma.sql`
      SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      WHERE 
      a."campaignName" ilike '%_TW%'
      and (a."campaignName" not ilike '%_HV%' 
      or a."campaignName" not ilike '%_HL%'
      or a."campaignName" not ilike '%_AL%')
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      `;
    case 'Spirituality':
      return Prisma.sql`
      SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      WHERE 
      a."campaignName" ilike '%_AL%' --For Astro
      and a."campaignName" not like '%_TW%' 
      and a."campaignName" not like '%_HV%'
      and a."campaignName" not like '%_HL%'
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      `;
    case 'HealoVed':
      return Prisma.sql`
      SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      WHERE 
      (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed
      and a."campaignName" not ilike '%_AL%'
      and a."campaignName" not ilike '%_TW%' 
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      `;
  }
}

export async function overallSpend(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
      SELECT
    SUM(a."spend") AS Category_spend
    FROM "AdStats" a
    Where 
    a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].Category_spend;
    }
    return 0;
  } catch (error) {
    console.error('Error in overallSpend', error);
    return 0;
  }
}

export async function categorySpend(
  from: string,
  to: string,
  category: Category,
) {
  try {
    const query = getOverAllSpendQuery(category, from, to);
    const data = await prisma.$queryRaw(query);
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error(error);
    return '0';
  }
}

export async function spendPlatformFinance(
  from: string,
  to: string,
  platform: Platform,
): Promise<number> {
  try {
    const data = await prisma.$queryRaw`
       SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      WHERE 
      a."campaignName" ilike '%_TW%'
      and (a."campaignName" not ilike '%_HV%' 
      or a."campaignName" not ilike '%_HL%'
      or a."campaignName" not ilike '%_AL%')
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = ${platform}::"AdPlatformType"
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend;
    }
    return 0;
  } catch (error) {
    console.error(`Error in ${platform} Spend Finance`, error);
    return -1;
  }
}

export async function spendPlatformSpirituality(
  from: string,
  to: string,
  platform: Platform,
): Promise<number> {
  try {
    const data = await prisma.$queryRaw`
     SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      WHERE 
      a."campaignName" ilike '%_AL%' --For Astro
      and a."campaignName" not like '%_TW%' 
      and a."campaignName" not like '%_HV%'
      and a."campaignName" not like '%_HL%'
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = ${platform} :: "AdPlatformType"
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend;
    }
    return 0;
  } catch (error) {
    console.error(`Error in ${platform} Spend Spirituality`, error);
    return -1;
  }
}

export async function spendPlatformHealoVed(
  from: string,
  to: string,
  platform: Platform,
): Promise<number> {
  try {
    const data = await prisma.$queryRaw`
    SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      Where 
      (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed
      and a."campaignName" not ilike '%_AL%'
      and a."campaignName" not ilike '%_TW%' 
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
        and a."platform" = ${platform} :: "AdPlatformType"
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return 0;
  } catch (error) {
    console.error(`Error in ${platform} Spend HealoVed`, error);
    return -1;
  }
}

export async function googlePaidSpendFinance(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
    SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      Where 
      a."campaignName" ilike '%_TW%'
      and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%')
      and (a."campaignName" not ilike '%_HV%' 
      or a."campaignName" not ilike '%_HL%'
      or a."campaignName" not ilike '%_AL%')
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = 'GOOGLE';
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error('Error in googlePaidSpend', error);
    return '-1';
  }
}

export async function facebookPaidSpendFinance(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
   SELECT
    SUM(a."spend") AS Category_spend
    FROM "AdStats" a
    Where 
      a."campaignName" ilike '%_TW%'
      and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%')
      and (a."campaignName" not ilike '%_HV%' 
      or a."campaignName" not ilike '%_HL%'
      or a."campaignName" not ilike '%_AL%')
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = 'META';
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error('Error in facebookPaidSpend', error);
    return '-1';
  }
}

export async function googlePaidSpendSpirituality(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
    SELECT
        SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      Where 
      a."campaignName" ilike '%_AL%' --For Astro
      and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%')
      and a."campaignName" not like '%_TW%' 
      and a."campaignName" not like '%_HV%'
      and a."campaignName" not like '%_HL%'
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = 'GOOGLE';
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error('Error in googlePaidSpendSpirituality', error);
    return '-1';
  }
}

export async function facebookPaidSpendSpirituality(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
    SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      Where 
        a."campaignName" ilike '%_AL%' --For Astro
      and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%')
      and a."campaignName" not like '%_TW%' 
      and a."campaignName" not like '%_HV%'
      and a."campaignName" not like '%_HL%'
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = 'META';
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error('Error in facebookPaidSpendSpirituality', error);
    return '-1';
  }
}

export async function googlePaidSpendHealoVed(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
    SELECT
        SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      Where 
      (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed
      and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%')
      and a."campaignName" not ilike '%_AL%'
      and a."campaignName" not ilike '%_TW%' 
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = 'GOOGLE';
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error('Error in googlePaidSpendHealoVed', error);
    return '-1';
  }
}

export async function facebookPaidSpendHealoVed(from: string, to: string) {
  try {
    const data = await prisma.$queryRaw`
    SELECT
      SUM(a."spend") AS Category_spend
      FROM "AdStats" a
      Where 
      (a."campaignName" ilike '%_HV%' OR a."campaignName" ilike '%_HL%') -- For HealoVed
      and (a."campaignName" ilike '%Paid%' or a."campaignName" ilike '%Conversion%')
      and a."campaignName" not ilike '%_AL%'
      and a."campaignName" not ilike '%_TW%' 
      and a."reportDate" BETWEEN ${from}::timestamp AND ${to}::timestamp
      and a."platform" = 'META';
    `;
    prisma.$disconnect();
    if (Array.isArray(data) && data.length > 0) {
      return data[0].category_spend?.toFixed(0) || '0';
    }
    return '0';
  } catch (error) {
    console.error('Error in facebookPaidSpendHealoVed', error);
    return '-1';
  }
}

export async function getSpendFromSheet(
  from: string,
  to: string,
  category: Category,
  platform: SheetPlatform,
) {
  const url = `${process.env.NEXT_PUBLIC_GOOGLE_SHEET_URL}?category=${category}&platform=${platform}&from=${from}&to=${to}`;
  try {
    const response = await axios.get(url);
    return response?.data?.totalSpend || '-1';
  } catch (error) {
    console.error('Error in get Spend From Sheet', error);
    return '-1';
  }
}