use db::sqlx;
use model::{pull_request::PullRequestModel, repos::RepoModel};
use serde::Deserialize;
use session::Session;

use super::types::{
    IssuePullRequestResponse, IssueRepoResponse, issue_pr_response,
    issue_repo_response,
};
use crate::{AppService, error::AppError, session_user};

#[derive(Debug, Clone, Deserialize, utoipa::ToSchema)]
pub struct BindIssueRepo {
    pub repo_id: uuid::Uuid,
}

#[derive(Debug, Clone, Deserialize, utoipa::ToSchema)]
pub struct BindIssuePullRequest {
    pub pull_request_id: uuid::Uuid,
}

impl AppService {
    pub async fn issue_bind_repo(
        &self,
        ctx: &Session,
        wk_name: &str,
        number: i64,
        params: BindIssueRepo,
    ) -> Result<Vec<IssueRepoResponse>, AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_member(wk.id, user_uid).await?;
        let issue = self.issue_resolve(wk.id, number).await?;

        let repo = sqlx::query_as::<_, RepoModel>(
            "SELECT id, wk, name, description, default_branch, visibility, size_bytes, \
             is_archived, is_template, is_mirror, created_by, storage_path, created_at, updated_at, deleted_at \
             FROM repo WHERE id = $1 AND wk = $2 AND deleted_at IS NULL",
        )
        .bind(params.repo_id)
        .bind(wk.id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?
        .ok_or(AppError::RepoNotFound)?;

        let now = chrono::Utc::now();
        sqlx::query(
            "INSERT INTO issue_repo (issue, repo, created_at) VALUES ($1, $2, $3) \
             ON CONFLICT (issue, repo) DO NOTHING",
        )
        .bind(issue.id)
        .bind(repo.id)
        .bind(now)
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        sqlx::query(
            "INSERT INTO issue_event (id, issue, actor, event, from_value, to_value, created_at) \
             VALUES ($1, $2, $3, 'linked_repo', NULL, $4, $5)",
        )
        .bind(uuid::Uuid::now_v7())
        .bind(issue.id)
        .bind(user_uid)
        .bind(&repo.name)
        .bind(now)
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        self.issue_repos(issue.id).await
    }

    pub async fn issue_unbind_repo(
        &self,
        ctx: &Session,
        wk_name: &str,
        number: i64,
        repo_id: uuid::Uuid,
    ) -> Result<Vec<IssueRepoResponse>, AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_member(wk.id, user_uid).await?;
        let issue = self.issue_resolve(wk.id, number).await?;

        let repo = sqlx::query_as::<_, RepoModel>(
            "SELECT id, wk, name, description, default_branch, visibility, size_bytes, \
             is_archived, is_template, is_mirror, created_by, storage_path, created_at, updated_at, deleted_at \
             FROM repo WHERE id = $1 AND wk = $2",
        )
        .bind(repo_id)
        .bind(wk.id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?
        .ok_or(AppError::RepoNotFound)?;

        sqlx::query("DELETE FROM issue_repo WHERE issue = $1 AND repo = $2")
            .bind(issue.id)
            .bind(repo_id)
            .execute(self.db.writer())
            .await
            .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        sqlx::query(
            "INSERT INTO issue_event (id, issue, actor, event, from_value, to_value, created_at) \
             VALUES ($1, $2, $3, 'unlinked_repo', $4, NULL, $5)",
        )
        .bind(uuid::Uuid::now_v7())
        .bind(issue.id)
        .bind(user_uid)
        .bind(&repo.name)
        .bind(chrono::Utc::now())
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        self.issue_repos(issue.id).await
    }

    pub async fn issue_bind_pull_request(
        &self,
        ctx: &Session,
        wk_name: &str,
        number: i64,
        params: BindIssuePullRequest,
    ) -> Result<Vec<IssuePullRequestResponse>, AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_member(wk.id, user_uid).await?;
        let issue = self.issue_resolve(wk.id, number).await?;

