import "server-only";

import type { PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import { ensureOfficialMailSchema, getDbPool } from "@/lib/db";
import { BILLING_GRACE_RETRY_LIMIT } from "@/lib/billing-retry-schedule";
import { synchronizeMailboxExternalAccessByEmail } from "@/lib/mail-external-access";
import { setMailcowMailboxActive } from "@/lib/mailcow";
import { syncMailcowMailboxQuotasForOwnerUserId } from "@/lib/mail-plan-quota";

type ManagedMemberRow = RowDataPacket & {
  email: string;
};

type GrowthPlanAccountRow = RowDataPacket & {
  user_id: number;
};

async function getGrowthPlanAccountUserIds(
  connection: PoolConnection,
  ownerUserId: number,
) {
  const [rows] = await connection.query<GrowthPlanAccountRow[]>(
    `
      SELECT ? AS user_id
      UNION
      SELECT member_user.id AS user_id
      FROM managed_team_mailboxes member
      INNER JOIN users member_user
        ON LOWER(member_user.email) = LOWER(member.email)
      WHERE member.owner_user_id = ?
    `,
    [ownerUserId, ownerUserId],
  );

  return [...new Set(rows.map((row) => Number(row.user_id)).filter(Number.isSafeInteger))];
}

export async function disableGrowthPlanAutomationsForOwnerUserId(ownerUserId: number) {
  await ensureOfficialMailSchema();
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    const accountUserIds = await getGrowthPlanAccountUserIds(connection, ownerUserId);

    if (accountUserIds.length === 0) {
      await connection.commit();
      return { aiAssist: 0, autoSend: 0 };
    }

    const placeholders = accountUserIds.map(() => "?").join(", ");
    const [autoSendResult] = await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_auto_send_settings
        SET enabled = 0, next_run_at = NULL, updated_at = NOW()
        WHERE owner_user_id IN (${placeholders})
          AND (enabled = 1 OR next_run_at IS NOT NULL)
      `,
      accountUserIds,
    );
    const [aiAssistResult] = await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_ai_assist_settings
        SET enabled = 0, updated_at = NOW()
        WHERE owner_user_id IN (${placeholders})
          AND enabled = 1
      `,
      accountUserIds,
    );

    // Developer API keys remain recoverable, but every request rechecks the
    // owner's active Growth entitlement and is rejected while on Free.
    await connection.commit();
    return {
      aiAssist: aiAssistResult.affectedRows,
      autoSend: autoSendResult.affectedRows,
    };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

async function setMemberMailcowState(ownerUserId: number, active: boolean) {
  const [members] = await getDbPool().query<ManagedMemberRow[]>(
    `
      SELECT email
      FROM managed_team_mailboxes
      WHERE owner_user_id = ?
        AND ${active ? "billing_suspended_at IS NOT NULL" : "status = 'active'"}
      ORDER BY id ASC
    `,
    [ownerUserId],
  );

  for (const member of members) {
    if (active) {
      // Older suspended members may still have the account password stored as
      // their direct Mailcow credential. Rotate it while the mailbox remains
      // disabled, before restoring IMAP/SMTP service.
      await synchronizeMailboxExternalAccessByEmail(member.email);
    }
    await setMailcowMailboxActive(member.email, active);
  }

  return members.length;
}

async function suspendManagedMembersForPlanDowngradeInternal(
  ownerUserId: number,
  tossSubscriptionId: number | null,
) {
  await ensureOfficialMailSchema();
  await disableGrowthPlanAutomationsForOwnerUserId(ownerUserId);
  const memberCount = await setMemberMailcowState(ownerUserId, false);
  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    await connection.query<ResultSetHeader>(
      `
        UPDATE managed_team_mailboxes
        SET
          status = 'disabled',
          billing_suspended_at = COALESCE(billing_suspended_at, NOW()),
          updated_at = NOW()
        WHERE owner_user_id = ?
          AND status = 'active'
      `,
      [ownerUserId],
    );
    await connection.query<ResultSetHeader>(
      `
        UPDATE mailboxes m
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(m.email)
        SET
          m.status = 'disabled',
          m.last_sync_error = '유료 플랜 이용 종료로 멤버 메일함 사용이 일시 정지되었습니다.',
          m.updated_at = NOW()
        WHERE mtm.owner_user_id = ?
          AND mtm.billing_suspended_at IS NOT NULL
      `,
      [ownerUserId],
    );
    await connection.query<ResultSetHeader>(
      `
        UPDATE users u
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(u.email)
        SET u.mail_configured = 0, u.updated_at = NOW()
        WHERE mtm.owner_user_id = ?
          AND mtm.billing_suspended_at IS NOT NULL
      `,
      [ownerUserId],
    );
    if (tossSubscriptionId !== null) {
      await connection.query<ResultSetHeader>(
        `
          UPDATE mailbox_toss_pay_subscriptions
          SET billing_member_access_suspended_at = NOW(), updated_at = NOW()
          WHERE id = ?
            AND owner_user_id = ?
            AND status = 'paused'
        `,
        [tossSubscriptionId, ownerUserId],
      );
    }
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }

  await syncMailcowMailboxQuotasForOwnerUserId(ownerUserId, "free");
  return memberCount;
}

