import "server-only";

import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";

export type DomainSetupPromotionDiscountType = "fixed" | "percent";

export type DomainSetupPromotionPreview = {
  code: string;
  discountAmount: number;
  finalAmount: number;
  label: string;
  originalAmount: number;
};

export type KavenixDomainSetupPromotionCodeRow = {
  code: string;
  createdAt: string;
  discountType: DomainSetupPromotionDiscountType;
  discountValue: number;
  expiresAt: string | null;
  id: number;
  isActive: boolean;
  label: string;
  perUserLimit: number;
  redeemedCount: number;
  reservedCount: number;
  startsAt: string | null;
  usageLimit: number | null;
};

type PromotionCodeRow = RowDataPacket & {
  code: string;
  created_at: Date;
  discount_type: DomainSetupPromotionDiscountType;
  discount_value: number;
  expires_at: Date | null;
  id: number;
  is_active: number;
  label: string;
  per_user_limit: number;
  starts_at: Date | null;
  usage_limit: number | null;
};

type PromotionCountRow = RowDataPacket & {
  redeemed_count: number;
  reserved_count: number;
  total: number;
};

type AdminPromotionCodeRow = PromotionCodeRow & PromotionCountRow;

const PROMOTION_CODE_PATTERN = /^[A-Z0-9][A-Z0-9_-]{2,63}$/;

export function normalizeDomainSetupPromotionCode(value: unknown) {
  return String(value ?? "").trim().toUpperCase().slice(0, 64);
}

function normalizeText(value: unknown, maxLength: number) {
  return String(value ?? "").replace(/\r\n/g, "\n").trim().slice(0, maxLength);
}

function toIsoString(value: Date | null) {
  return value ? value.toISOString() : null;
}

function calculateDiscount(
  originalAmount: number,
  discountType: DomainSetupPromotionDiscountType,
  discountValue: number,
) {
  const requestedDiscount =
    discountType === "percent"
      ? Math.floor((originalAmount * discountValue) / 100)
      : discountValue;

  return Math.min(originalAmount, Math.max(0, requestedDiscount));
}

async function getPromotionCounts(
  connection: PoolConnection,
  promotionCodeId: number,
  userId?: number,
) {
  const filters = ["promotion_code_id = ?", "status IN ('reserved', 'redeemed')"];
  const values: unknown[] = [promotionCodeId];

  if (userId) {
    filters.push("user_id = ?");
    values.push(userId);
  }

  const [rows] = await connection.query<PromotionCountRow[]>(
    `
      SELECT
        COUNT(*) AS total,
        SUM(CASE WHEN status = 'reserved' THEN 1 ELSE 0 END) AS reserved_count,
        SUM(CASE WHEN status = 'redeemed' THEN 1 ELSE 0 END) AS redeemed_count
      FROM domain_setup_promotion_redemptions
      WHERE ${filters.join(" AND ")}
    `,
    values,
  );

  return {
    redeemed: Number(rows[0]?.redeemed_count ?? 0),
    reserved: Number(rows[0]?.reserved_count ?? 0),
    total: Number(rows[0]?.total ?? 0),
  };
}

async function resolvePromotion(
  connection: PoolConnection,
  input: {
    code: string;
    forUpdate: boolean;
    originalAmount: number;
    userId: number;
  },
) {
  const code = normalizeDomainSetupPromotionCode(input.code);

  if (!code) {
    throw new Error("promotion-code-required");
  }

  if (!PROMOTION_CODE_PATTERN.test(code)) {
    throw new Error("promotion-code-invalid-format");
  }

  const [rows] = await connection.query<PromotionCodeRow[]>(
    `
      SELECT *
      FROM domain_setup_promotion_codes
      WHERE code = ?
      LIMIT 1
      ${input.forUpdate ? "FOR UPDATE" : ""}
    `,
    [code],
  );
  const promotion = rows[0];

  if (!promotion) {
    throw new Error("promotion-code-not-found");
  }

  const now = Date.now();

  if (!promotion.is_active) {
    throw new Error("promotion-code-inactive");
  }

  if (promotion.starts_at && promotion.starts_at.getTime() > now) {
    throw new Error("promotion-code-not-started");
  }

  if (promotion.expires_at && promotion.expires_at.getTime() < now) {
    throw new Error("promotion-code-expired");
  }

  const [allUsage, userUsage] = await Promise.all([
    getPromotionCounts(connection, promotion.id),
    getPromotionCounts(connection, promotion.id, input.userId),
  ]);

  if (promotion.usage_limit !== null && allUsage.total >= Number(promotion.usage_limit)) {
    throw new Error("promotion-code-usage-limit");
  }

  if (userUsage.total >= Number(promotion.per_user_limit)) {
    throw new Error("promotion-code-user-limit");
  }

  const originalAmount = Math.max(0, Math.trunc(input.originalAmount));
  const discountAmount = calculateDiscount(
    originalAmount,
    promotion.discount_type,
    Number(promotion.discount_value),
  );

  return {
    id: Number(promotion.id),
    preview: {
      code: promotion.code,
      discountAmount,
      finalAmount: originalAmount - discountAmount,
      label: promotion.label,
      originalAmount,
    } satisfies DomainSetupPromotionPreview,
  };
}

