use chrono::{DateTime, Utc};
use sqlx::PgPool;
use uuid::Uuid;

use crate::models::ai::*;

pub struct AiPlanRepository;

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

    pub async fn get_by_code(pool: &PgPool, code: &str) -> Result<Option<AiPlan>, sqlx::Error> {
        sqlx::query_as::<_, AiPlan>(
            "SELECT * FROM ai_plans WHERE code = $1 AND is_active = true"
        )
        .bind(code)
        .fetch_optional(pool)
        .await
    }

    pub async fn list_active(pool: &PgPool) -> Result<Vec<AiPlan>, sqlx::Error> {
        sqlx::query_as::<_, AiPlan>(
            "SELECT * FROM ai_plans WHERE is_active = true ORDER BY monthly_credits"
        )
        .fetch_all(pool)
        .await
    }

    pub async fn create(
        pool: &PgPool,
        code: &str,
        name: &str,
        monthly_credits: i32,
        daily_action_limit: i32,
        allowed_models: serde_json::Value,
        allowed_features: serde_json::Value,
    ) -> Result<AiPlan, sqlx::Error> {
        sqlx::query_as::<_, AiPlan>(
            r#"
            INSERT INTO ai_plans (code, name, monthly_credits, daily_action_limit, allowed_models, allowed_features)
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING *
            "#
        )
        .bind(code)
        .bind(name)
        .bind(monthly_credits)
        .bind(daily_action_limit)
        .bind(allowed_models)
        .bind(allowed_features)
        .fetch_one(pool)
        .await
    }

    pub async fn update(
        pool: &PgPool,
        id: Uuid,
        name: Option<&str>,
        monthly_credits: Option<i32>,
        daily_action_limit: Option<i32>,
        allowed_models: Option<serde_json::Value>,
        allowed_features: Option<serde_json::Value>,
        is_active: Option<bool>,
    ) -> Result<AiPlan, sqlx::Error> {
        sqlx::query_as::<_, AiPlan>(
            r#"
            UPDATE ai_plans SET
                name = COALESCE($2, name),
                monthly_credits = COALESCE($3, monthly_credits),
                daily_action_limit = COALESCE($4, daily_action_limit),
                allowed_models = COALESCE($5, allowed_models),
                allowed_features = COALESCE($6, allowed_features),
                is_active = COALESCE($7, is_active),
                updated_at = NOW()
            WHERE id = $1
            RETURNING *
            "#
        )
        .bind(id)
        .bind(name)
        .bind(monthly_credits)
        .bind(daily_action_limit)
        .bind(allowed_models)
        .bind(allowed_features)
        .bind(is_active)
        .fetch_one(pool)
        .await
    }
}

pub struct UserAiSubscriptionRepository;

impl UserAiSubscriptionRepository {
    pub async fn get_by_user_id(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<Option<UserAiSubscription>, sqlx::Error> {
        sqlx::query_as::<_, UserAiSubscription>(
            "SELECT * FROM user_ai_subscriptions WHERE user_id = $1"
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await
    }

    pub async fn create(
        pool: &PgPool,
        user_id: Uuid,
        plan_id: Uuid,
        role_code: Option<&str>,
        monthly_credits_total: i32,
        period_start: DateTime<Utc>,
        period_end: DateTime<Utc>,
    ) -> Result<UserAiSubscription, sqlx::Error> {
        sqlx::query_as::<_, UserAiSubscription>(
            r#"
            INSERT INTO user_ai_subscriptions (
                user_id, plan_id, role_code, monthly_credits_total,
                current_period_start, current_period_end
            )
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING *
            "#
        )
        .bind(user_id)
        .bind(plan_id)
        .bind(role_code)
        .bind(monthly_credits_total)
        .bind(period_start)
        .bind(period_end)
        .fetch_one(pool)
        .await
    }

    pub async fn update_plan(
        pool: &PgPool,
        user_id: Uuid,
        plan_id: Uuid,
        monthly_credits_total: i32,
        period_start: DateTime<Utc>,
        period_end: DateTime<Utc>,
    ) -> Result<UserAiSubscription, sqlx::Error> {
        sqlx::query_as::<_, UserAiSubscription>(
            r#"
            UPDATE user_ai_subscriptions
            SET plan_id = $2,
                monthly_credits_total = $3,
                monthly_credits_used = 0,
                daily_actions_used = 0,
                current_period_start = $4,
                current_period_end = $5,
                updated_at = NOW()
            WHERE user_id = $1
            RETURNING *
            "#
        )
        .bind(user_id)
        .bind(plan_id)
        .bind(monthly_credits_total)
        .bind(period_start)
        .bind(period_end)
        .fetch_one(pool)
        .await
    }

    pub async fn add_purchased_credits(
        pool: &PgPool,
        user_id: Uuid,
        credits: i32,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE user_ai_subscriptions SET purchased_credits_total = purchased_credits_total + $1, updated_at = NOW() WHERE user_id = $2"
        )
        .bind(credits)
        .bind(user_id)
        .execute(pool)
        .await?;
        Ok(())
    }

    /// Atomically checks and deducts credits + daily action count in a single
    /// conditional UPDATE, so concurrent calls for the same user can't both
    /// pass a separate check-then-act step and overspend past the limit.
    /// Returns the updated row if the charge succeeded, or None if the user
    /// didn't have enough credits or hit their daily action limit (caller
    /// can re-read the subscription to report which one).
    pub async fn try_charge(
        pool: &PgPool,
        user_id: Uuid,
        credits: i32,
        daily_action_limit: i32,
    ) -> Result<Option<UserAiSubscription>, sqlx::Error> {
        sqlx::query_as::<_, UserAiSubscription>(
            r#"
            UPDATE user_ai_subscriptions
            SET monthly_credits_used = monthly_credits_used + $1,
                daily_actions_used = daily_actions_used + 1,
                updated_at = NOW()
            WHERE user_id = $2
              AND daily_actions_used < $3
              AND (monthly_credits_total + purchased_credits_total
                   - monthly_credits_used - purchased_credits_used) >= $1
            RETURNING *
            "#,
        )
        .bind(credits)
        .bind(user_id)
        .bind(daily_action_limit)
        .fetch_optional(pool)
        .await
    }

    pub async fn increment_daily_actions(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE user_ai_subscriptions SET daily_actions_used = daily_actions_used + 1, updated_at = NOW() WHERE user_id = $1"
        )
        .bind(user_id)
        .execute(pool)
        .await?;
        Ok(())
    }