export async function suspendManagedMembersForBillingFailure(
  ownerUserId: number,
  subscriptionId: number,
) {
  return suspendManagedMembersForPlanDowngradeInternal(ownerUserId, subscriptionId);
}

export async function suspendManagedMembersForPlanDowngrade(ownerUserId: number) {
  return suspendManagedMembersForPlanDowngradeInternal(ownerUserId, null);
}

export async function restoreManagedMembersAfterBillingRecovery(ownerUserId: number) {
  await ensureOfficialMailSchema();
  const memberCount = await setMemberMailcowState(ownerUserId, true);

  if (memberCount === 0) {
    await getDbPool().query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET billing_member_access_suspended_at = NULL, updated_at = NOW()
        WHERE owner_user_id = ?
      `,
      [ownerUserId],
    );
    return 0;
  }

  const connection = await getDbPool().getConnection();

  try {
    await connection.beginTransaction();
    await connection.query<ResultSetHeader>(
      `
        UPDATE managed_team_mailboxes
        SET status = 'active', billing_suspended_at = NULL, updated_at = NOW()
        WHERE owner_user_id = ?
          AND billing_suspended_at IS NOT NULL
      `,
      [ownerUserId],
    );
    await connection.query<ResultSetHeader>(
      `
        UPDATE mailboxes m
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(m.email)
        SET m.status = 'active', m.last_sync_error = NULL, m.updated_at = NOW()
        WHERE mtm.owner_user_id = ?
          AND mtm.status = 'active'
      `,
      [ownerUserId],
    );
    await connection.query<ResultSetHeader>(
      `
        UPDATE users u
        INNER JOIN managed_team_mailboxes mtm
          ON LOWER(mtm.email) = LOWER(u.email)
        SET u.mail_configured = 1, u.updated_at = NOW()
        WHERE mtm.owner_user_id = ?
          AND mtm.status = 'active'
      `,
      [ownerUserId],
    );
    await connection.query<ResultSetHeader>(
      `
        UPDATE mailbox_toss_pay_subscriptions
        SET billing_member_access_suspended_at = NULL, updated_at = NOW()
        WHERE owner_user_id = ?
      `,
      [ownerUserId],
    );
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }

  await syncMailcowMailboxQuotasForOwnerUserId(ownerUserId, "growth");
  return memberCount;
}

export async function assertManagedMemberLoginAvailable(email: string) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<(RowDataPacket & { status: string })[]>(
    `
      SELECT status
      FROM managed_team_mailboxes
      WHERE LOWER(email) = LOWER(?)
      LIMIT 1
    `,
    [email],
  );

  if (rows[0]?.status === "disabled") {
    throw new Error("growth-plan-required");
  }
}

export async function processPendingBillingMemberSuspensions(limit = 20) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<
    (RowDataPacket & { owner_user_id: number; subscription_id: number })[]
  >(
    `
      SELECT id AS subscription_id, owner_user_id
      FROM mailbox_toss_pay_subscriptions
      WHERE status = 'paused'
        AND billing_grace_retry_count >= ?
        AND billing_member_access_suspended_at IS NULL
      ORDER BY updated_at ASC, id ASC
      LIMIT ?
    `,
    [BILLING_GRACE_RETRY_LIMIT, Math.max(1, Math.min(100, limit))],
  );

  for (const row of rows) {
    await suspendManagedMembersForBillingFailure(
      row.owner_user_id,
      row.subscription_id,
    );
  }

  return rows.length;
}

export async function processPendingBillingMemberRestorations(limit = 20) {
  await ensureOfficialMailSchema();
  const [rows] = await getDbPool().query<
    (RowDataPacket & { owner_user_id: number })[]
  >(
    `
      SELECT owner_user_id
      FROM mailbox_toss_pay_subscriptions
      WHERE status = 'active'
        AND billing_member_access_suspended_at IS NOT NULL
      ORDER BY updated_at ASC, id ASC
      LIMIT ?
    `,
    [Math.max(1, Math.min(100, limit))],
  );

  for (const row of rows) {
    await restoreManagedMembersAfterBillingRecovery(row.owner_user_id);
  }

  return rows.length;
}