use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::{FromRow, PgPool};
use uuid::Uuid;

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct Designation {
    pub id: Uuid,
    pub name: String,
    pub code: Option<String>,
    pub level: Option<String>,
    pub department_id: Option<Uuid>,
    pub can_approve: bool,
    pub is_active: bool,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateDesignationPayload {
    pub name: String,
    pub level: Option<String>,
    pub department_id: Option<Uuid>,
    pub can_approve: Option<bool>,
    pub status: Option<String>, // ACTIVE | INACTIVE
}

pub struct DesignationRepository;

impl DesignationRepository {
    pub async fn create(pool: &PgPool, payload: CreateDesignationPayload) -> Result<Designation, sqlx::Error> {
        let is_active = payload.status.map(|s| s.to_uppercase() == "ACTIVE").unwrap_or(true);
        let can_approve = payload.can_approve.unwrap_or(false);

        sqlx::query_as::<_, Designation>(
            r#"
            INSERT INTO designations (name, level, department_id, is_active, can_approve)
            VALUES ($1, $2, $3, $4, $5)
            RETURNING id, name, code, level, department_id, can_approve, is_active, created_at, updated_at
            "#
        )
        .bind(payload.name)
        .bind(payload.level)
        .bind(payload.department_id)
        .bind(is_active)
        .bind(can_approve)
        .fetch_one(pool)
        .await
    }

    pub async fn update(pool: &PgPool, id: Uuid, payload: serde_json::Value) -> Result<Designation, sqlx::Error> {
        let name = payload.get("name").and_then(|v| v.as_str());
        let level = payload.get("level").map(|v| v.to_string());
        let department_id = payload.get("department_id").and_then(|v| v.as_str()).and_then(|s| Uuid::parse_str(s).ok());
        let can_approve = payload.get("can_approve").and_then(|v| v.as_bool());
        let status = payload.get("status").and_then(|v| v.as_str());
        let is_active = status.map(|s| s.to_uppercase() == "ACTIVE");

        sqlx::query_as::<_, Designation>(
            r#"
            UPDATE designations
            SET name = COALESCE($2, name),
                level = COALESCE($3, level),
                department_id = COALESCE($4, department_id),
                can_approve = COALESCE($5, can_approve),
                is_active = COALESCE($6, is_active),
                updated_at = NOW()
            WHERE id = $1
            RETURNING id, name, code, level, department_id, can_approve, is_active, created_at, updated_at
            "#
        )
        .bind(id)
        .bind(name)
        .bind(level)
        .bind(department_id)
        .bind(can_approve)
        .bind(is_active)
        .fetch_one(pool)
        .await
    }

    pub async fn delete(pool: &PgPool, id: Uuid) -> Result<(), sqlx::Error> {
        sqlx::query("DELETE FROM designations WHERE id = $1")
            .bind(id)
            .execute(pool)
            .await?;
        Ok(())
    }

    pub async fn list_all(pool: &PgPool) -> Result<Vec<Designation>, sqlx::Error> {
        sqlx::query_as::<_, Designation>(
            "SELECT id, name, code, level, department_id, can_approve, is_active, created_at, updated_at FROM designations ORDER BY name ASC"
        )
        .fetch_all(pool)
        .await
    }

    pub async fn list_by_department(pool: &PgPool, department_id: Uuid) -> Result<Vec<Designation>, sqlx::Error> {
        sqlx::query_as::<_, Designation>(
            "SELECT id, name, code, level, department_id, can_approve, is_active, created_at, updated_at FROM designations WHERE department_id = $1 ORDER BY level DESC"
        )
        .bind(department_id)
        .fetch_all(pool)
        .await
    }
}
