use super::{
    CIRCUIT_BREAKER_THRESHOLD, CreateFeed, DbFeed, ErrorCategory, FeedId, SqlitePool,
    StructuredError, TIMESTAMP_FMT, Utc,
};

#[derive(Clone)]
/// Repository for feed subscription CRUD and fetch tracking
pub struct FeedsRepository {
    pool: SqlitePool,
}

impl FeedsRepository {
    /// Create a new feeds repository backed by the given pool.
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    /// Insert a new feed and return the created row.
    /// New feeds default to `enabled = 1` (auto-fetch active) and
    /// `created_at = updated_at` (no separate "first modified" vs "created" notion).
    #[tracing::instrument(skip_all)]
    pub async fn create(&self, input: CreateFeed) -> Result<DbFeed, sqlx::Error> {
        let id = FeedId::new();
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        // serde_json::Value is always serializable; unwrap is safe here.
        let config = serde_json::to_string(&input.config).unwrap_or_else(|_| "{}".to_string());

        sqlx::query_as(
            r"
            INSERT INTO feeds (id, busser_id, name, config, enabled, created_at, updated_at)
            VALUES (?1, ?2, ?3, ?4, 1, ?5, ?5)
            RETURNING *
            ",
        )
        .bind(id)
        .bind(input.busser_id.as_str())
        .bind(&input.name)
        .bind(&config)
        .bind(&now)
        .fetch_one(&self.pool)
        .await
    }

