import type { ModelId } from "@/lib/chat/types";
import type { UserServiceProfile } from "@/lib/auth/user-service";
import type { Queryable } from "@/lib/db/types";

export type DatabaseUser = {
  id: string;
  externalUserId: string | null;
  email: string;
  passwordHash: string;
  login: string | null;
  fullName: string | null;
  position: string | null;
  firstName: string | null;
  lastName: string | null;
  middleName: string;
  roles: string[];
  lastModelId: ModelId;
  createdAt: string;
  updatedAt: string;
};

type UserRow = {
  id: string;
  external_user_id: string | null;
  email: string;
  password_hash: string | null;
  login: string | null;
  full_name: string | null;
  position: string | null;
  first_name: string | null;
  last_name: string | null;
  middle_name: string;
  roles: string[];
  last_model_id: ModelId;
  created_at: Date | string;
  updated_at: Date | string;
};

export async function findUserByEmail(
  database: Queryable,
  email: string,
): Promise<DatabaseUser | null> {
  const result = await database.query<UserRow>(
    `SELECT id, external_user_id, email, password_hash, login, full_name,
            position, first_name, last_name, middle_name, roles, last_model_id,
            created_at, updated_at
     FROM users
     WHERE email = $1`,
    [normalizeEmail(email)],
  );

  return result.rows[0] ? mapUser(result.rows[0]) : null;
}

export async function findUserById(
  database: Queryable,
  id: string,
): Promise<DatabaseUser | null> {
  const result = await database.query<UserRow>(
    `SELECT id, external_user_id, email, password_hash, login, full_name,
            position, first_name, last_name, middle_name, roles, last_model_id,
            created_at, updated_at
     FROM users
     WHERE id = $1`,
    [id],
  );

  return result.rows[0] ? mapUser(result.rows[0]) : null;
}

export async function updateLastModelId(
  database: Queryable,
  userId: string,
  modelId: ModelId,
): Promise<void> {
  await database.query(
    `UPDATE users
     SET last_model_id = $1, updated_at = now()
     WHERE id = $2`,
    [modelId, userId],
  );
}

export async function upsertExternalUser(
  database: Queryable,
  profile: UserServiceProfile,
): Promise<DatabaseUser> {
  const result = await database.query<UserRow>(
    `WITH adopted AS (
       UPDATE users
       SET external_user_id = $1,
           email = $2,
           login = $3,
           full_name = $4,
           position = $5,
           first_name = $6,
           last_name = $7,
           middle_name = $8,
           roles = $9::jsonb,
           updated_at = now()
       WHERE email = $2 AND external_user_id IS NULL
       RETURNING id, external_user_id, email, password_hash, login, full_name,
                 position, first_name, last_name, middle_name, roles,
                 last_model_id, created_at, updated_at
     ), upserted AS (
       INSERT INTO users (
         external_user_id, email, login, full_name, position, first_name,
         last_name, middle_name, roles
       )
       SELECT $1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb
       WHERE NOT EXISTS (SELECT 1 FROM adopted)
       ON CONFLICT (external_user_id) DO UPDATE SET
         email = EXCLUDED.email,
         login = EXCLUDED.login,
         full_name = EXCLUDED.full_name,
         position = EXCLUDED.position,
         first_name = EXCLUDED.first_name,
         last_name = EXCLUDED.last_name,
         middle_name = EXCLUDED.middle_name,
         roles = EXCLUDED.roles,
         updated_at = now()
       RETURNING id, external_user_id, email, password_hash, login, full_name,
                 position, first_name, last_name, middle_name, roles,
                 last_model_id, created_at, updated_at
     )
     SELECT * FROM adopted
     UNION ALL
     SELECT * FROM upserted
     LIMIT 1`,
    [
      profile.id,
      normalizeEmail(profile.email),
      profile.login,
      profile.fullName,
      profile.position,
      profile.firstName,
      profile.lastName,
      profile.middleName,
      JSON.stringify(profile.roles),
    ],
  );

  if (!result.rows[0]) {
    throw new Error("External user upsert did not return a user");
  }

  return mapUser(result.rows[0]);
}

export function normalizeEmail(email: string) {
  return email.trim().toLowerCase();
}

function mapUser(row: UserRow): DatabaseUser {
  return {
    id: row.id,
    externalUserId: row.external_user_id,
    email: row.email,
    passwordHash: row.password_hash ?? "",
    login: row.login,
    fullName: row.full_name,
    position: row.position,
    firstName: row.first_name,
    lastName: row.last_name,
    middleName: row.middle_name ?? "",
    roles: row.roles ?? [],
    lastModelId: row.last_model_id,
    createdAt: new Date(row.created_at).toISOString(),
    updatedAt: new Date(row.updated_at).toISOString(),
  };
}