import { and, eq, gte, lte, sql } from 'drizzle-orm';
import { db } from '@/db/client';
import { incomes, oneTimeExpenses, recurringExpenses, users } from '@/db/schema';
import { calcularSalarioLiquido } from '@/utils/tax-calculator';

export interface DashboardData {
  user: {
    id: number;
    name: string;
    gross_salary: number;
    net_salary_override: number | null;
  };
  period: {
    month: number;
    year: number;
  };
  salary: {
    gross: number;
    calculated_net: number;
    effective_net: number;
    inss_discount: number;
    irrf_discount: number;
    has_override: boolean;
  };
  expenses: {
    recurring_total: number;
    one_time_total: number;
    total: number;
    recurring_list: { id: number; name: string; amount: number; category: string }[];
    one_time_list: {
      id: number;
      name: string;
      amount: number;
      category: string;
      expense_date: string;
    }[];
  };
  balance: {
    available: number;
    /** Percentual do salário líquido comprometido com despesas (0-100) */
    expense_ratio: number;
  };
}

export async function getDashboard(
  userId: number,
  referenceMonth: number,
  referenceYear: number,
): Promise<DashboardData | null> {
  // 1. Busca o usuário
  const [user] = await db
    .select()
    .from(users)
    .where(eq(users.id, userId))
    .limit(1);

  if (!user) return null;

  // 2. Calcula o salário via tabela INSS/IRRF
  const taxResult = calcularSalarioLiquido(user.gross_salary);

  // 3. Salário efetivo: usa override do usuário ou o calculado
  const effectiveNet = user.net_salary_override ?? taxResult.salarioLiquidoCalculado;

  // 4. Tenta encontrar um registro de renda para o mês/ano pedido
  const [incomeRecord] = await db
    .select()
    .from(incomes)
    .where(
      and(
        eq(incomes.user_id, userId),
        eq(incomes.reference_month, referenceMonth),
        eq(incomes.reference_year, referenceYear),
      ),
    )
    .limit(1);

  // Se existir registro de renda para o mês, usa os valores dele
  const salaryData = incomeRecord
    ? {
        gross: incomeRecord.gross_salary,
        calculated_net: incomeRecord.calculated_net_salary,
        effective_net: incomeRecord.effective_net_salary,
        inss_discount:
          incomeRecord.gross_salary - incomeRecord.calculated_net_salary - 0, // simplificado
        irrf_discount: 0,
        has_override: incomeRecord.effective_net_salary !== incomeRecord.calculated_net_salary,
      }
    : {
        gross: user.gross_salary,
        calculated_net: taxResult.salarioLiquidoCalculado,
        effective_net: effectiveNet,
        inss_discount: taxResult.descontoINSS,
        irrf_discount: taxResult.descontoIRRF,
        has_override: user.net_salary_override !== null,
      };

  // 5. Busca gastos recorrentes ATIVOS e vigentes no mês/ano pedido
  const refDateStr = `${referenceYear}-${String(referenceMonth).padStart(2, '0')}-01`;
  const lastDay = new Date(referenceYear, referenceMonth, 0).getDate();
  const refDateEndStr = `${referenceYear}-${String(referenceMonth).padStart(2, '0')}-${String(lastDay).padStart(2, '0')}`;

  const recurringList = await db
    .select()
    .from(recurringExpenses)
    .where(
      and(
        eq(recurringExpenses.user_id, userId),
        eq(recurringExpenses.is_active, true),
        lte(recurringExpenses.starts_at, refDateEndStr),
        // ends_at IS NULL ou ends_at >= início do mês
        sql`(${recurringExpenses.ends_at} IS NULL OR ${recurringExpenses.ends_at} >= ${refDateStr})`,
      ),
    );

  // 6. Busca gastos avulsos do mês/ano pedido
  const oneTimeList = await db
    .select()
    .from(oneTimeExpenses)
    .where(
      and(
        eq(oneTimeExpenses.user_id, userId),
        gte(oneTimeExpenses.expense_date, refDateStr),
        lte(oneTimeExpenses.expense_date, refDateEndStr),
      ),
    )
    .orderBy(oneTimeExpenses.expense_date);

  // 7. Calcula totais
  const recurringTotal = recurringList.reduce((sum, e) => {
    return sum + (e.type === 'income' ? -e.amount : e.amount);
  }, 0);
  const oneTimeTotal = oneTimeList.reduce((sum, e) => {
    return sum + (e.type === 'income' ? -e.amount : e.amount);
  }, 0);
  const totalExpenses = parseFloat((recurringTotal + oneTimeTotal).toFixed(2));
  const available = parseFloat((salaryData.effective_net - totalExpenses).toFixed(2));
  const expenseRatio =
    salaryData.effective_net > 0
      ? parseFloat(((totalExpenses / salaryData.effective_net) * 100).toFixed(1))
      : 0;

  return {
    user: {
      id: user.id,
      name: user.name,
      gross_salary: user.gross_salary,
      net_salary_override: user.net_salary_override,
    },
    period: { month: referenceMonth, year: referenceYear },
    salary: salaryData,
    expenses: {
      recurring_total: parseFloat(recurringTotal.toFixed(2)),
      one_time_total: parseFloat(oneTimeTotal.toFixed(2)),
      total: totalExpenses,
      recurring_list: recurringList.map((e) => ({
        id: e.id,
        name: e.name,
        amount: e.amount,
        category: e.category,
        type: e.type,
      })),
      one_time_list: oneTimeList.map((e) => ({
        id: e.id,
        name: e.name,
        amount: e.amount,
        category: e.category,
        type: e.type,
        expense_date: e.expense_date,
      })),
    },
    balance: {
      available,
      expense_ratio: expenseRatio,
    },
  };
}