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

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct OnboardingConfigListItem {
    pub id: Uuid,
    pub role_id: Uuid,
    pub role_key: String,
    pub version: i32,
    pub is_active: bool,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct OnboardingConfig {
    pub id: Uuid,
    pub role_id: Uuid,
    pub schema_json: serde_json::Value,
    pub version: i32,
    pub is_active: bool,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateOnboardingConfigPayload {
    pub role_id: Uuid,
    pub schema_json: serde_json::Value,
}

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct DashboardConfigListItem {
    pub id: Uuid,
    pub role_id: Uuid,
    pub role_key: String,
    pub audience: String,
    pub config_json: serde_json::Value,
    pub is_active: bool,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct DashboardConfig {
    pub id: Uuid,
    pub role_id: Uuid,
    pub audience: String,
    pub config_json: serde_json::Value,
    pub is_active: bool,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateDashboardConfigPayload {
    pub role_id: Uuid,
    pub audience: String,
    pub config_json: serde_json::Value,
}

#[derive(Debug, Serialize, Deserialize, FromRow)]
pub struct RuntimeConfig {
    pub id: Uuid,
    pub role_id: Uuid,
    pub config_json: serde_json::Value,
    pub version: i32,
    pub is_active: bool,
    pub updated_at: DateTime<Utc>,
}

#[derive(Debug, Serialize, Deserialize)]
pub struct CreateRuntimeConfigPayload {
    pub role_id: Uuid,
    pub config_json: serde_json::Value,
}

pub struct ConfigRepository;

impl ConfigRepository {
    pub async fn create_onboarding_config(
        pool: &PgPool,
        payload: CreateOnboardingConfigPayload,
    ) -> Result<OnboardingConfig, sqlx::Error> {
        // Soft-disable previous active configs for this role
        sqlx::query(
            r#"
            UPDATE onboarding_configs
            SET is_active = false
            WHERE role_id = $1 AND is_active = true
            "#,
        )
        .bind(payload.role_id)
        .execute(pool)
        .await?;

        // Insert new config
        let config = sqlx::query_as::<_, OnboardingConfig>(
            r#"
            INSERT INTO onboarding_configs (role_id, schema_json, version, is_active)
            VALUES (
                $1,
                $2,
                COALESCE((SELECT MAX(version) FROM onboarding_configs WHERE role_id = $1), 0) + 1,
                true
            )
            RETURNING id, role_id, schema_json, version, is_active, updated_at
            "#,
        )
        .bind(payload.role_id)
        .bind(payload.schema_json)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn get_active_onboarding_config(
        pool: &PgPool,
        role_id: Uuid,
    ) -> Result<OnboardingConfig, sqlx::Error> {
        let config = sqlx::query_as::<_, OnboardingConfig>(
            r#"
            SELECT id, role_id, schema_json, version, is_active, updated_at
            FROM onboarding_configs
            WHERE role_id = $1 AND is_active = true
            "#,
        )
        .bind(role_id)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn get_all_onboarding_configs(
        pool: &PgPool,
    ) -> Result<Vec<OnboardingConfigListItem>, sqlx::Error> {
        let configs = sqlx::query_as::<_, OnboardingConfigListItem>(
            r#"
            SELECT
                c.id, c.role_id, r.key as role_key,
                c.version, c.is_active, c.updated_at
            FROM onboarding_configs c
            JOIN roles r ON c.role_id = r.id
            ORDER BY c.updated_at DESC
            "#,
        )
        .fetch_all(pool)
        .await?;

        Ok(configs)
    }

    pub async fn get_active_onboarding_by_role_key(
        pool: &PgPool,
        role_key: &str,
    ) -> Result<OnboardingConfig, sqlx::Error> {
        let config = sqlx::query_as::<_, OnboardingConfig>(
            r#"
            SELECT c.id, c.role_id, c.schema_json, c.version, c.is_active, c.updated_at
            FROM onboarding_configs c
            JOIN roles r ON c.role_id = r.id
            WHERE r.key = $1 AND c.is_active = true
            "#,
        )
        .bind(role_key.to_uppercase())
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn create_dashboard_config(
        pool: &PgPool,
        payload: CreateDashboardConfigPayload,
    ) -> Result<DashboardConfig, sqlx::Error> {
        sqlx::query(
            r#"
            UPDATE role_sidebar_configs
            SET is_active = false
            WHERE role_id = $1 AND audience = $2::text AND is_active = true
            "#,
        )
        .bind(payload.role_id)
        .bind(&payload.audience)
        .execute(pool)
        .await?;

        let config = sqlx::query_as::<_, DashboardConfig>(
            r#"
            INSERT INTO role_sidebar_configs (role_id, audience, config_json, is_active)
            VALUES (
                $1,
                $2::text,
                $3,
                true
            )
            RETURNING id, role_id, audience, config_json, is_active, updated_at
            "#,
        )
        .bind(payload.role_id)
        .bind(&payload.audience)
        .bind(payload.config_json)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn get_active_dashboard_config(
        pool: &PgPool,
        role_id: Uuid,
        audience: &str,
    ) -> Result<DashboardConfig, sqlx::Error> {
        let config = sqlx::query_as::<_, DashboardConfig>(
            r#"
            SELECT id, role_id, audience, config_json, is_active, updated_at
            FROM role_sidebar_configs
            WHERE role_id = $1 AND audience = $2 AND is_active = true
            "#,
        )
        .bind(role_id)
        .bind(audience)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn get_all_dashboard_configs(
        pool: &PgPool,
    ) -> Result<Vec<DashboardConfigListItem>, sqlx::Error> {
        let configs = sqlx::query_as::<_, DashboardConfigListItem>(
            r#"
            SELECT
                c.id, c.role_id, r.key as role_key, c.audience, c.config_json, c.is_active, c.updated_at
            FROM role_sidebar_configs c
            JOIN roles r ON c.role_id = r.id
            ORDER BY c.updated_at DESC
            "#,
        )
        .fetch_all(pool)
        .await?;

        Ok(configs)
    }

    pub async fn get_active_dashboard_by_role_key(
        pool: &PgPool,
        role_key: &str,
        audience: &str,
    ) -> Result<DashboardConfig, sqlx::Error> {
        let config = sqlx::query_as::<_, DashboardConfig>(
            r#"
            SELECT c.id, c.role_id, c.audience, c.config_json, c.is_active, c.updated_at
            FROM role_sidebar_configs c
            JOIN roles r ON c.role_id = r.id
            WHERE r.key = $1 AND c.audience = $2 AND c.is_active = true
            "#,
        )
        .bind(role_key)
        .bind(audience)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn create_runtime_config(
        pool: &PgPool,
        payload: CreateRuntimeConfigPayload,
    ) -> Result<RuntimeConfig, sqlx::Error> {
        // Soft-disable previous active configs for this role
        sqlx::query(
            r#"
            UPDATE role_runtime_configs
            SET is_active = false
            WHERE role_id = $1 AND is_active = true
            "#,
        )
        .bind(payload.role_id)
        .execute(pool)
        .await?;

        // Insert new config
        let config = sqlx::query_as::<_, RuntimeConfig>(
            r#"
            INSERT INTO role_runtime_configs (role_id, config_json, version, is_active)
            VALUES (
                $1,
                $2,
                COALESCE((SELECT MAX(version) FROM role_runtime_configs WHERE role_id = $1), 0) + 1,
                true
            )
            RETURNING id, role_id, config_json, version, is_active, updated_at
            "#,
        )
        .bind(payload.role_id)
        .bind(payload.config_json)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn get_active_runtime_config(
        pool: &PgPool,
        role_id: Uuid,
    ) -> Result<RuntimeConfig, sqlx::Error> {
        let config = sqlx::query_as::<_, RuntimeConfig>(
            r#"
            SELECT id, role_id, config_json, version, is_active, updated_at
            FROM role_runtime_configs
            WHERE role_id = $1 AND is_active = true
            "#,
        )
        .bind(role_id)
        .fetch_one(pool)
        .await?;

        Ok(config)
    }

    pub async fn get_active_runtime_by_role_key(
        pool: &PgPool,
        role_key: &str,
    ) -> Result<RuntimeConfig, sqlx::Error> {
        let config = sqlx::query_as::<_, RuntimeConfig>(
            r#"
            SELECT rc.id, rc.role_id, rc.config_json, rc.version, rc.is_active, rc.updated_at
            FROM role_runtime_configs rc
            JOIN roles r ON rc.role_id = r.id
            WHERE r.key = $1 AND rc.is_active = true
            "#,
        )
        .bind(role_key.to_uppercase())
        .fetch_one(pool)
        .await?;

        Ok(config)
    }
}
