use db::sqlx;
use model::issues::MilestoneModel;
use serde::Deserialize;
use session::Session;

use super::types::{MilestoneResponse, milestone_response};
use crate::{AppService, error::AppError, session_user};

#[derive(Debug, Clone, Deserialize, utoipa::ToSchema)]
pub struct CreateMilestone {
    pub title: String,
    pub description: Option<String>,
    pub due_at: Option<String>,
}

#[derive(Debug, Clone, Deserialize, utoipa::ToSchema)]
pub struct UpdateMilestone {
    pub title: Option<String>,
    pub description: Option<String>,
    pub state: Option<String>,
    pub due_at: Option<String>,
}

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

impl AppService {
    pub async fn milestone_create(
        &self,
        ctx: &Session,
        wk_name: &str,
        params: CreateMilestone,
    ) -> Result<MilestoneResponse, AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_admin(wk.id, user_uid).await?;

        let title = params.title.trim();
        if title.is_empty() {
            return Err(AppError::BadRequest(
                "milestone title is required".to_string(),
            ));
        }

        let id = uuid::Uuid::now_v7();
        let now = chrono::Utc::now();
        let due_at = params.due_at.and_then(|d| {
            chrono::DateTime::parse_from_rfc3339(&d)
                .ok()
                .map(|dt| dt.to_utc())
        });

        let milestone = sqlx::query_as::<_, MilestoneModel>(
            "INSERT INTO milestone (id, wk, title, description, state, due_at, created_at, updated_at) \
             VALUES ($1, $2, $3, $4, 'open', $5, $6, $6) \
             RETURNING id, wk, title, description, state, due_at, closed_at, created_at, updated_at",
        )
        .bind(id)
        .bind(wk.id)
        .bind(title)
        .bind(&params.description)
        .bind(due_at)
        .bind(now)
        .fetch_one(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(milestone_response(milestone))
    }

    pub async fn milestone_list(
        &self,
        ctx: &Session,
        wk_name: &str,
    ) -> Result<Vec<MilestoneResponse>, 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 milestones = sqlx::query_as::<_, MilestoneModel>(
            "SELECT id, wk, title, description, state, due_at, closed_at, created_at, updated_at \
             FROM milestone WHERE wk = $1 ORDER BY created_at ASC",
        )
        .bind(wk.id)
        .fetch_all(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(milestones.into_iter().map(milestone_response).collect())
    }

    pub async fn milestone_update(
        &self,
        ctx: &Session,
        wk_name: &str,
        milestone_id: uuid::Uuid,
        params: UpdateMilestone,
    ) -> Result<MilestoneResponse, AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_admin(wk.id, user_uid).await?;