    pub async fn charge_credits(
        pool: &PgPool,
        user_id: Uuid,
        credits: i32,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE user_ai_subscriptions SET monthly_credits_used = monthly_credits_used + $1, updated_at = NOW() WHERE user_id = $2"
        )
        .bind(credits)
        .bind(user_id)
        .execute(pool)
        .await?;
        Ok(())
    }

    pub async fn reset_daily_actions(pool: &PgPool) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE user_ai_subscriptions SET daily_actions_used = 0, updated_at = NOW()"
        )
        .execute(pool)
        .await?;
        Ok(())
    }

    pub async fn reset_monthly_credits(pool: &PgPool) -> Result<(), sqlx::Error> {
        sqlx::query(
            "UPDATE user_ai_subscriptions SET monthly_credits_used = 0, daily_actions_used = 0, updated_at = NOW()"
        )
        .execute(pool)
        .await?;
        Ok(())
    }
}

pub struct AiFeatureCostRepository;

impl AiFeatureCostRepository {
    pub async fn get_by_code(
        pool: &PgPool,
        feature_code: &str,
    ) -> Result<Option<AiFeatureCost>, sqlx::Error> {
        sqlx::query_as::<_, AiFeatureCost>(
            "SELECT * FROM ai_feature_costs WHERE feature_code = $1 AND is_active = true"
        )
        .bind(feature_code)
        .fetch_optional(pool)
        .await
    }

    pub async fn list_active(pool: &PgPool) -> Result<Vec<AiFeatureCost>, sqlx::Error> {
        sqlx::query_as::<_, AiFeatureCost>(
            "SELECT * FROM ai_feature_costs WHERE is_active = true"
        )
        .fetch_all(pool)
        .await
    }

    pub async fn create(
        pool: &PgPool,
        feature_code: &str,
        display_name: &str,
        default_model: &str,
        credit_cost: i32,
        max_input_tokens: Option<i32>,
        max_output_tokens: Option<i32>,
    ) -> Result<AiFeatureCost, sqlx::Error> {
        sqlx::query_as::<_, AiFeatureCost>(
            r#"
            INSERT INTO ai_feature_costs (feature_code, display_name, default_model, credit_cost, max_input_tokens, max_output_tokens)
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING *
            "#
        )
        .bind(feature_code)
        .bind(display_name)
        .bind(default_model)
        .bind(credit_cost)
        .bind(max_input_tokens)
        .bind(max_output_tokens)
        .fetch_one(pool)
        .await
    }

