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

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct Department {
    pub id: Uuid,
    pub name: String,
    pub code: Option<String>,
    pub description: Option<String>,
    pub department_head: Option<String>,
    pub department_email: Option<String>,
    pub is_active: bool,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateDepartmentPayload {
    pub name: String,
    pub code: Option<String>,
    pub description: Option<String>,
    pub department_head: Option<String>,
    pub department_email: Option<String>,
    pub status: Option<String>,
}

pub struct DepartmentRepository;

impl DepartmentRepository {
    pub async fn create(pool: &PgPool, payload: CreateDepartmentPayload) -> Result<Department, sqlx::Error> {
        let is_active = payload.status.map(|s| s.to_uppercase() == "ACTIVE").unwrap_or(true);
        let code = payload.code.filter(|c| !c.is_empty()).map(|c| c.to_uppercase());

        sqlx::query_as::<_, Department>(
            r#"
            INSERT INTO departments (
                name, code, description, department_head, department_email, 
                is_active
            )
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING id, name, code, description, department_head, department_email, is_active, created_at, updated_at
            "#
        )
        .bind(payload.name)
        .bind(code)
        .bind(payload.description)
        .bind(payload.department_head)
        .bind(payload.department_email)
        .bind(is_active)
        .fetch_one(pool)
        .await
    }

    pub async fn update(pool: &PgPool, id: Uuid, payload: serde_json::Value) -> Result<Department, sqlx::Error> {
        let name = payload.get("name").and_then(|v| v.as_str());
        let code = payload.get("code").and_then(|v| v.as_str());
        let description = payload.get("description").map(|v| v.as_str().unwrap_or_default());
        let department_head = payload.get("department_head").map(|v| v.as_str().unwrap_or_default());
        let department_email = payload.get("department_email").map(|v| v.as_str().unwrap_or_default());
        let status = payload.get("status").and_then(|v| v.as_str());
        let is_active = status.map(|s| s.to_uppercase() == "ACTIVE");

        sqlx::query_as::<_, Department>(
            r#"
            UPDATE departments
            SET name = COALESCE($2, name),
                code = COALESCE($3, code),
                description = COALESCE($4, description),
                department_head = COALESCE($5, department_head),
                department_email = COALESCE($6, department_email),
                is_active = COALESCE($7, is_active),
                updated_at = NOW()
            WHERE id = $1
            RETURNING id, name, code, description, department_head, department_email, is_active, created_at, updated_at
            "#
        )
        .bind(id)
        .bind(name)
        .bind(code.map(|c| c.to_uppercase()))
        .bind(description)
        .bind(department_head)
        .bind(department_email)
        .bind(is_active)
        .fetch_one(pool)
        .await
    }

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

    pub async fn list(pool: &PgPool) -> Result<Vec<Department>, sqlx::Error> {
        sqlx::query_as::<_, Department>(
            "SELECT id, name, code, description, department_head, department_email, is_active, created_at, updated_at FROM departments ORDER BY name ASC"
        )
        .fetch_all(pool)
        .await
    }

    pub async fn get_by_id(pool: &PgPool, id: Uuid) -> Result<Option<Department>, sqlx::Error> {
        sqlx::query_as::<_, Department>(
            "SELECT id, name, code, description, department_head, department_email, is_active, created_at, updated_at FROM departments WHERE id = $1"
        )
        .bind(id)
        .fetch_optional(pool)
        .await
    }
}
