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

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct TutorProfile {
    pub id: Uuid,
    pub user_role_profile_id: Uuid,
    pub subjects: Vec<String>,
    pub board_types: Vec<String>,
    pub qualification: Option<String>,
    pub teaches_online: bool,
    pub teaches_offline: bool,
    pub experience_years: Option<i32>,
    pub hourly_rate_inr: Option<i32>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct UpsertTutorProfilePayload {
    pub subjects: Vec<String>,
    pub board_types: Vec<String>,
    pub qualification: Option<String>,
    pub teaches_online: bool,
    pub teaches_offline: bool,
    pub experience_years: Option<i32>,
    pub hourly_rate_inr: Option<i32>,
}

pub struct TutorRepository;

impl TutorRepository {
    pub async fn get_by_user_id(pool: &PgPool, user_id: Uuid) -> Result<Option<TutorProfile>, sqlx::Error> {
        sqlx::query_as::<_, TutorProfile>(
            r#"SELECT tp.id, tp.user_role_profile_id, tp.subjects, tp.board_types, tp.qualification,
                      tp.teaches_online, tp.teaches_offline, tp.experience_years, tp.hourly_rate_inr,
                      tp.created_at, tp.updated_at
               FROM tutor_profiles tp
               INNER JOIN user_role_profiles urp ON urp.id = tp.user_role_profile_id
               WHERE urp.user_id = $1 AND urp.role_key = 'tutor'"#,
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await
    }

    pub async fn upsert_by_user_id(pool: &PgPool, user_id: Uuid, p: UpsertTutorProfilePayload) -> Result<TutorProfile, sqlx::Error> {
        let user_role_profile = sqlx::query_as::<_, (Uuid,)>(
            r#"SELECT id FROM user_role_profiles WHERE user_id = $1 AND role_key = 'tutor'"#,
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await?
        .ok_or(sqlx::Error::RowNotFound)?;

        sqlx::query_as::<_, TutorProfile>(
            r#"INSERT INTO tutor_profiles (user_role_profile_id, subjects, board_types, qualification,
                                         teaches_online, teaches_offline, experience_years, hourly_rate_inr)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
               ON CONFLICT (user_role_profile_id) DO UPDATE SET
                   subjects = COALESCE(EXCLUDED.subjects, tutor_profiles.subjects),
                   board_types = COALESCE(EXCLUDED.board_types, tutor_profiles.board_types),
                   qualification = EXCLUDED.qualification,
                   teaches_online = EXCLUDED.teaches_online,
                   teaches_offline = EXCLUDED.teaches_offline,
                   experience_years = EXCLUDED.experience_years,
                   hourly_rate_inr = EXCLUDED.hourly_rate_inr,
                   updated_at = NOW()
               RETURNING id, user_role_profile_id, subjects, board_types, qualification,
                         teaches_online, teaches_offline, experience_years, hourly_rate_inr,
                         created_at, updated_at"#,
        )
        .bind(user_role_profile.0)
        .bind(&p.subjects)
        .bind(&p.board_types)
        .bind(&p.qualification)
        .bind(p.teaches_online)
        .bind(p.teaches_offline)
        .bind(p.experience_years)
        .bind(p.hourly_rate_inr)
        .fetch_one(pool)
        .await
    }

    pub async fn get_by_user_role_id(pool: &PgPool, user_role_profile_id: Uuid) -> Result<Option<TutorProfile>, sqlx::Error> {
        sqlx::query_as::<_, TutorProfile>(
            r#"SELECT id, user_role_profile_id, subjects, board_types, qualification,
                      teaches_online, teaches_offline, experience_years, hourly_rate_inr,
                      created_at, updated_at
               FROM tutor_profiles WHERE user_role_profile_id = $1"#,
        )
        .bind(user_role_profile_id)
        .fetch_optional(pool)
        .await
    }

    pub async fn upsert(pool: &PgPool, user_role_profile_id: Uuid, p: UpsertTutorProfilePayload) -> Result<TutorProfile, sqlx::Error> {
        sqlx::query_as::<_, TutorProfile>(
            r#"INSERT INTO tutor_profiles (user_role_profile_id, subjects, board_types, qualification,
                                         teaches_online, teaches_offline, experience_years, hourly_rate_inr)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
               ON CONFLICT (user_role_profile_id) DO UPDATE SET
                   subjects = COALESCE(EXCLUDED.subjects, tutor_profiles.subjects),
                   board_types = COALESCE(EXCLUDED.board_types, tutor_profiles.board_types),
                   qualification = EXCLUDED.qualification,
                   teaches_online = EXCLUDED.teaches_online,
                   teaches_offline = EXCLUDED.teaches_offline,
                   experience_years = EXCLUDED.experience_years,
                   hourly_rate_inr = EXCLUDED.hourly_rate_inr,
                   updated_at = NOW()
               RETURNING id, user_role_profile_id, subjects, board_types, qualification,
                         teaches_online, teaches_offline, experience_years, hourly_rate_inr,
                         created_at, updated_at"#,
        )
        .bind(user_role_profile_id)
        .bind(&p.subjects)
        .bind(&p.board_types)
        .bind(&p.qualification)
        .bind(p.teaches_online)
        .bind(p.teaches_offline)
        .bind(p.experience_years)
        .bind(p.hourly_rate_inr)
        .fetch_one(pool)
        .await
    }
}