    pub async fn update(
        pool: &PgPool,
        id: Uuid,
        display_name: Option<&str>,
        default_model: Option<&str>,
        credit_cost: Option<i32>,
        max_input_tokens: Option<i32>,
        max_output_tokens: Option<i32>,
        is_active: Option<bool>,
    ) -> Result<AiFeatureCost, sqlx::Error> {
        sqlx::query_as::<_, AiFeatureCost>(
            r#"
            UPDATE ai_feature_costs SET
                display_name = COALESCE($2, display_name),
                default_model = COALESCE($3, default_model),
                credit_cost = COALESCE($4, credit_cost),
                max_input_tokens = COALESCE($5, max_input_tokens),
                max_output_tokens = COALESCE($6, max_output_tokens),
                is_active = COALESCE($7, is_active),
                updated_at = NOW()
            WHERE id = $1
            RETURNING *
            "#
        )
        .bind(id)
        .bind(display_name)
        .bind(default_model)
        .bind(credit_cost)
        .bind(max_input_tokens)
        .bind(max_output_tokens)
        .bind(is_active)
        .fetch_one(pool)
        .await
    }
}

pub struct AiCreditPackageRepository;

impl AiCreditPackageRepository {
    pub async fn list_active(pool: &PgPool) -> Result<Vec<AiCreditPackage>, sqlx::Error> {
        sqlx::query_as::<_, AiCreditPackage>(
            "SELECT * FROM ai_credit_packages WHERE is_active = true ORDER BY price_inr ASC"
        )
        .fetch_all(pool)
        .await
    }

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

pub struct AiUsageLogRepository;

impl AiUsageLogRepository {
    pub async fn create(
        pool: &PgPool,
        user_id: Uuid,
        role_code: Option<&str>,
        feature_code: &str,
        model_alias: &str,
        credits_charged: i32,
        input_tokens: Option<i32>,
        output_tokens: Option<i32>,
        total_tokens: Option<i32>,
        status: &str,
        request_id: Option<&str>,
        error_message: Option<&str>,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            r#"
            INSERT INTO ai_usage_logs (
                user_id, role_code, feature_code, model_alias, credits_charged,
                input_tokens, output_tokens, total_tokens, status, request_id, error_message
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
            "#
        )
        .bind(user_id)
        .bind(role_code)
        .bind(feature_code)
        .bind(model_alias)
        .bind(credits_charged)
        .bind(input_tokens)
        .bind(output_tokens)
        .bind(total_tokens)
        .bind(status)
        .bind(request_id)
        .bind(error_message)
        .execute(pool)
        .await?;
        Ok(())
    }

