use super::{
    CreateQueryFeed, DbQueryFeed, QueryCondition, QueryFeedId, SqlitePool, TIMESTAMP_FMT, Utc,
};

#[derive(Clone)]
/// Repository for saved query feed (virtual source) CRUD
pub struct QueryFeedsRepository {
    pool: SqlitePool,
}

impl QueryFeedsRepository {
    #[tracing::instrument(skip_all)]
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    /// Insert a new query feed and return the created row.
    pub async fn create(&self, input: CreateQueryFeed) -> Result<DbQueryFeed, sqlx::Error> {
        let id = QueryFeedId::new();
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        let rules_json = serde_json::to_string(&input.rules).unwrap_or_else(|_| "[]".to_string());

        sqlx::query_as(
            r"
            INSERT INTO query_feeds (id, name, rules, created_at, updated_at)
            VALUES (?1, ?2, ?3, ?4, ?4)
            RETURNING *
            ",
        )
        .bind(id)
        .bind(&input.name)
        .bind(&rules_json)
        .bind(&now)
        .fetch_one(&self.pool)
        .await
    }

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

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

    /// Update a query feed's name and rules.
    #[tracing::instrument(skip_all)]
    pub async fn update(
        &self,
        id: QueryFeedId,
        name: &str,
        rules: &[QueryCondition],
    ) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        let rules_json = serde_json::to_string(rules).unwrap_or_else(|_| "[]".to_string());

        sqlx::query("UPDATE query_feeds SET name = ?1, rules = ?2, updated_at = ?3 WHERE id = ?4")
            .bind(name)
            .bind(&rules_json)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

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

// ── BookmarksRepository ──────────────────────────────────────────