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

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct CompanyProfile {
    pub id: Uuid,
    pub user_id: Uuid,
    pub company_name: String,
    pub registration_number: Option<String>,
    pub industry: Option<String>,
    pub website_url: Option<String>,
    pub employee_count: Option<i32>,
    pub business_type: Option<String>,
    pub gst_number: Option<String>,
    pub contact_name: Option<String>,
    pub contact_email: Option<String>,
    pub contact_phone: Option<String>,
    pub address_line1: Option<String>,
    pub city: Option<String>,
    pub state: Option<String>,
    pub country: String,
    pub postal_code: Option<String>,
    pub status: String,
    pub free_job_slots: i32,
    pub purchased_job_slots: i32,
    pub free_contact_views: i32,
    pub purchased_contact_views: i32,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct UpsertCompanyProfilePayload {
    pub company_name: String,
    pub registration_number: Option<String>,
    pub industry: Option<String>,
    pub website_url: Option<String>,
    pub employee_count: Option<i32>,
    pub business_type: Option<String>,
    pub gst_number: Option<String>,
    pub contact_name: Option<String>,
    pub contact_email: Option<String>,
    pub contact_phone: Option<String>,
    pub address_line1: Option<String>,
    pub city: Option<String>,
    pub state: Option<String>,
    pub postal_code: Option<String>,
}

pub struct CompanyRepository;

impl CompanyRepository {
    async fn is_dummy_account(pool: &PgPool, user_id: Uuid) -> Result<bool, sqlx::Error> {
        let email = sqlx::query_scalar::<_, String>("SELECT email FROM users WHERE id = $1")
            .bind(user_id)
            .fetch_one(pool)
            .await?;

        Ok(
            email.ends_with("@demo.com")
                || email == "paymentgateway@demo.com"
                || email.contains("+dummy@")
                || email.starts_with("dummy+"),
        )
    }

    pub async fn get_by_user_id(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<Option<CompanyProfile>, sqlx::Error> {
        let profile = sqlx::query_as::<_, CompanyProfile>(
            r#"
            SELECT
                id, user_id, company_name, registration_number, industry,
                website_url, employee_count, business_type, gst_number,
                contact_name, contact_email, contact_phone, address_line1,
                city, state, country, postal_code, status,
                free_job_slots, purchased_job_slots, free_contact_views, purchased_contact_views,
                created_at, updated_at
            FROM company_profiles
            WHERE user_id = $1
            "#,
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await?;

        Ok(profile)
    }

    pub async fn upsert(
        pool: &PgPool,
        user_id: Uuid,
        payload: UpsertCompanyProfilePayload,
    ) -> Result<CompanyProfile, sqlx::Error> {
        let is_dummy_account = Self::is_dummy_account(pool, user_id).await?;
        let default_status = if is_dummy_account { "APPROVED" } else { "PENDING" };

        let profile = sqlx::query_as::<_, CompanyProfile>(
            r#"
            INSERT INTO company_profiles (
                user_id, company_name, registration_number, industry, website_url,
                employee_count, business_type, gst_number, contact_name,
                contact_email, contact_phone, address_line1, city, state, postal_code, status
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
            ON CONFLICT (user_id) DO UPDATE SET
                company_name = EXCLUDED.company_name,
                registration_number = EXCLUDED.registration_number,
                industry = EXCLUDED.industry,
                website_url = EXCLUDED.website_url,
                employee_count = EXCLUDED.employee_count,
                business_type = EXCLUDED.business_type,
                gst_number = EXCLUDED.gst_number,
                contact_name = EXCLUDED.contact_name,
                contact_email = EXCLUDED.contact_email,
                contact_phone = EXCLUDED.contact_phone,
                address_line1 = EXCLUDED.address_line1,
                city = EXCLUDED.city,
                state = EXCLUDED.state,
                postal_code = EXCLUDED.postal_code,
                status = CASE
                    WHEN $17 THEN 'APPROVED'
                    WHEN company_profiles.status = 'APPROVED' THEN 'APPROVED'
                    ELSE 'PENDING'
                END,
                updated_at = NOW()
            RETURNING
                id, user_id, company_name, registration_number, industry,
                website_url, employee_count, business_type, gst_number,
                contact_name, contact_email, contact_phone, address_line1,
                city, state, country, postal_code, status,
                free_job_slots, purchased_job_slots, free_contact_views, purchased_contact_views,
                created_at, updated_at
            "#,
        )
        .bind(user_id)
        .bind(payload.company_name)
        .bind(payload.registration_number)
        .bind(payload.industry)
        .bind(payload.website_url)
        .bind(payload.employee_count)
        .bind(payload.business_type)
        .bind(payload.gst_number)
        .bind(payload.contact_name)
        .bind(payload.contact_email)
        .bind(payload.contact_phone)
        .bind(payload.address_line1)
        .bind(payload.city)
        .bind(payload.state)
        .bind(payload.postal_code)
        .bind(default_status)
        .bind(is_dummy_account)
        .fetch_one(pool)
        .await?;

        Ok(profile)
    }

    pub async fn submit_for_verification(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<CompanyProfile, sqlx::Error> {
        let is_dummy_account = Self::is_dummy_account(pool, user_id).await?;
        let next_status = if is_dummy_account { "APPROVED" } else { "PENDING_REVIEW" };

        let profile = sqlx::query_as::<_, CompanyProfile>(
            r#"
            UPDATE company_profiles
            SET status = $2, updated_at = NOW()
            WHERE user_id = $1
            RETURNING
                id, user_id, company_name, registration_number, industry,
                website_url, employee_count, business_type, gst_number,
                contact_name, contact_email, contact_phone, address_line1,
                city, state, country, postal_code, status,
                free_job_slots, purchased_job_slots, free_contact_views, purchased_contact_views,
                created_at, updated_at
            "#,
        )
        .bind(user_id)
        .bind(next_status)
        .fetch_one(pool)
        .await?;

        Ok(profile)
    }
}
