use crate::clone_events::{CloneEventLevel, CloneJobEvent};
use crate::clone_jobs::{
    CloneJobDetail, CloneJobId, CloneJobPhase, CloneJobRecord, CloneJobRequest, CloneJobStatus,
};
use crate::{CloneJobSummary, ControlPlaneError, DaemonId, LeaseToken};
use serde_json::Value;
use sqlx::{PgPool, Row};
use uuid::Uuid;

pub struct CloneJobRepository {
    pool: PgPool,
}

impl CloneJobRepository {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    pub fn pool(&self) -> &PgPool {
        &self.pool
    }

    pub async fn create_job(
        &self,
        request: CloneJobRequest,
    ) -> Result<CloneJobRecord, ControlPlaneError> {
        request.validate()?;
        let job_id = CloneJobId::new();
        let source = serde_json::to_value(&request.source)
            .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;
        let target = serde_json::to_value(&request.target)
            .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;
        let registration = serde_json::to_value(&request.registration)
            .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;
        let provisioning = serde_json::to_value(&request.provisioning)
            .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;
        let redacted_summary = request.redacted_summary();

        let requested_by = request.requested_by.clone();
        let max_attempts = request.max_attempts_or_default();
        let row = sqlx::query(
            r#"
            INSERT INTO public.database_clone_jobs (
                job_id, source, target, registration_policy, provisioning_policy,
                requested_by, status, phase, attempt_count, max_attempts, redacted_summary,
                created_at, updated_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, 'queued', 'validate_source', 0, $7, $8, now(), now())
            RETURNING *
            "#,
        )
        .bind(job_id.0)
        .bind(source)
        .bind(target)
        .bind(registration)
        .bind(provisioning)
        .bind(requested_by)
        .bind(max_attempts)
        .bind(redacted_summary)
        .fetch_one(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        self.map_job_row(row)
    }

    pub async fn list_jobs(&self) -> Result<Vec<CloneJobSummary>, ControlPlaneError> {
        let rows = sqlx::query(
            r#"
            SELECT *
            FROM public.database_clone_jobs
            ORDER BY created_at DESC
            "#,
        )
        .fetch_all(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        rows.into_iter()
            .map(|row| self.map_job_row(row).map(|record| record.summary()))
            .collect()
    }

    pub async fn get_job(
        &self,
        job_id: CloneJobId,
    ) -> Result<Option<CloneJobDetail>, ControlPlaneError> {
        let row = sqlx::query("SELECT * FROM public.database_clone_jobs WHERE job_id = $1")
            .bind(job_id.0)
            .fetch_optional(&self.pool)
            .await
            .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        row.map(|row| self.map_job_row(row).map(|record| record.detail()))
            .transpose()
    }

    pub async fn get_job_record(
        &self,
        job_id: CloneJobId,
    ) -> Result<Option<CloneJobRecord>, ControlPlaneError> {
        let row = sqlx::query("SELECT * FROM public.database_clone_jobs WHERE job_id = $1")
            .bind(job_id.0)
            .fetch_optional(&self.pool)
            .await
            .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        row.map(|row| self.map_job_row(row)).transpose()
    }

    pub async fn list_job_events(
        &self,
        job_id: CloneJobId,
    ) -> Result<Vec<CloneJobEvent>, ControlPlaneError> {
        let rows = sqlx::query(
            r#"
            SELECT *
            FROM public.database_clone_job_events
            WHERE job_id = $1
            ORDER BY created_at ASC
            "#,
        )
        .bind(job_id.0)
        .fetch_all(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        rows.into_iter().map(map_event_row).collect()
    }

    pub async fn append_event(
        &self,
        job_id: CloneJobId,
        daemon_id: Option<&DaemonId>,
        phase: CloneJobPhase,
        level: CloneEventLevel,
        kind: &str,
        message: &str,
        details: Value,
    ) -> Result<(), ControlPlaneError> {
        sqlx::query(
            r#"
            INSERT INTO public.database_clone_job_events (
                event_id, job_id, daemon_id, phase, level, kind, message, details, created_at
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, now())
            "#,
        )
        .bind(Uuid::new_v4())
        .bind(job_id.0)
        .bind(daemon_id.map(|value| value.as_str().to_string()))
        .bind(phase.as_str())
        .bind(level.as_str())
        .bind(kind)
        .bind(message)
        .bind(details)
        .execute(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;
        Ok(())
    }

    pub async fn cancel_job(
        &self,
        job_id: CloneJobId,
    ) -> Result<CloneJobRecord, ControlPlaneError> {
        let row = sqlx::query(
            r#"
            UPDATE public.database_clone_jobs
            SET
                cancel_requested = true,
                status = CASE
                    WHEN status IN ('completed', 'cancelled', 'failed_terminal') THEN status
                    ELSE 'cancel_requested'
                END,
                updated_at = now()
            WHERE job_id = $1
            RETURNING *
            "#,
        )
        .bind(job_id.0)
        .fetch_optional(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?
        .ok_or_else(|| ControlPlaneError::NotFound("clone job not found".to_string()))?;

        self.map_job_row(row)
    }

    pub async fn retry_job(&self, job_id: CloneJobId) -> Result<CloneJobRecord, ControlPlaneError> {
        let current = self
            .get_job_record(job_id)
            .await?
            .ok_or_else(|| ControlPlaneError::NotFound("clone job not found".to_string()))?;

        if !matches!(
            current.status,
            CloneJobStatus::FailedRetryable
                | CloneJobStatus::FailedTerminal
                | CloneJobStatus::Cancelled
        ) {
            return Err(ControlPlaneError::Conflict(
                "clone job is not in a terminal state and cannot be retried".to_string(),
            ));
        }

        let row = sqlx::query(
            r#"
            UPDATE public.database_clone_jobs
            SET
                status = 'queued',
                phase = 'validate_source',
                execution_daemon_id = NULL,
                lease_token = NULL,
                lease_expires_at = NULL,
                cancel_requested = false,
                verification_summary = NULL,
                failure_summary = NULL,
                completed_at = NULL,
                updated_at = now()
            WHERE job_id = $1
            RETURNING *
            "#,
        )
        .bind(job_id.0)
        .fetch_one(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        self.map_job_row(row)
    }

    pub async fn claim_next_job(
        &self,
        daemon_id: &DaemonId,
        lease_ttl_secs: i64,
    ) -> Result<Option<CloneJobRecord>, ControlPlaneError> {
        let lease_token = LeaseToken::new();
        let row = sqlx::query(
            r#"
            WITH candidate AS (
                SELECT job_id
                FROM public.database_clone_jobs
                WHERE
                    status = 'queued'
                    AND cancel_requested = false
                    AND attempt_count < max_attempts
                ORDER BY created_at ASC
                FOR UPDATE SKIP LOCKED
                LIMIT 1
            )
            UPDATE public.database_clone_jobs jobs
            SET
                status = 'preparing',
                execution_daemon_id = $1,
                lease_token = $2,
                lease_expires_at = now() + ($3::bigint * interval '1 second'),
                attempt_count = attempt_count + 1,
                started_at = COALESCE(started_at, now()),
                updated_at = now()
            FROM candidate
            WHERE jobs.job_id = candidate.job_id
            RETURNING jobs.*
            "#,
        )
        .bind(daemon_id.as_str())
        .bind(lease_token.0)
        .bind(lease_ttl_secs.max(30))
        .fetch_optional(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        row.map(|row| self.map_job_row(row)).transpose()
    }

    pub async fn heartbeat_lease(
        &self,
        job_id: CloneJobId,
        lease_token: LeaseToken,
        lease_ttl_secs: i64,
    ) -> Result<bool, ControlPlaneError> {
        let result = sqlx::query(
            r#"
            UPDATE public.database_clone_jobs
            SET lease_expires_at = now() + ($3::bigint * interval '1 second'), updated_at = now()
            WHERE job_id = $1 AND lease_token = $2
            "#,
        )
        .bind(job_id.0)
        .bind(lease_token.0)
        .bind(lease_ttl_secs.max(30))
        .execute(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?;

        Ok(result.rows_affected() > 0)
    }

    pub async fn set_phase_and_status(
        &self,
        job_id: CloneJobId,
        lease_token: LeaseToken,
        status: CloneJobStatus,
        phase: CloneJobPhase,
    ) -> Result<CloneJobRecord, ControlPlaneError> {
        let row = sqlx::query(
            r#"
            UPDATE public.database_clone_jobs
            SET status = $3, phase = $4, updated_at = now()
            WHERE job_id = $1 AND lease_token = $2
            RETURNING *
            "#,
        )
        .bind(job_id.0)
        .bind(lease_token.0)
        .bind(status.as_str())
        .bind(phase.as_str())
        .fetch_optional(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?
        .ok_or_else(|| ControlPlaneError::Conflict("clone job lease no longer held".to_string()))?;

        self.map_job_row(row)
    }

    pub async fn complete_job(
        &self,
        job_id: CloneJobId,
        lease_token: LeaseToken,
        verification_summary: Value,
    ) -> Result<CloneJobRecord, ControlPlaneError> {
        let row = sqlx::query(
            r#"
            UPDATE public.database_clone_jobs
            SET
                status = 'completed',
                phase = 'complete',
                verification_summary = $3,
                completed_at = now(),
                lease_token = NULL,
                lease_expires_at = NULL,
                updated_at = now()
            WHERE job_id = $1 AND lease_token = $2
            RETURNING *
            "#,
        )
        .bind(job_id.0)
        .bind(lease_token.0)
        .bind(verification_summary)
        .fetch_optional(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?
        .ok_or_else(|| ControlPlaneError::Conflict("clone job lease no longer held".to_string()))?;

        self.map_job_row(row)
    }

    pub async fn fail_job(
        &self,
        job_id: CloneJobId,
        lease_token: LeaseToken,
        failure_status: CloneJobStatus,
        failure_summary: Value,
    ) -> Result<CloneJobRecord, ControlPlaneError> {
        let row = sqlx::query(
            r#"
            UPDATE public.database_clone_jobs
            SET
                status = $3,
                failure_summary = $4,
                completed_at = now(),
                lease_token = NULL,
                lease_expires_at = NULL,
                updated_at = now()
            WHERE job_id = $1 AND lease_token = $2
            RETURNING *
            "#,
        )
        .bind(job_id.0)
        .bind(lease_token.0)
        .bind(failure_status.as_str())
        .bind(failure_summary)
        .fetch_optional(&self.pool)
        .await
        .map_err(|err| ControlPlaneError::Storage(err.to_string()))?
        .ok_or_else(|| ControlPlaneError::Conflict("clone job lease no longer held".to_string()))?;

        self.map_job_row(row)
    }

    fn map_job_row(&self, row: sqlx::postgres::PgRow) -> Result<CloneJobRecord, ControlPlaneError> {
        let source: Value = row.try_get("source").map_err(map_sql_error)?;
        let target: Value = row.try_get("target").map_err(map_sql_error)?;
        let registration: Value = row.try_get("registration_policy").map_err(map_sql_error)?;
        let provisioning: Value = row.try_get("provisioning_policy").map_err(map_sql_error)?;
        let execution_daemon_id = row
            .try_get::<Option<String>, _>("execution_daemon_id")
            .map_err(map_sql_error)?
            .map(DaemonId::new)
            .transpose()?;

        Ok(CloneJobRecord {
            job_id: CloneJobId(row.try_get("job_id").map_err(map_sql_error)?),
            source: serde_json::from_value(source)
                .map_err(|err| ControlPlaneError::Storage(err.to_string()))?,
            target: serde_json::from_value(target)
                .map_err(|err| ControlPlaneError::Storage(err.to_string()))?,
            registration: serde_json::from_value(registration)
                .map_err(|err| ControlPlaneError::Storage(err.to_string()))?,
            provisioning: serde_json::from_value(provisioning)
                .map_err(|err| ControlPlaneError::Storage(err.to_string()))?,
            requested_by: row.try_get("requested_by").map_err(map_sql_error)?,
            status: CloneJobStatus::parse(
                &row.try_get::<String, _>("status").map_err(map_sql_error)?,
            )?,
            phase: CloneJobPhase::parse(
                &row.try_get::<String, _>("phase").map_err(map_sql_error)?,
            )?,
            attempt_count: row.try_get("attempt_count").map_err(map_sql_error)?,
            max_attempts: row.try_get("max_attempts").map_err(map_sql_error)?,
            execution_daemon_id,
            lease_token: row
                .try_get::<Option<Uuid>, _>("lease_token")
                .map_err(map_sql_error)?
                .map(LeaseToken),
            lease_expires_at: row.try_get("lease_expires_at").map_err(map_sql_error)?,
            cancel_requested: row.try_get("cancel_requested").map_err(map_sql_error)?,
            redacted_summary: row.try_get("redacted_summary").map_err(map_sql_error)?,
            verification_summary: row.try_get("verification_summary").map_err(map_sql_error)?,
            failure_summary: row.try_get("failure_summary").map_err(map_sql_error)?,
            created_at: row.try_get("created_at").map_err(map_sql_error)?,
            started_at: row.try_get("started_at").map_err(map_sql_error)?,
            updated_at: row.try_get("updated_at").map_err(map_sql_error)?,
            completed_at: row.try_get("completed_at").map_err(map_sql_error)?,
        })
    }
}

fn map_event_row(row: sqlx::postgres::PgRow) -> Result<CloneJobEvent, ControlPlaneError> {
    Ok(CloneJobEvent {
        event_id: row.try_get("event_id").map_err(map_sql_error)?,
        job_id: row.try_get("job_id").map_err(map_sql_error)?,
        daemon_id: row.try_get("daemon_id").map_err(map_sql_error)?,
        phase: CloneJobPhase::parse(&row.try_get::<String, _>("phase").map_err(map_sql_error)?)?,
        level: CloneEventLevel::parse(&row.try_get::<String, _>("level").map_err(map_sql_error)?)?,
        kind: row.try_get("kind").map_err(map_sql_error)?,
        message: row.try_get("message").map_err(map_sql_error)?,
        details: row.try_get("details").map_err(map_sql_error)?,
        created_at: row.try_get("created_at").map_err(map_sql_error)?,
    })
}

fn map_sql_error(err: sqlx::Error) -> ControlPlaneError {
    ControlPlaneError::Storage(err.to_string())
}