        let milestone = sqlx::query_as::<_, MilestoneModel>(
            "SELECT id, wk, title, description, state, due_at, closed_at, created_at, updated_at \
             FROM milestone WHERE id = $1 AND wk = $2",
        )
        .bind(milestone_id)
        .bind(wk.id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?
        .ok_or(AppError::MilestoneNotFound)?;

        let title = params.title.unwrap_or_else(|| milestone.title.clone());
        let description = params.description.unwrap_or_else(|| {
            milestone.description.clone().unwrap_or_default()
        });
        let state = params.state.unwrap_or_else(|| milestone.state.clone());
        let due_at = params
            .due_at
            .and_then(|d| {
                chrono::DateTime::parse_from_rfc3339(&d)
                    .ok()
                    .map(|dt| dt.to_utc())
            })
            .or(milestone.due_at);
        let closed_at = if state == "closed" && milestone.state != "closed" {
            Some(chrono::Utc::now())
        } else {
            milestone.closed_at
        };

        let now = chrono::Utc::now();
        let updated = sqlx::query_as::<_, MilestoneModel>(
            "UPDATE milestone SET title = $1, description = $2, state = $3, due_at = $4, closed_at = $5, updated_at = $6 \
             WHERE id = $7 \
             RETURNING id, wk, title, description, state, due_at, closed_at, created_at, updated_at",
        )
        .bind(title)
        .bind(description)
        .bind(state)
        .bind(due_at)
        .bind(closed_at)
        .bind(now)
        .bind(milestone_id)
        .fetch_one(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(milestone_response(updated))
    }

    pub async fn milestone_delete(
        &self,
        ctx: &Session,
        wk_name: &str,
        milestone_id: uuid::Uuid,
    ) -> Result<(), AppError> {
        let user_uid = session_user(ctx)?;
        let wk = self.workspace_resolve(wk_name).await?;
        self.workspace_require_admin(wk.id, user_uid).await?;

        let exists = sqlx::query_scalar::<_, bool>(
            "SELECT EXISTS(SELECT 1 FROM milestone WHERE id = $1 AND wk = $2)",
        )
        .bind(milestone_id)
        .bind(wk.id)
        .fetch_one(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        if !exists {
            return Err(AppError::MilestoneNotFound);
        }

        sqlx::query("DELETE FROM issue_milestone WHERE milestone = $1")
            .bind(milestone_id)
            .execute(self.db.writer())
            .await
            .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        sqlx::query("DELETE FROM milestone WHERE id = $1")
            .bind(milestone_id)
            .execute(self.db.writer())
            .await
            .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(())
    }

    pub async fn issue_set_milestone(
        &self,
        ctx: &Session,
        wk_name: &str,
        number: i64,
        params: SetIssueMilestone,
    ) -> Result<Option<MilestoneResponse>, 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 milestone = sqlx::query_as::<_, MilestoneModel>(
            "SELECT id, wk, title, description, state, due_at, closed_at, created_at, updated_at \
             FROM milestone WHERE id = $1 AND wk = $2",
        )
        .bind(params.milestone_id)
        .bind(wk.id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?
        .ok_or(AppError::MilestoneNotFound)?;

        let now = chrono::Utc::now();
        sqlx::query("DELETE FROM issue_milestone WHERE issue = $1")
            .bind(issue.id)
            .execute(self.db.writer())
            .await
            .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        sqlx::query(
            "INSERT INTO issue_milestone (issue, milestone, created_at) VALUES ($1, $2, $3)",
        )
        .bind(issue.id)
        .bind(milestone.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, 'milestone_changed', NULL, $4, $5)",
        )
        .bind(uuid::Uuid::now_v7())
        .bind(issue.id)
        .bind(user_uid)
        .bind(&milestone.title)
        .bind(now)
        .execute(self.db.writer())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(Some(milestone_response(milestone)))
    }

    pub async fn issue_clear_milestone(
        &self,
        ctx: &Session,
        wk_name: &str,
        number: i64,
    ) -> Result<(), 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 old_milestone = self.issue_milestone(issue.id).await?;
        if let Some(old) = &old_milestone {
            sqlx::query("DELETE FROM issue_milestone WHERE issue = $1")
                .bind(issue.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, 'milestone_removed', $4, NULL, $5)",
            )
            .bind(uuid::Uuid::now_v7())
            .bind(issue.id)
            .bind(user_uid)
            .bind(&old.title)
            .bind(chrono::Utc::now())
            .execute(self.db.writer())
            .await
            .map_err(|e| AppError::DatabaseError(e.to_string()))?;
        }

        Ok(())
    }

    pub async fn issue_milestone(
        &self,
        issue_id: uuid::Uuid,
    ) -> Result<Option<MilestoneResponse>, AppError> {
        let milestone = sqlx::query_as::<_, MilestoneModel>(
            "SELECT m.id, m.wk, m.title, m.description, m.state, m.due_at, m.closed_at, m.created_at, m.updated_at \
             FROM issue_milestone im \
             INNER JOIN milestone m ON m.id = im.milestone \
             WHERE im.issue = $1",
        )
        .bind(issue_id)
        .fetch_optional(self.db.reader())
        .await
        .map_err(|e| AppError::DatabaseError(e.to_string()))?;

        Ok(milestone.map(milestone_response))
    }
}