import "server-only";

import type { Pool, PoolConnection, RowDataPacket } from "mysql2/promise";

import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { hasComplimentaryGrowthPlanByEmail } from "@/lib/mail-billing";

type OperationalBenefitRow = RowDataPacket & {
  email: string;
  operational_benefit_enabled: number;
};

type Queryable = Pool | PoolConnection;

async function getQueryable(queryable?: Queryable) {
  await ensureOfficialMailSchema();
  return queryable ?? getDbPool();
}

export async function hasOperationalBenefitByOwnerUserId(
  ownerUserId: number,
  queryable?: Queryable,
) {
  if (!Number.isInteger(ownerUserId) || ownerUserId <= 0) {
    return false;
  }

  const executor = await getQueryable(queryable);
  const [rows] = await executor.query<OperationalBenefitRow[]>(
    `
      SELECT
        u.email,
        COALESCE(MAX(
          CASE
            WHEN d.operational_benefit_enabled = 1
              AND d.mailcow_cleanup_at IS NULL
            THEN 1
            ELSE 0
          END
        ), 0) AS operational_benefit_enabled
      FROM users u
      LEFT JOIN domains d ON d.user_id = u.id
      WHERE u.id = ?
      GROUP BY u.id, u.email
      LIMIT 1
    `,
    [ownerUserId],
  );
  const owner = rows[0];

  return Boolean(
    owner &&
      (hasComplimentaryGrowthPlanByEmail(owner.email) ||
        owner.operational_benefit_enabled === 1),
  );
}

export async function hasOperationalBenefitByOwnerEmail(
  ownerEmail: string | null | undefined,
  queryable?: Queryable,
) {
  const normalizedEmail = ownerEmail?.trim().toLowerCase() ?? "";

  if (!normalizedEmail) {
    return false;
  }

  if (hasComplimentaryGrowthPlanByEmail(normalizedEmail)) {
    return true;
  }

  const executor = await getQueryable(queryable);
  const [rows] = await executor.query<OperationalBenefitRow[]>(
    `
      SELECT
        u.email,
        COALESCE(MAX(
          CASE
            WHEN d.operational_benefit_enabled = 1
              AND d.mailcow_cleanup_at IS NULL
            THEN 1
            ELSE 0
          END
        ), 0) AS operational_benefit_enabled
      FROM users u
      LEFT JOIN domains d ON d.user_id = u.id
      WHERE LOWER(u.email) = ?
      GROUP BY u.id, u.email
      LIMIT 1
    `,
    [normalizedEmail],
  );

  return rows[0]?.operational_benefit_enabled === 1;
}