use super::{
    BookmarkId, CreateBookmark, DbBookmark, SqlitePool, TIMESTAMP_FMT, UpdateBookmark, Utc,
};

#[derive(Clone)]
/// Repository for bookmark (reading list) CRUD
pub struct BookmarksRepository {
    pool: SqlitePool,
}

impl BookmarksRepository {
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }

    /// Insert a new bookmark and return the created row.
    #[tracing::instrument(skip_all)]
    pub async fn create(&self, input: CreateBookmark) -> Result<DbBookmark, sqlx::Error> {
        let id = BookmarkId::new();
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();

        let mut tx = self.pool.begin().await?;

        let bookmark: DbBookmark = sqlx::query_as(
            r"
            INSERT INTO bookmarks (id, url, title, description, author, source_name,
                                   feed_item_id, notes, is_pinned, created_at, updated_at)
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, 0, ?9, ?9)
            RETURNING *
            ",
        )
        .bind(id)
        .bind(&input.url)
        .bind(&input.title)
        .bind(&input.description)
        .bind(&input.author)
        .bind(&input.source_name)
        .bind(&input.feed_item_id)
        .bind(&input.notes)
        .bind(&now)
        .fetch_one(&mut *tx)
        .await?;

        // Insert tags
        for tag in &input.tags {
            let tag = tag.trim();
            if tag.is_empty() {
                continue;
            }
            sqlx::query("INSERT OR IGNORE INTO bookmark_tags (bookmark_id, tag) VALUES (?1, ?2)")
                .bind(id)
                .bind(tag)
                .execute(&mut *tx)
                .await?;
        }

        tx.commit().await?;
        Ok(bookmark)
    }

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

    /// Look up a bookmark by URL. Returns `None` if not found.
    #[tracing::instrument(skip_all)]
    pub async fn get_by_url(&self, url: &str) -> Result<Option<DbBookmark>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM bookmarks WHERE url = ?1")
            .bind(url)
            .fetch_optional(&self.pool)
            .await
    }

    /// Look up a bookmark by its linked feed item ID. Returns `None` if not found.
    #[tracing::instrument(skip_all)]
    pub async fn get_by_feed_item(
        &self,
        feed_item_id: &str,
    ) -> Result<Option<DbBookmark>, sqlx::Error> {
        sqlx::query_as("SELECT * FROM bookmarks WHERE feed_item_id = ?1")
            .bind(feed_item_id)
            .fetch_optional(&self.pool)
            .await
    }

    /// List bookmarks, optionally filtered by tag. Ordered by pinned first, then newest.
    #[tracing::instrument(skip_all)]
    pub async fn list(&self, tag: Option<&str>) -> Result<Vec<DbBookmark>, sqlx::Error> {
        match tag {
            Some(tag) => {
                sqlx::query_as(
                    r"
                    SELECT b.* FROM bookmarks b
                    INNER JOIN bookmark_tags bt ON bt.bookmark_id = b.id
                    WHERE bt.tag = ?1
                    ORDER BY b.is_pinned DESC, b.created_at DESC
                    ",
                )
                .bind(tag)
                .fetch_all(&self.pool)
                .await
            }
            None => {
                sqlx::query_as("SELECT * FROM bookmarks ORDER BY is_pinned DESC, created_at DESC")
                    .fetch_all(&self.pool)
                    .await
            }
        }
    }

    /// Update a bookmark's mutable fields.
    #[tracing::instrument(skip_all)]
    pub async fn update(&self, id: BookmarkId, input: UpdateBookmark) -> Result<(), sqlx::Error> {
        let now = Utc::now().format(TIMESTAMP_FMT).to_string();

        sqlx::query(
            r"
            UPDATE bookmarks SET
                title = COALESCE(?2, title),
                description = COALESCE(?3, description),
                notes = COALESCE(?4, notes),
                is_pinned = COALESCE(?5, is_pinned),
                updated_at = ?1
            WHERE id = ?6
            ",
        )
        .bind(&now)
        .bind(&input.title)
        .bind(&input.description)
        .bind(&input.notes)
        .bind(input.is_pinned.map(|b| b as i32))
        .bind(id)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

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

    /// Replace all tags for a bookmark (delete-all-then-insert).
    #[tracing::instrument(skip_all)]
    pub async fn set_tags(&self, id: BookmarkId, tags: &[String]) -> Result<(), sqlx::Error> {
        let mut tx = self.pool.begin().await?;

        sqlx::query("DELETE FROM bookmark_tags WHERE bookmark_id = ?1")
            .bind(id)
            .execute(&mut *tx)
            .await?;

        for tag in tags {
            let tag = tag.trim();
            if tag.is_empty() {
                continue;
            }
            sqlx::query("INSERT OR IGNORE INTO bookmark_tags (bookmark_id, tag) VALUES (?1, ?2)")
                .bind(id)
                .bind(tag)
                .execute(&mut *tx)
                .await?;
        }

        tx.commit().await?;
        Ok(())
    }

    /// Get all tags for a bookmark.
    #[tracing::instrument(skip_all)]
    pub async fn get_tags(&self, id: BookmarkId) -> Result<Vec<String>, sqlx::Error> {
        let rows: Vec<(String,)> =
            sqlx::query_as("SELECT tag FROM bookmark_tags WHERE bookmark_id = ?1 ORDER BY tag")
                .bind(id)
                .fetch_all(&self.pool)
                .await?;
        Ok(rows.into_iter().map(|r| r.0).collect())
    }

    /// Get all distinct tags across all bookmarks.
    #[tracing::instrument(skip_all)]
    pub async fn list_all_tags(&self) -> Result<Vec<String>, sqlx::Error> {
        let rows: Vec<(String,)> =
            sqlx::query_as("SELECT DISTINCT tag FROM bookmark_tags ORDER BY tag")
                .fetch_all(&self.pool)
                .await?;
        Ok(rows.into_iter().map(|r| r.0).collect())
    }

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