    /// Look up a single feed by its ID. Returns `None` if not found.
    #[tracing::instrument(skip_all)]
    pub async fn get(&self, id: FeedId) -> Result<Option<DbFeed>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feeds WHERE id = ?1")
            .bind(id)
            .fetch_optional(&self.pool)
            .await
    }

    /// List all feeds belonging to a given busser, ordered by name.
    #[tracing::instrument(skip_all)]
    pub async fn get_by_busser(&self, busser_id: &str) -> Result<Vec<DbFeed>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feeds WHERE busser_id = ?1 ORDER BY name")
            .bind(busser_id)
            .fetch_all(&self.pool)
            .await
    }

    /// List only enabled feeds that are not circuit-broken, ordered by name.
    #[tracing::instrument(skip_all)]
    pub async fn list_enabled(&self) -> Result<Vec<DbFeed>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feeds WHERE enabled = 1 AND circuit_broken = 0 ORDER BY name")
            .fetch_all(&self.pool)
            .await
    }

    /// List every feed (enabled or disabled), ordered by name.
    #[tracing::instrument(skip_all)]
    pub async fn list_all(&self) -> Result<Vec<DbFeed>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feeds ORDER BY name")
            .fetch_all(&self.pool)
            .await
    }

    /// Update the `last_fetch` and `updated_at` timestamps to now.
    #[tracing::instrument(skip_all)]
    pub async fn update_last_fetch(&self, id: FeedId) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feeds SET last_fetch = ?1, updated_at = ?1 WHERE id = ?2")
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Enable or disable a feed.
    #[tracing::instrument(skip_all)]
    pub async fn set_enabled(&self, id: FeedId, enabled: bool) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feeds SET enabled = ?1, updated_at = ?2 WHERE id = ?3")
            .bind(enabled)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Record a successful fetch: reset failure counter, clear error, update timestamps.
    #[tracing::instrument(skip_all)]
    pub async fn record_fetch_success(&self, id: FeedId) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query(
            "UPDATE feeds SET consecutive_failures = 0, last_error = NULL, \
             last_success_at = ?1, last_fetch = ?1, updated_at = ?1 WHERE id = ?2",
        )
        .bind(&now)
        .bind(id)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Record a fetch failure: increment counter, store error, update timestamp.
    ///
    /// Returns `true` if the circuit breaker tripped (i.e. the feed just crossed
    /// the [`CIRCUIT_BREAKER_THRESHOLD`] and was marked `circuit_broken = 1`).
    #[tracing::instrument(skip_all)]
    pub async fn record_fetch_failure(&self, id: FeedId, error: &str) -> Result<bool, sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query(
            "UPDATE feeds SET consecutive_failures = consecutive_failures + 1, \
             last_error = ?1, last_fetch = ?2, updated_at = ?2 WHERE id = ?3",
        )
        .bind(error)
        .bind(&now)
        .bind(id)
        .execute(&self.pool)
        .await?;

        // Check if we just crossed the threshold
        let feed = self.get(id).await?;
        if let Some(feed) = feed
            && !feed.circuit_broken
            && feed.consecutive_failures >= CIRCUIT_BREAKER_THRESHOLD
        {
            self.set_circuit_broken(id, true).await?;
            return Ok(true);
        }
        Ok(false)
    }

    /// Record a structured fetch failure with category-aware behavior:
    ///
    /// - `RateLimited`: store error JSON, do NOT increment `consecutive_failures`.
    /// - `Auth` / `Config`: increment + immediately set `circuit_broken = 1`.
    /// - `Transient` / `Parse` / `Unknown`: increment normally (existing behavior).
    ///
    /// Returns `true` if the circuit breaker tripped.
    #[tracing::instrument(skip_all)]
    pub async fn record_fetch_failure_structured(
        &self,
        id: FeedId,
        error: &StructuredError,
    ) -> Result<bool, sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        let error_json = error.to_json();

        match error.category {
            ErrorCategory::RateLimited => {
                // Store error but don't increment failure counter
                sqlx::query(
                    "UPDATE feeds SET last_error = ?1, last_fetch = ?2, updated_at = ?2 WHERE id = ?3",
                )
                .bind(&error_json)
                .bind(&now)
                .bind(id)
                .execute(&self.pool)
                .await?;
                Ok(false)
            }
            ErrorCategory::Auth | ErrorCategory::Config => {
                // Increment + immediate circuit break
                sqlx::query(
                    "UPDATE feeds SET consecutive_failures = consecutive_failures + 1, \
                     last_error = ?1, last_fetch = ?2, updated_at = ?2 WHERE id = ?3",
                )
                .bind(&error_json)
                .bind(&now)
                .bind(id)
                .execute(&self.pool)
                .await?;
                self.set_circuit_broken(id, true).await?;
                Ok(true)
            }
            ErrorCategory::Transient | ErrorCategory::Parse | ErrorCategory::Unknown => {
                // Normal behavior: increment and check threshold
                sqlx::query(
                    "UPDATE feeds SET consecutive_failures = consecutive_failures + 1, \
                     last_error = ?1, last_fetch = ?2, updated_at = ?2 WHERE id = ?3",
                )
                .bind(&error_json)
                .bind(&now)
                .bind(id)
                .execute(&self.pool)
                .await?;

                let feed = self.get(id).await?;
                if let Some(feed) = feed
                    && !feed.circuit_broken
                    && feed.consecutive_failures >= CIRCUIT_BREAKER_THRESHOLD
                {
                    self.set_circuit_broken(id, true).await?;
                    return Ok(true);
                }
                Ok(false)
            }
        }
    }

    /// Mark a feed as circuit-broken (or clear the circuit breaker).
    #[tracing::instrument(skip_all)]
    pub async fn set_circuit_broken(&self, id: FeedId, broken: bool) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feeds SET circuit_broken = ?1, updated_at = ?2 WHERE id = ?3")
            .bind(broken)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Reset the circuit breaker on a feed: clear `circuit_broken`, reset
    /// `consecutive_failures` to 0, and clear `last_error`.
    ///
    /// Called when a user manually triggers a fetch for a circuit-broken feed.
    #[tracing::instrument(skip_all)]
    pub async fn reset_circuit_breaker(&self, id: FeedId) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query(
            "UPDATE feeds SET circuit_broken = 0, consecutive_failures = 0, \
             last_error = NULL, updated_at = ?1 WHERE id = ?2",
        )
        .bind(&now)
        .bind(id)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Update a feed's config JSON string.
    #[tracing::instrument(skip_all)]
    pub async fn update_config(&self, id: FeedId, config: &str) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feeds SET config = ?1, updated_at = ?2 WHERE id = ?3")
            .bind(config)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Update a feed's display name.
    #[tracing::instrument(skip_all)]
    pub async fn update_name(&self, id: FeedId, name: &str) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feeds SET name = ?1, updated_at = ?2 WHERE id = ?3")
            .bind(name)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Delete a feed by ID.
    #[tracing::instrument(skip_all)]
    pub async fn delete(&self, id: FeedId) -> Result<(), sqlx::Error> {
        sqlx::query("DELETE FROM feeds WHERE id = ?1")
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }
}