use athena_schema_heal::{
    SchemaHealAuditConfig, SchemaHealContext, SchemaHealPlan, SchemaHealer, SchemaMutation,
};
use sqlx::PgPool;

pub struct ControlPlaneSchemaService;

impl ControlPlaneSchemaService {
    pub async fn ensure_clone_tables(pool: &PgPool) -> Result<bool, crate::ControlPlaneError> {
        let report = SchemaHealer::new(pool)
            .with_audit_config(SchemaHealAuditConfig {
                enabled: true,
                actor: "athena_control_plane".to_string(),
            })
            .apply_plan(&clone_tables_plan())
            .await
            .map_err(|err| crate::ControlPlaneError::Storage(err.to_string()))?;

        Ok(report.applied_count() > 0)
    }

    pub async fn ensure_daemon_registry_table(
        pool: &PgPool,
    ) -> Result<bool, crate::ControlPlaneError> {
        let report = SchemaHealer::new(pool)
            .with_audit_config(SchemaHealAuditConfig {
                enabled: true,
                actor: "athena_control_plane".to_string(),
            })
            .apply_plan(&daemon_registry_plan())
            .await
            .map_err(|err| crate::ControlPlaneError::Storage(err.to_string()))?;

        Ok(report.applied_count() > 0)
    }
}

fn clone_tables_plan() -> SchemaHealPlan {
    let context = SchemaHealContext::new("athena_logging", "public")
        .with_object_type("database_clone_control_plane")
        .with_table_name("database_clone_jobs");

    SchemaHealPlan::new("database_clone_control_plane", context)
        .with_mutation(SchemaMutation::ensure_table(
            "ensure_database_clone_jobs_table",
            "public",
            "database_clone_jobs",
            r#"
            CREATE TABLE IF NOT EXISTS public.database_clone_jobs (
                job_id uuid PRIMARY KEY,
                source jsonb NOT NULL,
                target jsonb NOT NULL,
                registration_policy jsonb NOT NULL DEFAULT '{}'::jsonb,
                provisioning_policy jsonb NOT NULL DEFAULT '{}'::jsonb,
                requested_by text,
                status text NOT NULL,
                phase text NOT NULL,
                attempt_count integer NOT NULL DEFAULT 0,
                max_attempts integer NOT NULL DEFAULT 3,
                execution_daemon_id text,
                lease_token uuid,
                lease_expires_at timestamptz,
                cancel_requested boolean NOT NULL DEFAULT false,
                redacted_summary jsonb NOT NULL DEFAULT '{}'::jsonb,
                verification_summary jsonb,
                failure_summary jsonb,
                created_at timestamptz NOT NULL DEFAULT now(),
                started_at timestamptz,
                updated_at timestamptz NOT NULL DEFAULT now(),
                completed_at timestamptz
            )
            "#,
        ))
        .with_mutation(SchemaMutation::ensure_table(
            "ensure_database_clone_job_events_table",
            "public",
            "database_clone_job_events",
            r#"
            CREATE TABLE IF NOT EXISTS public.database_clone_job_events (
                event_id uuid PRIMARY KEY,
                job_id uuid NOT NULL REFERENCES public.database_clone_jobs(job_id) ON DELETE CASCADE,
                daemon_id text,
                phase text NOT NULL,
                level text NOT NULL,
                kind text NOT NULL,
                message text NOT NULL,
                details jsonb NOT NULL DEFAULT '{}'::jsonb,
                created_at timestamptz NOT NULL DEFAULT now()
            )
            "#,
        ))
        .with_mutation(SchemaMutation::ensure_index(
            "ensure_idx_database_clone_jobs_status_created_at",
            "public",
            "database_clone_jobs",
            "idx_database_clone_jobs_status_created_at",
            "CREATE INDEX IF NOT EXISTS idx_database_clone_jobs_status_created_at ON public.database_clone_jobs(status, created_at ASC)",
        ))
        .with_mutation(SchemaMutation::ensure_index(
            "ensure_idx_database_clone_jobs_execution_daemon_id",
            "public",
            "database_clone_jobs",
            "idx_database_clone_jobs_execution_daemon_id",
            "CREATE INDEX IF NOT EXISTS idx_database_clone_jobs_execution_daemon_id ON public.database_clone_jobs(execution_daemon_id)",
        ))
        .with_mutation(SchemaMutation::ensure_index(
            "ensure_idx_database_clone_job_events_job_id_created_at",
            "public",
            "database_clone_job_events",
            "idx_database_clone_job_events_job_id_created_at",
            "CREATE INDEX IF NOT EXISTS idx_database_clone_job_events_job_id_created_at ON public.database_clone_job_events(job_id, created_at ASC)",
        ))
}