        let pr = sqlx::query_as::<_, PullRequestModel>(
            "SELECT id, repo, number, title, body, state, draft, author, \
             source_repo, source_branch, source_sha, target_branch, target_sha, \
             merged_by, merged_at, closed_by, closed_at, created_at, updated_at, deleted_at \
             FROM pull_request WHERE id = $1 AND deleted_at IS NULL",
        )
        .bind(params.pull_request_id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?
        .ok_or(AppError::PullRequestNotFound)?;

        let now = chrono::Utc::now();
        sqlx::query(
            "INSERT INTO issue_pull_request (issue, pull_request, created_at) VALUES ($1, $2, $3) \
             ON CONFLICT (issue, pull_request) DO NOTHING",
        )
        .bind(issue.id)
        .bind(pr.id)
        .bind(now)
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        sqlx::query(
            "INSERT INTO issue_event (id, issue, actor, event, from_value, to_value, created_at) \
             VALUES ($1, $2, $3, 'linked_pull_request', NULL, $4, $5)",
        )
        .bind(uuid::Uuid::now_v7())
        .bind(issue.id)
        .bind(user_uid)
        .bind(format!("#{}", pr.number))
        .bind(now)
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        self.issue_pull_requests(issue.id).await
    }

    pub async fn issue_unbind_pull_request(
        &self,
        ctx: &Session,
        wk_name: &str,
        number: i64,
        pull_request_id: uuid::Uuid,
    ) -> Result<Vec<IssuePullRequestResponse>, AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_member(wk.id, user_uid).await?;
        let issue = self.issue_resolve(wk.id, number).await?;

        let pr = sqlx::query_as::<_, PullRequestModel>(
            "SELECT id, repo, number, title, body, state, draft, author, \
             source_repo, source_branch, source_sha, target_branch, target_sha, \
             merged_by, merged_at, closed_by, closed_at, created_at, updated_at, deleted_at \
             FROM pull_request WHERE id = $1",
        )
        .bind(pull_request_id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?
        .ok_or(AppError::PullRequestNotFound)?;

        sqlx::query("DELETE FROM issue_pull_request WHERE issue = $1 AND pull_request = $2")
            .bind(issue.id)
            .bind(pull_request_id)
            .execute(self.db.writer())
            .await
            .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        sqlx::query(
            "INSERT INTO issue_event (id, issue, actor, event, from_value, to_value, created_at) \
             VALUES ($1, $2, $3, 'unlinked_pull_request', $4, NULL, $5)",
        )
        .bind(uuid::Uuid::now_v7())
        .bind(issue.id)
        .bind(user_uid)
        .bind(format!("#{}", pr.number))
        .bind(chrono::Utc::now())
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        self.issue_pull_requests(issue.id).await
    }

    pub async fn issue_repos(
        &self,
        issue_id: uuid::Uuid,
    ) -> Result<Vec<IssueRepoResponse>, AppError> {
        let repos = sqlx::query_as::<_, RepoModel>(
            "SELECT r.id, r.wk, r.name, r.description, r.default_branch, r.visibility, r.size_bytes, \
             r.is_archived, r.is_template, r.is_mirror, r.created_by, r.storage_path, r.created_at, r.updated_at, r.deleted_at \
             FROM issue_repo ir \
             INNER JOIN repo r ON r.id = ir.repo \
             WHERE ir.issue = $1 AND r.deleted_at IS NULL \
             ORDER BY r.name ASC",
        )
        .bind(issue_id)
        .fetch_all(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(repos.into_iter().map(issue_repo_response).collect())
    }

    pub async fn issue_pull_requests(
        &self,
        issue_id: uuid::Uuid,
    ) -> Result<Vec<IssuePullRequestResponse>, AppError> {
        let prs = sqlx::query_as::<_, PullRequestModel>(
            "SELECT pr.id, pr.repo, pr.number, pr.title, pr.body, pr.state, pr.draft, pr.author, \
             pr.source_repo, pr.source_branch, pr.source_sha, pr.target_branch, pr.target_sha, \
             pr.merged_by, pr.merged_at, pr.closed_by, pr.closed_at, pr.created_at, pr.updated_at, pr.deleted_at \
             FROM issue_pull_request ip \
             INNER JOIN pull_request pr ON pr.id = ip.pull_request \
             WHERE ip.issue = $1 AND pr.deleted_at IS NULL \
             ORDER BY pr.created_at DESC",
        )
        .bind(issue_id)
        .fetch_all(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(prs.into_iter().map(issue_pr_response).collect())
    }
}