async function getUserIdByEmail(connection: PoolConnection, email: string) {
  const [rows] = await connection.query<(RowDataPacket & { id: number })[]>(
    "SELECT id FROM users WHERE email = ? LIMIT 1",
    [email.trim().toLowerCase()],
  );

  if (!rows[0]) {
    throw new Error("domain-assistance-user-not-found");
  }

  return Number(rows[0].id);
}

export async function getDomainSetupPromotionPreview(
  ownerEmail: string,
  code: string,
  originalAmount: number,
) {
  await ensureOfficialMailSchema();
  const connection = await getDbPool().getConnection();

  try {
    const userId = await getUserIdByEmail(connection, ownerEmail);
    const promotion = await resolvePromotion(connection, {
      code,
      forUpdate: false,
      originalAmount,
      userId,
    });
    return promotion.preview;
  } finally {
    connection.release();
  }
}

export async function reserveDomainSetupPromotion(
  connection: PoolConnection,
  input: {
    assistanceRequestId: number;
    code: string;
    originalAmount: number;
    userId: number;
  },
) {
  const promotion = await resolvePromotion(connection, {
    code: input.code,
    forUpdate: true,
    originalAmount: input.originalAmount,
    userId: input.userId,
  });

  await connection.query(
    `
      INSERT INTO domain_setup_promotion_redemptions (
        promotion_code_id,
        assistance_request_id,
        user_id,
        original_amount,
        discount_amount,
        final_amount,
        status
      ) VALUES (?, ?, ?, ?, ?, ?, 'reserved')
    `,
    [
      promotion.id,
      input.assistanceRequestId,
      input.userId,
      promotion.preview.originalAmount,
      promotion.preview.discountAmount,
      promotion.preview.finalAmount,
    ],
  );

  return { promotionCodeId: promotion.id, ...promotion.preview };
}

export async function redeemDomainSetupPromotion(
  connection: PoolConnection,
  assistanceRequestId: number,
) {
  await connection.query(
    `
      UPDATE domain_setup_promotion_redemptions
      SET status = 'redeemed', redeemed_at = NOW(), released_at = NULL, updated_at = NOW()
      WHERE assistance_request_id = ? AND status = 'reserved'
    `,
    [assistanceRequestId],
  );
}

export async function releaseDomainSetupPromotion(
  connection: PoolConnection,
  assistanceRequestId: number,
) {
  await connection.query(
    `
      UPDATE domain_setup_promotion_redemptions
      SET status = 'released', released_at = NOW(), updated_at = NOW()
      WHERE assistance_request_id = ? AND status = 'reserved'
    `,
    [assistanceRequestId],
  );
}

export async function getKavenixDomainSetupPromotionCodeCount() {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<(RowDataPacket & { total: number })[]>(
    "SELECT COUNT(*) AS total FROM domain_setup_promotion_codes",
  );
  return Number(rows[0]?.total ?? 0);
}

