import type { PoolClient } from 'pg';

export interface ScheduledAccountDeletion {
  affectedUserIds: string[];
  guardianUserIds: string[];
  accountType: 'candidate' | 'guardian' | 'representative';
  purgeAfter: Date;
}

export interface ImmediateAccountDeletion {
  affectedUserIds: string[];
  guardianUserIds: string[];
  accountType: 'candidate' | 'guardian' | 'representative';
  targetEmail: string;
}

export const scheduleAccountDeletion = async (
  client: PoolClient,
  userId: string,
): Promise<ScheduledAccountDeletion | null> => {
  const targetResult = await client.query<{
    id: string;
    account_type: ScheduledAccountDeletion['accountType'];
  }>(
    `SELECT id,account_type FROM users
     WHERE id=$1 AND role='user' AND deleted_at IS NULL
     FOR UPDATE`,
    [userId],
  );
  const target = targetResult.rows[0];
  if (!target) return null;

  const guardianResult = target.account_type === 'candidate'
    ? await client.query<{ id: string }>(
      `SELECT guardian.id
       FROM guardian_invitations invitation
       JOIN users guardian ON guardian.id=invitation.accepted_by
       WHERE invitation.candidate_user_id=$1
         AND invitation.status IN ('registered','verified')
         AND guardian.account_type='guardian'
         AND guardian.deleted_at IS NULL
       FOR UPDATE OF guardian`,
      [userId],
    )
    : { rows: [] as Array<{ id: string }> };
  const guardianUserIds = [...new Set(guardianResult.rows.map((row) => row.id))];
  const affectedUserIds = [userId, ...guardianUserIds];

  await client.query(
    `INSERT INTO seed_account_suppressions (email)
     SELECT LOWER(email) FROM users WHERE id=ANY($1::uuid[])
     ON CONFLICT (email) DO NOTHING`,
    [affectedUserIds],
  );

  const deleted = await client.query<{ id: string; purge_after: Date }>(
    `UPDATE users
     SET is_active=FALSE,token_version=token_version+1,
         deleted_at=NOW(),purge_after=NOW()+INTERVAL '30 days'
     WHERE id=ANY($1::uuid[]) AND deleted_at IS NULL
     RETURNING id,purge_after`,
    [affectedUserIds],
  );
  const purgeAfter = deleted.rows.find((row) => row.id === userId)?.purge_after;
  if (!purgeAfter) return null;

  await client.query(
    `UPDATE profiles SET is_published=FALSE,publication_requested=FALSE
     WHERE user_id=ANY($1::uuid[])`,
    [affectedUserIds],
  );
  await client.query(
    `UPDATE journey_memberships SET is_active=FALSE
     WHERE user_id=ANY($1::uuid[])`,
    [affectedUserIds],
  );

  if (target.account_type === 'candidate') {
    await client.query(
      `UPDATE journeys SET status='closed',updated_at=NOW()
       WHERE status='active' AND (first_user_id=$1 OR second_user_id=$1)`,
      [userId],
    );
    await client.query(
      `UPDATE engagement_requests
       SET status=CASE WHEN sender_user_id=$1 THEN 'withdrawn' ELSE 'declined' END,
           updated_at=NOW()
       WHERE status IN ('pending','accepted')
         AND (sender_user_id=$1 OR recipient_user_id=$1)`,
      [userId],
    );
  }

  return { affectedUserIds, guardianUserIds, accountType: target.account_type, purgeAfter };
};

export const deleteAccountImmediately = async (
  client: PoolClient,
  userId: string,
  adminUserId: string,
): Promise<ImmediateAccountDeletion | null> => {
  const targetResult = await client.query<{
    id: string;
    email: string;
    account_type: ImmediateAccountDeletion['accountType'];
  }>(
    `SELECT id,email,account_type FROM users WHERE id=$1 AND role='user' FOR UPDATE`,
    [userId],
  );
  const target = targetResult.rows[0];
  if (!target) return null;

  const guardianResult = target.account_type === 'candidate'
    ? await client.query<{ id: string }>(
      `SELECT guardian.id
       FROM guardian_invitations invitation
       JOIN users guardian ON guardian.id=invitation.accepted_by
       WHERE invitation.candidate_user_id=$1
         AND invitation.status IN ('registered','verified')
         AND guardian.account_type='guardian'
       FOR UPDATE OF guardian`,
      [userId],
    )
    : { rows: [] as Array<{ id: string }> };
  const guardianUserIds = [...new Set(guardianResult.rows.map((row) => row.id))];
  const affectedUserIds = [userId, ...guardianUserIds];

  await client.query(
    `INSERT INTO seed_account_suppressions (email,deleted_by)
     SELECT LOWER(email),$2 FROM users WHERE id=ANY($1::uuid[])
     ON CONFLICT (email) DO UPDATE SET deleted_at=NOW(),deleted_by=EXCLUDED.deleted_by`,
    [affectedUserIds, adminUserId],
  );
  await client.query(
    `DELETE FROM journey_call_guardians
     WHERE participant_user_id=ANY($1::uuid[]) OR guardian_user_id=ANY($1::uuid[])
       OR verification_id IN (SELECT id FROM guardian_invitations
         WHERE candidate_user_id=ANY($1::uuid[]) OR accepted_by=ANY($1::uuid[]))
       OR exception_id IN (SELECT id FROM guardian_exceptions
         WHERE candidate_user_id=ANY($1::uuid[]) OR representative_user_id=ANY($1::uuid[]))`,
    [affectedUserIds],
  );
  await client.query(
    `UPDATE guardian_invitations SET status='revoked',accepted_by=NULL,completed_at=NOW()
     WHERE accepted_by=ANY($1::uuid[])`,
    [affectedUserIds],
  );
  await client.query(
    `UPDATE guardian_exceptions SET status='revoked',representative_user_id=NULL
     WHERE representative_user_id=ANY($1::uuid[])`,
    [affectedUserIds],
  );
  await client.query('DELETE FROM users WHERE id=ANY($1::uuid[])', [affectedUserIds]);

  return {
    affectedUserIds,
    guardianUserIds,
    accountType: target.account_type,
    targetEmail: target.email,
  };
};