    pub async fn list_by_user(
        pool: &PgPool,
        user_id: Uuid,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<AiUsageLog>, sqlx::Error> {
        sqlx::query_as::<_, AiUsageLog>(
            "SELECT * FROM ai_usage_logs WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3"
        )
        .bind(user_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
    }
}

pub struct AiCreditTransactionRepository;

impl AiCreditTransactionRepository {
    pub async fn create(
        pool: &PgPool,
        user_id: Uuid,
        transaction_type: &str,
        source: &str,
        credits: i32,
        balance_after: i32,
        reference_id: Option<Uuid>,
        description: Option<&str>,
    ) -> Result<(), sqlx::Error> {
        sqlx::query(
            "INSERT INTO ai_credit_transactions (user_id, transaction_type, source, credits, balance_after, reference_id, description) VALUES ($1, $2, $3, $4, $5, $6, $7)"
        )
        .bind(user_id)
        .bind(transaction_type)
        .bind(source)
        .bind(credits)
        .bind(balance_after)
        .bind(reference_id)
        .bind(description)
        .execute(pool)
        .await?;
        Ok(())
    }

    pub async fn list_by_user(
        pool: &PgPool,
        user_id: Uuid,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<AiCreditTransaction>, sqlx::Error> {
        sqlx::query_as::<_, AiCreditTransaction>(
            "SELECT * FROM ai_credit_transactions WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3"
        )
        .bind(user_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
    }
}

pub struct AiAutoApplySettingsRepository;

impl AiAutoApplySettingsRepository {
    pub async fn get_by_user_id(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<Option<AiAutoApplySettings>, sqlx::Error> {
        sqlx::query_as::<_, AiAutoApplySettings>(
            "SELECT * FROM ai_auto_apply_settings WHERE user_id = $1"
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await
    }

    pub async fn upsert(
        pool: &PgPool,
        user_id: Uuid,
        is_enabled: bool,
        preferred_titles: Option<serde_json::Value>,
        preferred_locations: Option<serde_json::Value>,
        preferred_job_types: Option<serde_json::Value>,
        preferred_work_modes: Option<serde_json::Value>,
        preferred_skills: Option<serde_json::Value>,
        min_salary: Option<i32>,
        max_salary: Option<i32>,
        max_applications_per_day: i32,
        require_user_approval: bool,
    ) -> Result<AiAutoApplySettings, sqlx::Error> {
        sqlx::query_as::<_, AiAutoApplySettings>(
            r#"
            INSERT INTO ai_auto_apply_settings (
                user_id, is_enabled, preferred_titles, preferred_locations, preferred_job_types,
                preferred_work_modes, preferred_skills, min_salary, max_salary,
                max_applications_per_day, require_user_approval
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
            ON CONFLICT (user_id) DO UPDATE SET
                is_enabled = EXCLUDED.is_enabled,
                preferred_titles = EXCLUDED.preferred_titles,
                preferred_locations = EXCLUDED.preferred_locations,
                preferred_job_types = EXCLUDED.preferred_job_types,
                preferred_work_modes = EXCLUDED.preferred_work_modes,
                preferred_skills = EXCLUDED.preferred_skills,
                min_salary = EXCLUDED.min_salary,
                max_salary = EXCLUDED.max_salary,
                max_applications_per_day = EXCLUDED.max_applications_per_day,
                require_user_approval = EXCLUDED.require_user_approval,
                updated_at = NOW()
            RETURNING *
            "#
        )
        .bind(user_id)
        .bind(is_enabled)
        .bind(preferred_titles)
        .bind(preferred_locations)
        .bind(preferred_job_types)
        .bind(preferred_work_modes)
        .bind(preferred_skills)
        .bind(min_salary)
        .bind(max_salary)
        .bind(max_applications_per_day)
        .bind(require_user_approval)
        .fetch_one(pool)
        .await
    }
}

pub struct AiAutoRequestSettingsRepository;

impl AiAutoRequestSettingsRepository {
    pub async fn get_by_user_id(
        pool: &PgPool,
        user_id: Uuid,
    ) -> Result<Option<AiAutoRequestSettings>, sqlx::Error> {
        sqlx::query_as::<_, AiAutoRequestSettings>(
            "SELECT * FROM ai_auto_request_settings WHERE user_id = $1"
        )
        .bind(user_id)
        .fetch_optional(pool)
        .await
    }

    pub async fn upsert(
        pool: &PgPool,
        user_id: Uuid,
        professional_role_code: &str,
        is_enabled: bool,
        preferred_categories: Option<serde_json::Value>,
        preferred_locations: Option<serde_json::Value>,
        preferred_requirement_types: Option<serde_json::Value>,
        min_budget: Option<i32>,
        max_budget: Option<i32>,
        max_requests_per_day: i32,
        require_user_approval: bool,
    ) -> Result<AiAutoRequestSettings, sqlx::Error> {
        sqlx::query_as::<_, AiAutoRequestSettings>(
            r#"
            INSERT INTO ai_auto_request_settings (
                user_id, professional_role_code, is_enabled, preferred_categories,
                preferred_locations, preferred_requirement_types, min_budget, max_budget,
                max_requests_per_day, require_user_approval
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
            ON CONFLICT (user_id) DO UPDATE SET
                professional_role_code = EXCLUDED.professional_role_code,
                is_enabled = EXCLUDED.is_enabled,
                preferred_categories = EXCLUDED.preferred_categories,
                preferred_locations = EXCLUDED.preferred_locations,
                preferred_requirement_types = EXCLUDED.preferred_requirement_types,
                min_budget = EXCLUDED.min_budget,
                max_budget = EXCLUDED.max_budget,
                max_requests_per_day = EXCLUDED.max_requests_per_day,
                require_user_approval = EXCLUDED.require_user_approval,
                updated_at = NOW()
            RETURNING *
            "#
        )
        .bind(user_id)
        .bind(professional_role_code)
        .bind(is_enabled)
        .bind(preferred_categories)
        .bind(preferred_locations)
        .bind(preferred_requirement_types)
        .bind(min_budget)
        .bind(max_budget)
        .bind(max_requests_per_day)
        .bind(require_user_approval)
        .fetch_one(pool)
        .await
    }
}

pub struct AiAutoApplyLogRepository;

impl AiAutoApplyLogRepository {
    pub async fn list_by_user(
        pool: &PgPool,
        user_id: Uuid,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<AiAutoApplyLog>, sqlx::Error> {
        sqlx::query_as::<_, AiAutoApplyLog>(
            "SELECT * FROM ai_auto_apply_logs WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3"
        )
        .bind(user_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
    }
}

pub struct AiAutoRequestLogRepository;

impl AiAutoRequestLogRepository {
    pub async fn list_by_user(
        pool: &PgPool,
        user_id: Uuid,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<AiAutoRequestLog>, sqlx::Error> {
        sqlx::query_as::<_, AiAutoRequestLog>(
            "SELECT * FROM ai_auto_request_logs WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3"
        )
        .bind(user_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(pool)
        .await
    }
}
