//! Git repository CRUD and lookup queries.

use chrono::{DateTime, Utc};
use sqlx::{FromRow, PgPool};

use super::models::DbGitRepo;
use super::{GitRepoId, ProjectId, UserId, Visibility};
use crate::error::Result;

/// A public repo joined with its owner's username, for the explore page.
#[derive(FromRow)]
pub struct PublicRepoWithOwner {
    pub name: String,
    pub description: String,
    pub created_at: DateTime<Utc>,
    pub owner_username: String,
}

/// Register a new git repository for a user (default visibility: public).
///
/// Validates `name` against `validate_git_repo_name` as a defense-in-depth
/// backstop — the SSH dispatch path and HTTP smart-protocol path both call
/// this with names supplied by untrusted remote git clients.
#[tracing::instrument(skip_all)]
pub async fn create_repo(pool: &PgPool, user_id: UserId, name: &str) -> Result<DbGitRepo> {
    crate::validation::validate_git_repo_name(name)?;
    let repo = sqlx::query_as::<_, DbGitRepo>(
        r#"
        INSERT INTO git_repos (user_id, name)
        VALUES ($1, $2)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(name)
    .fetch_one(pool)
    .await?;

    Ok(repo)
}

/// Register a new git repository with explicit visibility.
#[tracing::instrument(skip_all)]
pub async fn create_repo_with_visibility(
    pool: &PgPool,
    user_id: UserId,
    name: &str,
    visibility: Visibility,
) -> Result<DbGitRepo> {
    crate::validation::validate_git_repo_name(name)?;
    let repo = sqlx::query_as::<_, DbGitRepo>(
        r#"
        INSERT INTO git_repos (user_id, name, visibility)
        VALUES ($1, $2, $3)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(name)
    .bind(visibility)
    .fetch_one(pool)
    .await?;

    Ok(repo)
}

/// Look up a repo by its primary key. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_repo_by_id(pool: &PgPool, repo_id: GitRepoId) -> Result<Option<DbGitRepo>> {
    let repo = sqlx::query_as::<_, DbGitRepo>(
        "SELECT * FROM git_repos WHERE id = $1",
    )
    .bind(repo_id)
    .fetch_optional(pool)
    .await?;

    Ok(repo)
}

/// Look up a repo by its owning user and bare name. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_repo_by_user_and_name(
    pool: &PgPool,
    user_id: UserId,
    name: &str,
) -> Result<Option<DbGitRepo>> {
    let repo = sqlx::query_as::<_, DbGitRepo>(
        "SELECT * FROM git_repos WHERE user_id = $1 AND name = $2",
    )
    .bind(user_id)
    .bind(name)
    .fetch_optional(pool)
    .await?;

    Ok(repo)
}

/// List all repos owned by a user, newest first.
#[tracing::instrument(skip_all)]
pub async fn get_repos_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbGitRepo>> {
    let repos = sqlx::query_as::<_, DbGitRepo>(
        "SELECT * FROM git_repos WHERE user_id = $1 ORDER BY created_at DESC LIMIT 500",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(repos)
}

/// List public repos owned by a user, newest first.
#[tracing::instrument(skip_all)]
pub async fn get_public_repos_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbGitRepo>> {
    let repos = sqlx::query_as::<_, DbGitRepo>(
        "SELECT * FROM git_repos WHERE user_id = $1 AND visibility = 'public' ORDER BY created_at DESC LIMIT 500",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(repos)
}

/// List all repos linked to a specific project.
#[tracing::instrument(skip_all)]
pub async fn get_repos_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbGitRepo>> {
    let repos = sqlx::query_as::<_, DbGitRepo>(
        "SELECT * FROM git_repos WHERE project_id = $1 ORDER BY name ASC",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(repos)
}

/// Link a repo to a project (sets `project_id`).
#[tracing::instrument(skip_all)]
pub async fn link_repo_to_project(
    pool: &PgPool,
    repo_id: GitRepoId,
    project_id: ProjectId,
) -> Result<()> {
    sqlx::query("UPDATE git_repos SET project_id = $2 WHERE id = $1")
        .bind(repo_id)
        .bind(project_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Unlink a repo from its project (sets `project_id = NULL`).
#[tracing::instrument(skip_all)]
pub async fn unlink_repo_from_project(pool: &PgPool, repo_id: GitRepoId) -> Result<()> {
    sqlx::query("UPDATE git_repos SET project_id = NULL WHERE id = $1")
        .bind(repo_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Update the visibility of a repo.
#[tracing::instrument(skip_all)]
pub async fn update_visibility(
    pool: &PgPool,
    repo_id: GitRepoId,
    visibility: Visibility,
) -> Result<()> {
    sqlx::query("UPDATE git_repos SET visibility = $2 WHERE id = $1")
        .bind(repo_id)
        .bind(visibility)
        .execute(pool)
        .await?;

    Ok(())
}

/// Update description and visibility in one call.
#[tracing::instrument(skip_all)]
pub async fn update_repo_settings(
    pool: &PgPool,
    repo_id: GitRepoId,
    description: &str,
    visibility: Visibility,
) -> Result<()> {
    sqlx::query("UPDATE git_repos SET description = $2, visibility = $3 WHERE id = $1")
        .bind(repo_id)
        .bind(description)
        .bind(visibility)
        .execute(pool)
        .await?;

    Ok(())
}

/// Delete a repo from the database (leaves files on disk for safety).
#[tracing::instrument(skip_all)]
pub async fn delete_repo(pool: &PgPool, repo_id: GitRepoId) -> Result<()> {
    sqlx::query("DELETE FROM git_repos WHERE id = $1")
        .bind(repo_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// List all public repos across all users, newest first, with owner username.
#[tracing::instrument(skip_all)]
pub async fn get_all_public_repos(
    pool: &PgPool,
    limit: i64,
    offset: i64,
) -> Result<Vec<PublicRepoWithOwner>> {
    let repos = sqlx::query_as::<_, PublicRepoWithOwner>(
        r#"
        SELECT g.name, g.description, g.created_at, u.username AS owner_username
        FROM git_repos g
        JOIN users u ON u.id = g.user_id
        WHERE g.visibility = 'public'
        ORDER BY g.created_at DESC
        LIMIT $1 OFFSET $2
        "#,
    )
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    Ok(repos)
}

/// Count all public repos (for pagination).
#[tracing::instrument(skip_all)]
pub async fn count_all_public_repos(pool: &PgPool) -> Result<i64> {
    let count: (i64,) = sqlx::query_as(
        "SELECT COUNT(*) FROM git_repos WHERE visibility = 'public'",
    )
    .fetch_one(pool)
    .await?;

    Ok(count.0)
}