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

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct CateringServiceProfile {
    pub id: Uuid,
    pub user_role_profile_id: Uuid,
    pub business_name: Option<String>,
    pub cuisine_types: Vec<String>,
    pub event_types: Vec<String>,
    pub min_guests: Option<i32>,
    pub max_guests: Option<i32>,
    pub has_setup_team: bool,
    pub has_serving_staff: bool,
    pub price_per_head_inr: Option<i32>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct UpsertCateringServiceProfilePayload {
    pub business_name: Option<String>,
    pub cuisine_types: Vec<String>,
    pub event_types: Vec<String>,
    pub min_guests: Option<i32>,
    pub max_guests: Option<i32>,
    pub has_setup_team: bool,
    pub has_serving_staff: bool,
    pub price_per_head_inr: Option<i32>,
}

pub struct CateringServiceRepository;

impl CateringServiceRepository {
    pub async fn get_by_user_id(pool: &PgPool, user_id: Uuid) -> Result<Option<CateringServiceProfile>, sqlx::Error> {
        sqlx::query_as::<_, CateringServiceProfile>(
            r#"SELECT csp.id, csp.user_role_profile_id, csp.business_name, csp.cuisine_types, csp.event_types,
                      csp.min_guests, csp.max_guests, csp.has_setup_team, csp.has_serving_staff,
                      csp.price_per_head_inr, csp.created_at, csp.updated_at
               FROM catering_service_profiles csp
               INNER JOIN user_role_profiles urp ON urp.id = csp.user_role_profile_id
               WHERE urp.user_id = $1 AND urp.role_key = 'catering_service'"#,
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await
    }

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

        sqlx::query_as::<_, CateringServiceProfile>(
            r#"INSERT INTO catering_service_profiles (user_role_profile_id, business_name, cuisine_types, event_types,
                                                    min_guests, max_guests, has_setup_team, has_serving_staff, price_per_head_inr)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
               ON CONFLICT (user_role_profile_id) DO UPDATE SET
                   business_name = EXCLUDED.business_name,
                   cuisine_types = COALESCE(EXCLUDED.cuisine_types, catering_service_profiles.cuisine_types),
                   event_types = COALESCE(EXCLUDED.event_types, catering_service_profiles.event_types),
                   min_guests = EXCLUDED.min_guests,
                   max_guests = EXCLUDED.max_guests,
                   has_setup_team = EXCLUDED.has_setup_team,
                   has_serving_staff = EXCLUDED.has_serving_staff,
                   price_per_head_inr = EXCLUDED.price_per_head_inr,
                   updated_at = NOW()
               RETURNING id, user_role_profile_id, business_name, cuisine_types, event_types,
                         min_guests, max_guests, has_setup_team, has_serving_staff,
                         price_per_head_inr, created_at, updated_at"#,
        )
        .bind(user_role_profile.0)
        .bind(&p.business_name)
        .bind(&p.cuisine_types)
        .bind(&p.event_types)
        .bind(p.min_guests)
        .bind(p.max_guests)
        .bind(p.has_setup_team)
        .bind(p.has_serving_staff)
        .bind(p.price_per_head_inr)
        .fetch_one(pool)
        .await
    }

    pub async fn get_by_user_role_id(pool: &PgPool, user_role_profile_id: Uuid) -> Result<Option<CateringServiceProfile>, sqlx::Error> {
        sqlx::query_as::<_, CateringServiceProfile>(
            r#"SELECT id, user_role_profile_id, business_name, cuisine_types, event_types,
                      min_guests, max_guests, has_setup_team, has_serving_staff,
                      price_per_head_inr, created_at, updated_at
               FROM catering_service_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: UpsertCateringServiceProfilePayload) -> Result<CateringServiceProfile, sqlx::Error> {
        sqlx::query_as::<_, CateringServiceProfile>(
            r#"INSERT INTO catering_service_profiles (user_role_profile_id, business_name, cuisine_types, event_types,
                                                    min_guests, max_guests, has_setup_team, has_serving_staff, price_per_head_inr)
               VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
               ON CONFLICT (user_role_profile_id) DO UPDATE SET
                   business_name = EXCLUDED.business_name,
                   cuisine_types = COALESCE(EXCLUDED.cuisine_types, catering_service_profiles.cuisine_types),
                   event_types = COALESCE(EXCLUDED.event_types, catering_service_profiles.event_types),
                   min_guests = EXCLUDED.min_guests,
                   max_guests = EXCLUDED.max_guests,
                   has_setup_team = EXCLUDED.has_setup_team,
                   has_serving_staff = EXCLUDED.has_serving_staff,
                   price_per_head_inr = EXCLUDED.price_per_head_inr,
                   updated_at = NOW()
               RETURNING id, user_role_profile_id, business_name, cuisine_types, event_types,
                         min_guests, max_guests, has_setup_team, has_serving_staff,
                         price_per_head_inr, created_at, updated_at"#,
        )
        .bind(user_role_profile_id)
        .bind(&p.business_name)
        .bind(&p.cuisine_types)
        .bind(&p.event_types)
        .bind(p.min_guests)
        .bind(p.max_guests)
        .bind(p.has_setup_team)
        .bind(p.has_serving_staff)
        .bind(p.price_per_head_inr)
        .fetch_one(pool)
        .await
    }
}
