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

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct Employee {
    pub id: Uuid,
    pub first_name: String,
    pub last_name: String,
    pub email: String,
    pub phone: Option<String>,
    pub password_hash: String,
    pub employee_code: Option<String>,
    pub department_id: Option<Uuid>,
    pub designation_id: Option<Uuid>,
    pub role_code: String,
    pub status: String,
    pub joining_date: NaiveDate,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct EmployeeSession {
    pub id: Uuid,
    pub employee_id: Uuid,
    pub token_hash: String,
    pub expires_at: DateTime<Utc>,
    pub revoked: bool,
    pub created_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateEmployeePayload {
    pub first_name: String,
    pub last_name: String,
    pub email: String,
    pub phone: Option<String>,
    pub password_hash: String,
    pub department_id: Option<Uuid>,
    pub designation_id: Option<Uuid>,
    pub role_code: String,
}

pub struct EmployeeRepository;

impl EmployeeRepository {
    pub async fn create(pool: &PgPool, payload: CreateEmployeePayload) -> Result<Employee, sqlx::Error> {
        let level_code = payload.role_code.clone();
        sqlx::query_as::<_, Employee>(
            r#"
            INSERT INTO employees (first_name, last_name, email, phone, password_hash, department_id, designation_id, role_code)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
            RETURNING id, first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code, status, joining_date, created_at, updated_at
            "#
        )
        .bind(payload.first_name)
        .bind(payload.last_name)
        .bind(payload.email.to_lowercase())
        .bind(payload.phone)
        .bind(payload.password_hash)
        .bind(payload.department_id)
        .bind(payload.designation_id)
        .bind(level_code)
        .fetch_one(pool)
        .await
    }

    pub async fn create_with_code(pool: &PgPool, payload: CreateEmployeePayload, employee_code: Option<String>) -> Result<Employee, sqlx::Error> {
        let role_code = payload.role_code.clone();
        sqlx::query_as::<_, Employee>(
            r#"
            INSERT INTO employees (first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code)
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
            RETURNING id, first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code, status, joining_date, created_at, updated_at
            "#
        )
        .bind(payload.first_name)
        .bind(payload.last_name)
        .bind(payload.email.to_lowercase())
        .bind(payload.phone)
        .bind(payload.password_hash)
        .bind(employee_code)
        .bind(payload.department_id)
        .bind(payload.designation_id)
        .bind(role_code)
        .fetch_one(pool)
        .await
    }

    pub async fn update(
        pool: &PgPool,
        id: Uuid,
        first_name: Option<String>,
        last_name: Option<String>,
        email: Option<String>,
        department_id: Option<Uuid>,
        designation_id: Option<Uuid>,
        role_code: Option<String>,
        status: Option<String>,
    ) -> Result<Employee, sqlx::Error> {
        sqlx::query_as::<_, Employee>(
            r#"
            UPDATE employees
            SET 
                first_name = COALESCE($1, first_name),
                last_name = COALESCE($2, last_name),
                email = COALESCE($3, email),
                department_id = COALESCE($4, department_id),
                designation_id = COALESCE($5, designation_id),
                role_code = COALESCE($6, role_code),
                status = COALESCE($7, status),
                updated_at = NOW()
            WHERE id = $8
            RETURNING id, first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code, status, joining_date, created_at, updated_at
            "#
        )
        .bind(first_name)
        .bind(last_name)
        .bind(email.map(|e| e.to_lowercase()))
        .bind(department_id)
        .bind(designation_id)
        .bind(role_code)
        .bind(status)
        .bind(id)
        .fetch_one(pool)
        .await
    }

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

    pub async fn get_by_id(pool: &PgPool, id: Uuid) -> Result<Option<Employee>, sqlx::Error> {
        sqlx::query_as::<_, Employee>(
            "SELECT id, first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code, status, joining_date, created_at, updated_at FROM employees WHERE id = $1"
        )
        .bind(id)
        .fetch_optional(pool)
        .await
    }

    pub async fn get_by_email(pool: &PgPool, email: &str) -> Result<Option<Employee>, sqlx::Error> {
        sqlx::query_as::<_, Employee>(
            "SELECT id, first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code, status, joining_date, created_at, updated_at FROM employees WHERE email = $1"
        )
        .bind(email.to_lowercase())
        .fetch_optional(pool)
        .await
    }

    pub async fn list(pool: &PgPool, q: Option<String>) -> Result<Vec<Employee>, sqlx::Error> {
        let search = q.unwrap_or_default().to_lowercase();
        sqlx::query_as::<_, Employee>(
            r#"
            SELECT id, first_name, last_name, email, phone, password_hash, employee_code, department_id, designation_id, role_code, status, joining_date, created_at, updated_at 
            FROM employees 
            WHERE ($1 = '' OR LOWER(first_name) LIKE '%' || $1 || '%' OR LOWER(last_name) LIKE '%' || $1 || '%' OR LOWER(email) LIKE '%' || $1 || '%')
            ORDER BY last_name, first_name
            "#
        )
        .bind(search)
        .fetch_all(pool)
        .await
    }

    pub async fn store_session(
        pool: &PgPool,
        employee_id: Uuid,
        token_hash: &str,
        expires_at: DateTime<Utc>,
    ) -> Result<EmployeeSession, sqlx::Error> {
        sqlx::query_as::<_, EmployeeSession>(
            r#"
            INSERT INTO employee_sessions (employee_id, token_hash, expires_at)
            VALUES ($1, $2, $3)
            RETURNING id, employee_id, token_hash, expires_at, revoked, created_at
            "#
        )
        .bind(employee_id)
        .bind(token_hash)
        .bind(expires_at)
        .fetch_one(pool)
        .await
    }

    pub async fn get_valid_session_by_token(
        pool: &PgPool,
        token_hash: &str,
    ) -> Result<Option<EmployeeSession>, sqlx::Error> {
        sqlx::query_as::<_, EmployeeSession>(
            r#"
            SELECT id, employee_id, token_hash, expires_at, revoked, created_at
            FROM employee_sessions
            WHERE token_hash = $1 AND revoked = false AND expires_at > NOW()
            "#
        )
        .bind(token_hash)
        .fetch_optional(pool)
        .await
    }

    pub async fn revoke_session(pool: &PgPool, session_id: Uuid) -> Result<(), sqlx::Error> {
        sqlx::query("UPDATE employee_sessions SET revoked = true WHERE id = $1")
            .bind(session_id)
            .execute(pool)
            .await?;
        Ok(())
    }

    pub async fn change_password(
        pool: &PgPool,
        id: Uuid,
        password_hash: &str,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            r#"
            UPDATE employees
            SET password_hash = $2, updated_at = NOW()
            WHERE id = $1
            "#
        )
        .bind(id)
        .bind(password_hash)
        .execute(pool)
        .await?;
        Ok(())
    }
}
