import type { RowDataPacket } from "mysql2/promise";
import { pool } from "./pool.js";

interface UserRow extends RowDataPacket {
  id: number;
  username: string;
  email: string;
  password_hash: string;
  display_name: string | null;
  bio: string | null;
  created_at: string;
  updated_at: string;
}

interface SessionRow extends RowDataPacket {
  id: string;
  user_id: number;
  expires_at: string;
}

interface RepoRow extends RowDataPacket {
  id: number;
  owner_id: number;
  name: string;
  description: string | null;
  visibility: "public" | "private";
  default_channel: string;
  created_at: string;
  updated_at: string;
  owner_username: string;
  owner_display_name: string | null;
}

export async function findUserByEmail(email: string): Promise<UserRow | null> {
  const [rows] = await pool.query<UserRow[]>(
    "SELECT * FROM users WHERE email = ? LIMIT 1",
    [email],
  );
  return rows[0] ?? null;
}

export async function findUserByUsername(username: string): Promise<UserRow | null> {
  const [rows] = await pool.query<UserRow[]>(
    "SELECT * FROM users WHERE username = ? LIMIT 1",
    [username],
  );
  return rows[0] ?? null;
}

export async function findUserById(id: number): Promise<UserRow | null> {
  const [rows] = await pool.query<UserRow[]>(
    "SELECT * FROM users WHERE id = ? LIMIT 1",
    [id],
  );
  return rows[0] ?? null;
}

export async function createUser(params: {
  username: string;
  email: string;
  passwordHash: string;
}): Promise<number> {
  const [result] = await pool.query(
    "INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)",
    [params.username, params.email, params.passwordHash],
  );
  return (result as { insertId: number }).insertId;
}

export async function createSession(params: {
  id: string;
  userId: number;
  expiresAt: Date;
}): Promise<void> {
  await pool.query(
    "INSERT INTO sessions (id, user_id, expires_at) VALUES (?, ?, ?)",
    [params.id, params.userId, params.expiresAt],
  );
}

export async function findSession(id: string): Promise<(SessionRow & UserRow) | null> {
  const [rows] = await pool.query<(SessionRow & UserRow)[]>(
    `SELECT s.id as session_id, s.expires_at, u.*
     FROM sessions s
     JOIN users u ON s.user_id = u.id
     WHERE s.id = ? AND s.expires_at > NOW()
     LIMIT 1`,
    [id],
  );
  return rows[0] ?? null;
}

export async function deleteSession(id: string): Promise<void> {
  await pool.query("DELETE FROM sessions WHERE id = ?", [id]);
}

export async function createRepo(params: {
  ownerId: number;
  name: string;
  description?: string;
  visibility: "public" | "private";
}): Promise<number> {
  const [result] = await pool.query(
    "INSERT INTO repositories (owner_id, name, description, visibility) VALUES (?, ?, ?, ?)",
    [params.ownerId, params.name, params.description ?? null, params.visibility],
  );
  return (result as { insertId: number }).insertId;
}

export async function findRepo(ownerId: number, name: string): Promise<RepoRow | null> {
  const [rows] = await pool.query<RepoRow[]>(
    `SELECT r.*, u.username AS owner_username, u.display_name AS owner_display_name
     FROM repositories r JOIN users u ON r.owner_id = u.id
     WHERE r.owner_id = ? AND r.name = ?
     LIMIT 1`,
    [ownerId, name],
  );
  return rows[0] ?? null;
}

export async function findRepoByOwnerUsername(
  ownerUsername: string,
  repoName: string,
): Promise<RepoRow | null> {
  const [rows] = await pool.query<RepoRow[]>(
    `SELECT r.*, u.username AS owner_username, u.display_name AS owner_display_name
     FROM repositories r JOIN users u ON r.owner_id = u.id
     WHERE u.username = ? AND r.name = ?
     LIMIT 1`,
    [ownerUsername, repoName],
  );
  return rows[0] ?? null;
}

export async function listReposByOwner(ownerId: number): Promise<RepoRow[]> {
  const [rows] = await pool.query<RepoRow[]>(
    `SELECT r.*, u.username AS owner_username, u.display_name AS owner_display_name
     FROM repositories r JOIN users u ON r.owner_id = u.id
     WHERE r.owner_id = ?
     ORDER BY r.updated_at DESC`,
    [ownerId],
  );
  return rows;
}