use super::{
    CreateFeedItem, DateTime, DbFeedItem, FeedId, ItemId, MAX_SEARCH_QUERY_LENGTH, SqlitePool,
    TIMESTAMP_FMT, Utc, sanitize_fts_query,
};

#[derive(Clone)]
/// Repository for feed item CRUD, read/star toggling, and paginated listing
pub struct ItemsRepository {
    pool: SqlitePool,
}

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

    /// Insert a feed item or update it if `external_id` already exists.
    ///
    /// The ON CONFLICT clause deliberately preserves user state (`is_read`,
    /// `is_starred`), these are never overwritten by a re-fetch. Content fields
    /// (author, text, body, etc.) are updated in case the source edited the post.
    #[tracing::instrument(skip_all)]
    pub async fn upsert(&self, input: CreateFeedItem) -> Result<DbFeedItem, sqlx::Error> {
        let id = ItemId::new();
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        let published = input.published_at.format(TIMESTAMP_FMT).to_string();
        // Vec<String> is always serializable; unwrap is safe here.
        let media = serde_json::to_string(&input.media).unwrap_or_else(|_| "[]".to_string());
        let tags = serde_json::to_string(&input.tags).unwrap_or_else(|_| "[]".to_string());
        let actions = serde_json::to_string(&input.actions).unwrap_or_else(|_| "[]".to_string());

        sqlx::query_as(
            r"
            INSERT INTO feed_items (
                id, external_id, feed_id, busser_id,
                bite_author, bite_text, bite_secondary, bite_indicator,
                title, body, url, media, actions,
                published_at, fetched_at, source_name, score, tags,
                is_read, is_starred, created_at, updated_at
            )
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, 0, 0, ?19, ?19)
            ON CONFLICT (external_id) DO UPDATE SET
                bite_author = EXCLUDED.bite_author,
                bite_text = EXCLUDED.bite_text,
                bite_secondary = EXCLUDED.bite_secondary,
                bite_indicator = EXCLUDED.bite_indicator,
                title = EXCLUDED.title,
                body = EXCLUDED.body,
                url = EXCLUDED.url,
                media = EXCLUDED.media,
                actions = EXCLUDED.actions,
                score = EXCLUDED.score,
                tags = EXCLUDED.tags,
                updated_at = EXCLUDED.updated_at
            RETURNING *
            ",
        )
        .bind(id)
        .bind(&input.external_id)
        .bind(input.feed_id)
        .bind(input.busser_id.as_str())
        .bind(&input.bite_author)
        .bind(&input.bite_text)
        .bind(&input.bite_secondary)
        .bind(&input.bite_indicator)
        .bind(&input.title)
        .bind(&input.body)
        .bind(&input.url)
        .bind(&media)
        .bind(&actions)
        .bind(&published)
        .bind(&now)
        .bind(&input.source_name)
        .bind(input.score)
        .bind(&tags)
        .bind(&now)
        .fetch_one(&self.pool)
        .await
    }

    /// Look up a feed item by its internal ID.
    #[tracing::instrument(skip_all)]
    pub async fn get(&self, id: ItemId) -> Result<Option<DbFeedItem>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feed_items WHERE id = ?1")
            .bind(id)
            .fetch_optional(&self.pool)
            .await
    }

    /// Look up a feed item by the `external_id` produced by the busser.
    #[tracing::instrument(skip_all)]
    pub async fn get_by_external_id(
        &self,
        external_id: &str,
    ) -> Result<Option<DbFeedItem>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feed_items WHERE external_id = ?1")
            .bind(external_id)
            .fetch_optional(&self.pool)
            .await
    }

    /// List all items ordered by `published_at` descending, with pagination.
    /// Sorted by publish date (not fetch date) so items appear where the author
    /// intended, even if fetched out of order during backfill.
    #[tracing::instrument(skip_all)]
    pub async fn list_all(&self, limit: i64, offset: i64) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM feed_items ORDER BY published_at DESC LIMIT ?1 OFFSET ?2")
            .bind(limit)
            .bind(offset)
            .fetch_all(&self.pool)
            .await
    }

    /// List items belonging to a specific feed, newest first.
    #[tracing::instrument(skip_all)]
    pub async fn list_by_feed(
        &self,
        feed_id: FeedId,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        sqlx::query_as(
            "SELECT * FROM feed_items WHERE feed_id = ?1 ORDER BY published_at DESC LIMIT ?2 OFFSET ?3",
        )
        .bind(feed_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(&self.pool)
        .await
    }

    /// List items from a specific busser source, newest first.
    #[tracing::instrument(skip_all)]
    pub async fn list_by_busser(
        &self,
        busser_id: &str,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        sqlx::query_as(
            "SELECT * FROM feed_items WHERE busser_id = ?1 ORDER BY published_at DESC LIMIT ?2 OFFSET ?3",
        )
        .bind(busser_id)
        .bind(limit)
        .bind(offset)
        .fetch_all(&self.pool)
        .await
    }

    /// List unread items only, newest first.
    #[tracing::instrument(skip_all)]
    pub async fn list_unread(
        &self,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        sqlx::query_as(
            "SELECT * FROM feed_items WHERE is_read = 0 ORDER BY published_at DESC LIMIT ?1 OFFSET ?2",
        )
        .bind(limit)
        .bind(offset)
        .fetch_all(&self.pool)
        .await
    }

    /// List starred items only, newest first.
    #[tracing::instrument(skip_all)]
    pub async fn list_starred(
        &self,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        sqlx::query_as(
            "SELECT * FROM feed_items WHERE is_starred = 1 ORDER BY published_at DESC LIMIT ?1 OFFSET ?2",
        )
        .bind(limit)
        .bind(offset)
        .fetch_all(&self.pool)
        .await
    }

    /// Full-text search using FTS5 across title, body, and bite_text.
    ///
    /// Uses the `feed_items_fts` virtual table for fast ranked search.
    /// Results are ordered by FTS5 relevance then by `published_at DESC`.
    /// Additional boolean filters (source, unread, starred) are combined.
    #[tracing::instrument(skip_all)]
    pub async fn list_search(
        &self,
        query: &str,
        source: Option<&str>,
        unread_only: bool,
        starred_only: bool,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        // Reject excessively long queries before any processing.
        if query.len() > MAX_SEARCH_QUERY_LENGTH {
            return Ok(vec![]);
        }

        let fts_query = sanitize_fts_query(query);

        // If sanitization stripped everything (e.g. query was just `*` or `^`),
        // return early, an empty MATCH expression would be a SQL error.
        if fts_query.is_empty() {
            return Ok(vec![]);
        }

        let mut sql = String::from(
            "SELECT fi.* FROM feed_items fi \
             INNER JOIN feed_items_fts fts ON fi.rowid = fts.rowid \
             WHERE feed_items_fts MATCH ?1",
        );
        if source.is_some() {
            sql.push_str(" AND fi.busser_id = ?4");
        }
        if unread_only {
            sql.push_str(" AND fi.is_read = 0");
        }
        if starred_only {
            sql.push_str(" AND fi.is_starred = 1");
        }
        sql.push_str(" ORDER BY fts.rank, fi.published_at DESC LIMIT ?2 OFFSET ?3");

        let mut q = sqlx::query_as::<_, DbFeedItem>(sqlx::AssertSqlSafe(sql.as_str()))
            .bind(&fts_query) // ?1
            .bind(limit) // ?2
            .bind(offset); // ?3

        if let Some(src) = source {
            q = q.bind(src); // ?4
        }

        q.fetch_all(&self.pool).await
    }

    /// List feed items with any combination of filters pushed into SQL.
    ///
    /// Unifies `list_all`, `list_by_busser`, `list_unread`, `list_starred`,
    /// and `list_search` into a single method so callers don't need an
    /// if/else chain that drops filter combinations.
    #[tracing::instrument(skip_all)]
    pub async fn list_filtered(
        &self,
        search: Option<&str>,
        source: Option<&str>,
        unread_only: bool,
        starred_only: bool,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<DbFeedItem>, sqlx::Error> {
        // When a search query is present, use FTS5.
        if let Some(query) = search {
            return self
                .list_search(query, source, unread_only, starred_only, limit, offset)
                .await;
        }

        // Build a dynamic query with conditional WHERE clauses.
        let mut sql = String::from("SELECT * FROM feed_items WHERE 1=1");
        if source.is_some() {
            sql.push_str(" AND busser_id = ?3");
        }
        if unread_only {
            sql.push_str(" AND is_read = 0");
        }
        if starred_only {
            sql.push_str(" AND is_starred = 1");
        }
        sql.push_str(" ORDER BY published_at DESC LIMIT ?1 OFFSET ?2");

        let mut q = sqlx::query_as::<_, DbFeedItem>(sqlx::AssertSqlSafe(sql.as_str()))
            .bind(limit) // ?1
            .bind(offset); // ?2

        if let Some(src) = source {
            q = q.bind(src); // ?3
        }

        q.fetch_all(&self.pool).await
    }

    /// Set the read flag on a feed item.
    #[tracing::instrument(skip_all)]
    pub async fn mark_read(&self, id: ItemId, is_read: bool) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feed_items SET is_read = ?1, updated_at = ?2 WHERE id = ?3")
            .bind(is_read)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Set the starred flag on a feed item.
    #[tracing::instrument(skip_all)]
    pub async fn mark_starred(&self, id: ItemId, is_starred: bool) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        sqlx::query("UPDATE feed_items SET is_starred = ?1, updated_at = ?2 WHERE id = ?3")
            .bind(is_starred)
            .bind(&now)
            .bind(id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// Mark all unread items as read, optionally filtered to a specific source.
    #[tracing::instrument(skip_all)]
    pub async fn mark_all_read(&self, busser_id: Option<&str>) -> Result<u64, sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();
        let result = match busser_id {
            Some(id) => {
                sqlx::query(
                    "UPDATE feed_items SET is_read = 1, updated_at = ?1 WHERE is_read = 0 AND busser_id = ?2",
                )
                .bind(&now)
                .bind(id)
                .execute(&self.pool)
                .await?
            }
            None => {
                sqlx::query(
                    "UPDATE feed_items SET is_read = 1, updated_at = ?1 WHERE is_read = 0",
                )
                .bind(&now)
                .execute(&self.pool)
                .await?
            }
        };
        Ok(result.rows_affected())
    }

    /// Count all feed items.
    #[tracing::instrument(skip_all)]
    pub async fn count_all(&self) -> Result<i64, sqlx::Error> {
        let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM feed_items")
            .fetch_one(&self.pool)
            .await?;
        Ok(row.0)
    }

    /// Count items from a specific busser source.
    #[tracing::instrument(skip_all)]
    pub async fn count_by_busser(&self, busser_id: &str) -> Result<i64, sqlx::Error> {
        let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM feed_items WHERE busser_id = ?1")
            .bind(busser_id)
            .fetch_one(&self.pool)
            .await?;
        Ok(row.0)
    }

    /// Count unread items across all sources.
    #[tracing::instrument(skip_all)]
    pub async fn count_unread(&self) -> Result<i64, sqlx::Error> {
        let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM feed_items WHERE is_read = 0")
            .fetch_one(&self.pool)
            .await?;
        Ok(row.0)
    }

    /// Count starred items.
    #[tracing::instrument(skip_all)]
    pub async fn count_starred(&self) -> Result<i64, sqlx::Error> {
        let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM feed_items WHERE is_starred = 1")
            .fetch_one(&self.pool)
            .await?;
        Ok(row.0)
    }

    /// Count unread items from a specific busser source.
    #[tracing::instrument(skip_all)]
    pub async fn count_unread_by_busser(&self, busser_id: &str) -> Result<i64, sqlx::Error> {
        let row: (i64,) =
            sqlx::query_as("SELECT COUNT(*) FROM feed_items WHERE busser_id = ?1 AND is_read = 0")
                .bind(busser_id)
                .fetch_one(&self.pool)
                .await?;
        Ok(row.0)
    }

    /// Count items matching a combination of source, unread, and starred filters.
    #[tracing::instrument(skip_all)]
    pub async fn count_filtered(
        &self,
        source: Option<&str>,
        unread_only: bool,
        starred_only: bool,
    ) -> Result<i64, sqlx::Error> {
        let mut sql = "SELECT COUNT(*) FROM feed_items WHERE 1=1".to_string();
        if source.is_some() {
            sql.push_str(" AND busser_id = ?1");
        }
        if unread_only {
            sql.push_str(" AND is_read = 0");
        }
        if starred_only {
            sql.push_str(" AND is_starred = 1");
        }
        let mut query = sqlx::query_as::<_, (i64,)>(sqlx::AssertSqlSafe(sql.as_str()));
        if let Some(s) = source {
            query = query.bind(s);
        }
        let row = query.fetch_one(&self.pool).await?;
        Ok(row.0)
    }

    /// Get total and unread counts for all busser sources in a single query.
    ///
    /// Returns `(busser_id, total_count, unread_count)` tuples. This avoids
    /// issuing separate count queries per source (N+1 problem).
    pub async fn counts_by_busser(&self) -> Result<Vec<(String, i64, i64)>, sqlx::Error> {
        let rows: Vec<(String, i64, i64)> = sqlx::query_as(
            r"
            SELECT busser_id,
                   COUNT(*) AS total_count,
                   SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) AS unread_count
            FROM feed_items
            GROUP BY busser_id
            ",
        )
        .fetch_all(&self.pool)
        .await?;
        Ok(rows)
    }

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

    /// Delete all items belonging to a feed. Returns the number of rows removed.
    #[tracing::instrument(skip_all)]
    pub async fn delete_by_feed(&self, feed_id: FeedId) -> Result<u64, sqlx::Error> {
        let result = sqlx::query("DELETE FROM feed_items WHERE feed_id = ?1")
            .bind(feed_id)
            .execute(&self.pool)
            .await?;
        Ok(result.rows_affected())
    }

    /// Delete old read items that are not starred. Returns the number of rows removed.
    ///
    /// Items published before `before` that have been read and are not starred
    /// will be permanently deleted. Starred items are always preserved regardless
    /// of age or read status.
    #[tracing::instrument(skip_all)]
    pub async fn delete_stale_read(&self, before: DateTime<Utc>) -> Result<u64, sqlx::Error> {
        let cutoff = before.format(TIMESTAMP_FMT).to_string();
        let result = sqlx::query(
            "DELETE FROM feed_items WHERE is_read = 1 AND is_starred = 0 AND published_at < ?1",
        )
        .bind(&cutoff)
        .execute(&self.pool)
        .await?;
        Ok(result.rows_affected())
    }
}