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

#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct UgcContentCreatorProfile {
    pub id: Uuid,
    pub user_role_profile_id: Uuid,
    pub niche_tags: Vec<String>,
    pub content_formats: Vec<String>,
    pub platforms: Vec<String>,
    pub turnaround_days: Option<i32>,
    pub starting_price_inr: Option<i32>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct UpsertUgcContentCreatorProfilePayload {
    pub niche_tags: Vec<String>,
    pub content_formats: Vec<String>,
    pub platforms: Vec<String>,
    pub turnaround_days: Option<i32>,
    pub starting_price_inr: Option<i32>,
}

pub struct UgcContentCreatorRepository;

impl UgcContentCreatorRepository {
    pub async fn get_by_user_id(pool: &PgPool, user_id: Uuid) -> Result<Option<UgcContentCreatorProfile>, sqlx::Error> {
        sqlx::query_as::<_, UgcContentCreatorProfile>(
            r#"SELECT uccp.id, uccp.user_role_profile_id, uccp.niche_tags, uccp.content_formats, uccp.platforms,
                      uccp.turnaround_days, uccp.starting_price_inr,
                      uccp.created_at, uccp.updated_at
               FROM ugc_content_creator_profiles uccp
               INNER JOIN user_role_profiles urp ON urp.id = uccp.user_role_profile_id
               WHERE urp.user_id = $1 AND urp.role_key = 'ugc_content_creator'"#,
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await
    }

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

        sqlx::query_as::<_, UgcContentCreatorProfile>(
            r#"INSERT INTO ugc_content_creator_profiles (user_role_profile_id, niche_tags, content_formats,
                                                      platforms, turnaround_days, starting_price_inr)
               VALUES ($1, $2, $3, $4, $5, $6)
               ON CONFLICT (user_role_profile_id) DO UPDATE SET
                   niche_tags = COALESCE(EXCLUDED.niche_tags, ugc_content_creator_profiles.niche_tags),
                   content_formats = COALESCE(EXCLUDED.content_formats, ugc_content_creator_profiles.content_formats),
                   platforms = COALESCE(EXCLUDED.platforms, ugc_content_creator_profiles.platforms),
                   turnaround_days = EXCLUDED.turnaround_days,
                   starting_price_inr = EXCLUDED.starting_price_inr,
                   updated_at = NOW()
               RETURNING id, user_role_profile_id, niche_tags, content_formats, platforms,
                         turnaround_days, starting_price_inr, created_at, updated_at"#,
        )
        .bind(user_role_profile.0)
        .bind(&p.niche_tags)
        .bind(&p.content_formats)
        .bind(&p.platforms)
        .bind(p.turnaround_days)
        .bind(p.starting_price_inr)
        .fetch_one(pool)
        .await
    }

    pub async fn get_by_user_role_id(pool: &PgPool, user_role_profile_id: Uuid) -> Result<Option<UgcContentCreatorProfile>, sqlx::Error> {
        sqlx::query_as::<_, UgcContentCreatorProfile>(
            r#"SELECT id, user_role_profile_id, niche_tags, content_formats, platforms,
                      turnaround_days, starting_price_inr, created_at, updated_at
               FROM ugc_content_creator_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: UpsertUgcContentCreatorProfilePayload) -> Result<UgcContentCreatorProfile, sqlx::Error> {
        sqlx::query_as::<_, UgcContentCreatorProfile>(
            r#"INSERT INTO ugc_content_creator_profiles (user_role_profile_id, niche_tags, content_formats,
                                                      platforms, turnaround_days, starting_price_inr)
               VALUES ($1, $2, $3, $4, $5, $6)
               ON CONFLICT (user_role_profile_id) DO UPDATE SET
                   niche_tags = COALESCE(EXCLUDED.niche_tags, ugc_content_creator_profiles.niche_tags),
                   content_formats = COALESCE(EXCLUDED.content_formats, ugc_content_creator_profiles.content_formats),
                   platforms = COALESCE(EXCLUDED.platforms, ugc_content_creator_profiles.platforms),
                   turnaround_days = EXCLUDED.turnaround_days,
                   starting_price_inr = EXCLUDED.starting_price_inr,
                   updated_at = NOW()
               RETURNING id, user_role_profile_id, niche_tags, content_formats, platforms,
                         turnaround_days, starting_price_inr, created_at, updated_at"#,
        )
        .bind(user_role_profile_id)
        .bind(&p.niche_tags)
        .bind(&p.content_formats)
        .bind(&p.platforms)
        .bind(p.turnaround_days)
        .bind(p.starting_price_inr)
        .fetch_one(pool)
        .await
    }
}
