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

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct LeadRequest {
    pub id: Uuid,
    pub reference_number: String,
    pub lead_id: Option<Uuid>,
    pub user_role_profile_id: Option<Uuid>,
    pub professional_user_id: Option<Uuid>,
    pub status: String,
    pub tracecoins_reserved: i32,
    pub remarks: Option<String>,
    pub expires_at: DateTime<Utc>,
    pub requested_at: DateTime<Utc>,
    pub resolved_at: Option<DateTime<Utc>>,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateLeadRequestPayload {
    pub lead_id: Uuid,
    pub user_role_profile_id: Uuid,
    pub professional_user_id: Uuid,
    pub customer_user_id: Option<Uuid>,
    pub remarks: Option<String>,
}

impl CreateLeadRequestPayload {
    pub fn new(
        lead_id: Uuid,
        user_role_profile_id: Uuid,
        professional_user_id: Uuid,
        customer_user_id: Option<Uuid>,
        remarks: Option<String>,
    ) -> Self {
        Self {
            lead_id,
            user_role_profile_id,
            professional_user_id,
            customer_user_id,
            remarks,
        }
    }
}

pub struct LeadRequestRepository;

impl LeadRequestRepository {
    pub async fn create(
        pool: &PgPool,
        payload: CreateLeadRequestPayload,
    ) -> Result<LeadRequest, sqlx::Error> {
        let req = sqlx::query_as::<_, LeadRequest>(
            r#"
            INSERT INTO lead_requests (lead_id, user_role_profile_id, professional_user_id, customer_user_id, remarks)
            VALUES ($1, $2, $3, $4, $5)
            RETURNING *
            "#,
        )
        .bind(payload.lead_id)
        .bind(payload.user_role_profile_id)
        .bind(payload.professional_user_id)
        .bind(payload.customer_user_id)
        .bind(&payload.remarks)
        .fetch_one(pool)
        .await?;

        Ok(req)
    }

    pub async fn get_by_id(pool: &PgPool, id: Uuid) -> Result<Option<LeadRequest>, sqlx::Error> {
        sqlx::query_as::<_, LeadRequest>("SELECT * FROM lead_requests WHERE id = $1")
            .bind(id)
            .fetch_optional(pool)
            .await
    }

    pub async fn list_by_user_role_profile_id(
        pool: &PgPool,
        user_role_profile_id: Uuid,
        page: i64,
        limit: i64,
    ) -> Result<Vec<LeadRequest>, sqlx::Error> {
        let offset = (page - 1) * limit;
        let reqs = sqlx::query_as::<_, LeadRequest>(
            r#"
            SELECT * FROM lead_requests
            WHERE user_role_profile_id = $1
            ORDER BY requested_at DESC
            LIMIT $2 OFFSET $3
            "#,
        )
        .bind(user_role_profile_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await?;

        Ok(reqs)
    }

    pub async fn update_status(
        pool: &PgPool,
        id: Uuid,
        status: &str,
    ) -> Result<LeadRequest, sqlx::Error> {
        let req = sqlx::query_as::<_, LeadRequest>(
            r#"
            UPDATE lead_requests
            SET status = $1, resolved_at = NOW(), updated_at = NOW()
            WHERE id = $2
            RETURNING *
            "#,
        )
        .bind(status)
        .bind(id)
        .fetch_one(pool)
        .await?;

        Ok(req)
    }
}