fn daemon_registry_plan() -> SchemaHealPlan {
    let context = SchemaHealContext::new("athena_logging", "public")
        .with_object_type("daemon_runtime_registry")
        .with_table_name("daemon_runtime_registry");

    SchemaHealPlan::new("daemon_runtime_registry", context)
        .with_mutation(SchemaMutation::ensure_table(
            "ensure_daemon_runtime_registry_table",
            "public",
            "daemon_runtime_registry",
            r#"
            CREATE TABLE IF NOT EXISTS public.daemon_runtime_registry (
                daemon_id text PRIMARY KEY,
                runtime_kind text NOT NULL,
                host text NOT NULL,
                version text NOT NULL,
                workers_json jsonb NOT NULL DEFAULT '[]'::jsonb,
                metadata_json jsonb NOT NULL DEFAULT '{}'::jsonb,
                started_at timestamptz NOT NULL DEFAULT now(),
                last_seen_at timestamptz NOT NULL DEFAULT now()
            )
            "#,
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_runtime_kind",
            "public",
            "daemon_runtime_registry",
            "runtime_kind",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN runtime_kind text NOT NULL DEFAULT 'unknown'",
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_host",
            "public",
            "daemon_runtime_registry",
            "host",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN host text NOT NULL DEFAULT 'unknown-host'",
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_version",
            "public",
            "daemon_runtime_registry",
            "version",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN version text NOT NULL DEFAULT 'unknown'",
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_workers_json",
            "public",
            "daemon_runtime_registry",
            "workers_json",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN workers_json jsonb NOT NULL DEFAULT '[]'::jsonb",
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_metadata_json",
            "public",
            "daemon_runtime_registry",
            "metadata_json",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN metadata_json jsonb NOT NULL DEFAULT '{}'::jsonb",
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_started_at",
            "public",
            "daemon_runtime_registry",
            "started_at",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN started_at timestamptz NOT NULL DEFAULT now()",
        ))
        .with_mutation(SchemaMutation::ensure_column(
            "ensure_daemon_runtime_registry_last_seen_at",
            "public",
            "daemon_runtime_registry",
            "last_seen_at",
            "ALTER TABLE public.daemon_runtime_registry ADD COLUMN last_seen_at timestamptz NOT NULL DEFAULT now()",
        ))
        .with_mutation(SchemaMutation::ensure_index(
            "ensure_idx_daemon_runtime_registry_last_seen_at",
            "public",
            "daemon_runtime_registry",
            "idx_daemon_runtime_registry_last_seen_at",
            "CREATE INDEX IF NOT EXISTS idx_daemon_runtime_registry_last_seen_at ON public.daemon_runtime_registry(last_seen_at DESC)",
        ))
        .with_mutation(SchemaMutation::ensure_index(
            "ensure_idx_daemon_runtime_registry_runtime_kind",
            "public",
            "daemon_runtime_registry",
            "idx_daemon_runtime_registry_runtime_kind",
            "CREATE INDEX IF NOT EXISTS idx_daemon_runtime_registry_runtime_kind ON public.daemon_runtime_registry(runtime_kind)",
        ))
}
