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

#[derive(Debug, Serialize, Deserialize, FromRow, Clone)]
pub struct JobSeekerDocument {
    pub id: Uuid,
    pub job_seeker_id: Uuid,
    pub document_type: String,
    pub file_name: String,
    pub file_url: String,
    pub file_size: i64,
    pub mime_type: String,
    pub created_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateJobSeekerDocumentPayload {
    pub document_type: String,
    pub file_name: String,
    pub file_size: i64,
    pub mime_type: String,
}

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct JobSeekerProfile {
    pub id: Uuid,
    pub user_id: Uuid,
    pub full_name: Option<String>,
    pub location: Option<String>,
    pub summary: Option<String>,
    pub experience_years: Option<i32>,
    pub skills: Option<Vec<String>>,
    pub resume_url: Option<String>,
    pub active_application_count: i32,
    pub status: String,
    pub bio: Option<String>,
    pub custom_data: Option<serde_json::Value>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct UpsertJobSeekerProfilePayload {
    pub full_name: Option<String>,
    pub location: Option<String>,
    pub summary: Option<String>,
    pub experience_years: Option<i32>,
    pub skills: Option<Vec<String>>,
    pub resume_url: Option<String>,
    pub bio: Option<String>,
    pub custom_data: Option<serde_json::Value>,
}

pub struct JobSeekerRepository;

impl JobSeekerRepository {
    pub async fn get_by_user_id(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<Option<JobSeekerProfile>, sqlx::Error> {
        let profile = sqlx::query_as::<_, JobSeekerProfile>(
            r#"
            SELECT
                id, user_id, full_name, location, summary, experience_years,
                skills, resume_url, active_application_count, status, bio, custom_data,
                created_at, updated_at
            FROM job_seeker_profiles
            WHERE user_id = $1
            "#,
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await?;

        Ok(profile)
    }

    pub async fn upsert(
        pool: &PgPool,
        user_id: Uuid,
        payload: UpsertJobSeekerProfilePayload,
    ) -> Result<JobSeekerProfile, sqlx::Error> {
        let profile = sqlx::query_as::<_, JobSeekerProfile>(
            r#"
            INSERT INTO job_seeker_profiles (
                user_id, full_name, location, summary, experience_years,
                skills, resume_url, bio, custom_data
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
            ON CONFLICT (user_id) DO UPDATE SET
                full_name = EXCLUDED.full_name,
                location = EXCLUDED.location,
                summary = EXCLUDED.summary,
                experience_years = EXCLUDED.experience_years,
                skills = EXCLUDED.skills,
                resume_url = EXCLUDED.resume_url,
                bio = EXCLUDED.bio,
                custom_data = EXCLUDED.custom_data,
                updated_at = NOW()
            RETURNING
                id, user_id, full_name, location, summary, experience_years,
                skills, resume_url, active_application_count, status, bio, custom_data,
                created_at, updated_at
            "#,
        )
        .bind(user_id)
        .bind(payload.full_name)
        .bind(payload.location)
        .bind(payload.summary)
        .bind(payload.experience_years.unwrap_or(0))
        .bind(payload.skills.unwrap_or_default())
        .bind(payload.resume_url)
        .bind(payload.bio)
        .bind(payload.custom_data)
        .fetch_one(pool)
        .await?;

        Ok(profile)
    }

    pub async fn update_active_application_count(
        pool: &PgPool,
        id: Uuid,
        delta: i32,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE job_seeker_profiles SET active_application_count = active_application_count + $1 WHERE id = $2",
        )
        .bind(delta)
        .bind(id)
        .execute(pool)
        .await?;
        Ok(())
    }

    pub async fn submit_for_verification(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<JobSeekerProfile, sqlx::Error> {
        let profile = sqlx::query_as::<_, JobSeekerProfile>(
            r#"
            UPDATE job_seeker_profiles
            SET status = 'PENDING_REVIEW', updated_at = NOW()
            WHERE user_id = $1
            RETURNING
                id, user_id, full_name, location, summary, experience_years,
                skills, resume_url, active_application_count, status, bio, custom_data,
                created_at, updated_at
            "#,
        )
        .bind(user_id)
        .fetch_one(pool)
        .await?;

        Ok(profile)
    }

    pub async fn create_document(
        pool: &PgPool,
        job_seeker_id: Uuid,
        payload: CreateJobSeekerDocumentPayload,
        file_url: String,
    ) -> Result<JobSeekerDocument, sqlx::Error> {
        let doc = sqlx::query_as::<_, JobSeekerDocument>(
            r#"
            INSERT INTO job_seeker_documents (
                job_seeker_id, document_type, file_name, file_url, file_size, mime_type
            )
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING
                id, job_seeker_id, document_type, file_name, file_url, file_size, mime_type, created_at
            "#,
        )
        .bind(job_seeker_id)
        .bind(payload.document_type)
        .bind(payload.file_name)
        .bind(file_url)
        .bind(payload.file_size)
        .bind(payload.mime_type)
        .fetch_one(pool)
        .await?;

        Ok(doc)
    }

    pub async fn list_documents(
        pool: &PgPool,
        job_seeker_id: Uuid,
    ) -> Result<Vec<JobSeekerDocument>, sqlx::Error> {
        let docs = sqlx::query_as::<_, JobSeekerDocument>(
            r#"
            SELECT id, job_seeker_id, document_type, file_name, file_url, file_size, mime_type, created_at
            FROM job_seeker_documents
            WHERE job_seeker_id = $1
            ORDER BY created_at DESC
            "#,
        )
        .bind(job_seeker_id)
        .fetch_all(pool)
        .await?;

        Ok(docs)
    }

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