export async function getKavenixDomainSetupPromotionCodes() {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<AdminPromotionCodeRow[]>(`
    SELECT
      code.*,
      COALESCE(SUM(CASE WHEN redemption.status = 'reserved' THEN 1 ELSE 0 END), 0) AS reserved_count,
      COALESCE(SUM(CASE WHEN redemption.status = 'redeemed' THEN 1 ELSE 0 END), 0) AS redeemed_count,
      COALESCE(SUM(CASE WHEN redemption.status IN ('reserved', 'redeemed') THEN 1 ELSE 0 END), 0) AS total
    FROM domain_setup_promotion_codes code
    LEFT JOIN domain_setup_promotion_redemptions redemption
      ON redemption.promotion_code_id = code.id
    GROUP BY code.id
    ORDER BY code.created_at DESC, code.id DESC
  `);

  return rows.map(
    (row) =>
      ({
        code: row.code,
        createdAt: row.created_at.toISOString(),
        discountType: row.discount_type,
        discountValue: Number(row.discount_value),
        expiresAt: toIsoString(row.expires_at),
        id: Number(row.id),
        isActive: row.is_active === 1,
        label: row.label,
        perUserLimit: Number(row.per_user_limit),
        redeemedCount: Number(row.redeemed_count ?? 0),
        reservedCount: Number(row.reserved_count ?? 0),
        startsAt: toIsoString(row.starts_at),
        usageLimit: row.usage_limit === null ? null : Number(row.usage_limit),
      }) satisfies KavenixDomainSetupPromotionCodeRow,
  );
}

function parseOptionalDate(value: unknown) {
  const normalized = normalizeText(value, 64);

  if (!normalized) {
    return null;
  }

  const parsed = new Date(normalized);

  if (Number.isNaN(parsed.getTime())) {
    throw new Error("promotion-code-invalid-period");
  }

  return parsed;
}

export async function createKavenixDomainSetupPromotionCode(input: {
  code: unknown;
  createdByEmail: string;
  discountType: unknown;
  discountValue: unknown;
  expiresAt?: unknown;
  label: unknown;
  perUserLimit: unknown;
  startsAt?: unknown;
  usageLimit?: unknown;
}) {
  await ensureOfficialMailSchema();
  const code = normalizeDomainSetupPromotionCode(input.code);
  const label = normalizeText(input.label, 191);
  const discountType = String(input.discountType) as DomainSetupPromotionDiscountType;
  const discountValue = Math.trunc(Number(input.discountValue));
  const perUserLimit = Math.trunc(Number(input.perUserLimit));
  const usageLimitValue = String(input.usageLimit ?? "").trim();
  const usageLimit = usageLimitValue ? Math.trunc(Number(usageLimitValue)) : null;
  const startsAt = parseOptionalDate(input.startsAt);
  const expiresAt = parseOptionalDate(input.expiresAt);

  if (!PROMOTION_CODE_PATTERN.test(code)) {
    throw new Error("promotion-code-invalid-format");
  }

  if (!label) {
    throw new Error("promotion-code-label-required");
  }

  if (!['fixed', 'percent'].includes(discountType)) {
    throw new Error("promotion-code-invalid-discount");
  }

  if (
    !Number.isInteger(discountValue) ||
    discountValue <= 0 ||
    (discountType === "percent" && discountValue > 100) ||
    (discountType === "fixed" && discountValue > 1_000_000)
  ) {
    throw new Error("promotion-code-invalid-discount");
  }

  if (!Number.isInteger(perUserLimit) || perUserLimit <= 0 || perUserLimit > 1000) {
    throw new Error("promotion-code-invalid-limit");
  }

  if (usageLimit !== null && (!Number.isInteger(usageLimit) || usageLimit <= 0)) {
    throw new Error("promotion-code-invalid-limit");
  }

  if (startsAt && expiresAt && startsAt >= expiresAt) {
    throw new Error("promotion-code-invalid-period");
  }

  try {
    const [result] = await getDbPool().query<ResultSetHeader>(
      `
        INSERT INTO domain_setup_promotion_codes (
          code,
          label,
          discount_type,
          discount_value,
          usage_limit,
          per_user_limit,
          starts_at,
          expires_at,
          is_active,
          created_by_email
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, ?)
      `,
      [
        code,
        label,
        discountType,
        discountValue,
        usageLimit,
        perUserLimit,
        startsAt,
        expiresAt,
        input.createdByEmail.trim().toLowerCase(),
      ],
    );

    return Number(result.insertId);
  } catch (error) {
    if (error && typeof error === "object" && "code" in error && error.code === "ER_DUP_ENTRY") {
      throw new Error("promotion-code-duplicate");
    }

    throw error;
  }
}

export async function updateKavenixDomainSetupPromotionCode(
  promotionCodeId: number,
  input: { isActive: boolean },
) {
  await ensureOfficialMailSchema();
  const [result] = await getDbPool().query<ResultSetHeader>(
    "UPDATE domain_setup_promotion_codes SET is_active = ?, updated_at = NOW() WHERE id = ?",
    [input.isActive ? 1 : 0, promotionCodeId],
  );

  if (result.affectedRows === 0) {
    throw new Error("promotion-code-not-found");
  }
}