//! Database models for the SQLite persistence layer.
//!
//! Each struct maps 1:1 to a table. Helper methods handle serialization
//! quirks (JSON columns, timestamp formats).

use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;

use crate::id_types::{BookmarkId, BusserId, BusserStateId, FeedId, ItemId, QueryFeedId};
use crate::TIMESTAMP_FMT;

/// Parse a value or log a warning and return the default.
/// Used for data that we wrote ourselves (JSON) — parse failures
/// indicate DB corruption or a write bug, both extremely rare.
fn parse_or_default<T: Default>(result: Result<T, impl std::fmt::Display>, context: &str) -> T {
    match result {
        Ok(v) => v,
        Err(e) => {
            tracing::warn!(error = %e, context, "Parse failed, using default");
            T::default()
        }
    }
}

#[tracing::instrument(skip_all)]
/// Parse a timestamp string from SQLite, falling back to UNIX_EPOCH on failure
pub fn parse_timestamp(s: &str) -> DateTime<Utc> {
    s.parse::<DateTime<Utc>>()
        .or_else(|_| {
            chrono::NaiveDateTime::parse_from_str(s, TIMESTAMP_FMT).map(|ndt| ndt.and_utc())
        })
        .unwrap_or(DateTime::UNIX_EPOCH)
}

#[derive(Debug, Clone, FromRow, Serialize, Deserialize)]
/// Registered feed/busser source stored in the `feeds` table
pub struct DbFeed {
    /// Internal UUID.
    pub id: FeedId,
    /// Plugin/busser identifier this feed belongs to.
    pub busser_id: BusserId,
    /// Human-readable feed name.
    pub name: String,
    /// JSON-encoded plugin configuration for this feed.
    pub config: String,
    /// Whether auto-fetching is enabled for this feed.
    pub enabled: bool,
    /// Timestamp of the last successful fetch, if any.
    pub last_fetch: Option<String>,
    /// Number of consecutive fetch failures (0 = healthy).
    pub consecutive_failures: i64,
    /// Error message from the last failed fetch, if any.
    pub last_error: Option<String>,
    /// Timestamp of the last successful fetch, if any.
    pub last_success_at: Option<String>,
    /// Whether this feed has been auto-disabled by the circuit breaker.
    pub circuit_broken: bool,
    /// Row creation timestamp.
    pub created_at: String,
    /// Row last-modified timestamp.
    pub updated_at: String,
}

impl DbFeed {
    /// Deserialize the JSON config column into a `serde_json::Value`.
    #[tracing::instrument(skip_all)]
    pub fn config_json(&self) -> serde_json::Value {
        parse_or_default(
            serde_json::from_str(&self.config),
            "Failed to parse feed config JSON",
        )
    }
}

#[derive(Debug, Clone, FromRow, Serialize, Deserialize)]
/// Feed item stored in the `feed_items` table
pub struct DbFeedItem {
    /// Internal UUID.
    pub id: ItemId,
    /// Busser-provided unique key (format: `busser_id:item_id`).
    pub external_id: String,
    /// Foreign key to the parent `feeds` row.
    pub feed_id: FeedId,
    /// Plugin/busser that produced this item.
    pub busser_id: BusserId,

    /// Author attribution for the compact bite view.
    pub bite_author: String,
    /// Primary text shown in the bite view.
    pub bite_text: String,
    /// Secondary info (score, retweet count, etc.) for the bite view.
    pub bite_secondary: Option<String>,
    /// Type indicator (emoji/icon) for the bite view.
    pub bite_indicator: Option<String>,

    /// Full article/post title.
    pub title: Option<String>,
    /// Full body/content text.
    pub body: Option<String>,
    /// URL to the original content.
    pub url: Option<String>,
    /// JSON-encoded list of media attachment URLs.
    pub media: String,
    /// JSON-encoded list of plugin-declared custom actions.
    pub actions: String,

    /// When the item was originally published (SQLite timestamp text).
    pub published_at: String,
    /// When we fetched this item (SQLite timestamp text).
    pub fetched_at: String,
    /// Human-readable source name for display.
    pub source_name: String,
    /// Engagement score (likes, upvotes, etc.).
    pub score: Option<i64>,
    /// JSON-encoded list of tags/categories.
    pub tags: String,

    /// Whether the user has read this item.
    pub is_read: bool,
    /// Whether the user has starred this item.
    pub is_starred: bool,

    /// Row creation timestamp.
    pub created_at: String,
    /// Row last-modified timestamp.
    pub updated_at: String,
}

impl DbFeedItem {
    /// Parse `published_at` into a `DateTime<Utc>`.
    #[tracing::instrument(skip_all)]
    pub fn published_at_dt(&self) -> DateTime<Utc> {
        parse_timestamp(&self.published_at)
    }

    /// Parse `fetched_at` into a `DateTime<Utc>`.
    #[tracing::instrument(skip_all)]
    pub fn fetched_at_dt(&self) -> DateTime<Utc> {
        parse_timestamp(&self.fetched_at)
    }

    /// Deserialize the JSON `media` column into a `Vec<String>`.
    #[tracing::instrument(skip_all)]
    pub fn media_vec(&self) -> Vec<String> {
        parse_or_default(
            serde_json::from_str(&self.media),
            "Failed to parse feed item media JSON",
        )
    }

    /// Deserialize the JSON `tags` column into a `Vec<String>`.
    #[tracing::instrument(skip_all)]
    pub fn tags_vec(&self) -> Vec<String> {
        parse_or_default(
            serde_json::from_str(&self.tags),
            "Failed to parse feed item tags JSON",
        )
    }

    /// Deserialize the JSON `actions` column into a `Vec<ItemAction>`.
    #[tracing::instrument(skip_all)]
    pub fn actions_vec(&self) -> Vec<bb_interface::ItemAction> {
        parse_or_default(
            serde_json::from_str(&self.actions),
            "Failed to parse feed item actions JSON",
        )
    }

    /// Reconstruct a full [`FeedItem`](bb_interface::FeedItem) from the flat DB row.
    ///
    /// The interface type splits an item into three parts:
    /// 1. **Bite** — compact list-view display (author, text, secondary, indicator)
    /// 2. **Content** — full article (title, body, url, media)
    /// 3. **Meta** — metadata (source name, timestamps, score, tags)
    ///
    /// This method maps the flat `DbFeedItem` columns back into those three
    /// structs, deserializing JSON columns (media, tags) along the way.
    #[tracing::instrument(skip_all)]
    pub fn to_feed_item(&self) -> bb_interface::FeedItem {
        use bb_interface::{BiteDisplay, FeedItem, FeedItemContent, FeedItemId, FeedItemMeta};

        let id = FeedItemId::new(&*self.busser_id, &self.external_id);

        // 1. Bite display — compact list row
        let mut bite = BiteDisplay::new(&self.bite_author, &self.bite_text);
        bite.secondary = self.bite_secondary.clone();
        bite.indicator = self.bite_indicator.clone();

        // 2. Content — full article view
        let mut content = FeedItemContent::new();
        content.title = self.title.clone();
        content.body = self.body.clone();
        content.url = self.url.clone();
        content.media = self.media_vec();
        content.actions = self.actions_vec();

        // 3. Meta — timestamps, score, tags
        let published_at = self.published_at_dt();
        let mut meta = FeedItemMeta::new(&self.source_name, published_at.timestamp());
        meta.fetched_at = self.fetched_at_dt().timestamp();
        meta.score = self.score;
        meta.tags = self.tags_vec();

        let mut item = FeedItem::new(id, bite, content, meta);
        item.db_id = Some(self.id.to_string());
        item.is_read = self.is_read;
        item.is_starred = self.is_starred;
        item
    }
}

#[derive(Debug, Clone, FromRow, Serialize, Deserialize)]
/// Key-value state storage for bussers (e.g. cursors, tokens)
pub struct DbBusserState {
    /// Internal UUID.
    pub id: BusserStateId,
    /// Plugin/busser this state belongs to.
    pub busser_id: BusserId,
    /// State key (unique per busser).
    pub key: String,
    /// State value (opaque string).
    pub value: String,
    /// Row creation timestamp.
    pub created_at: String,
    /// Row last-modified timestamp.
    pub updated_at: String,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
/// Input for creating a new feed row
pub struct CreateFeed {
    /// Plugin/busser identifier.
    pub busser_id: BusserId,
    /// Human-readable name.
    pub name: String,
    /// Plugin-specific configuration (stored as JSON text).
    pub config: serde_json::Value,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
/// Input for inserting or upserting a feed item
pub struct CreateFeedItem {
    /// Busser-provided unique key (format: `busser_id:item_id`).
    pub external_id: String,
    /// Parent feed ID.
    pub feed_id: FeedId,
    /// Plugin/busser identifier.
    pub busser_id: BusserId,
    /// Author attribution for bite display.
    pub bite_author: String,
    /// Primary text for bite display.
    pub bite_text: String,
    /// Optional secondary info for bite display.
    pub bite_secondary: Option<String>,
    /// Optional type indicator for bite display.
    pub bite_indicator: Option<String>,
    /// Full article/post title.
    pub title: Option<String>,
    /// Full body/content text.
    pub body: Option<String>,
    /// URL to the original content.
    pub url: Option<String>,
    /// Media attachment URLs.
    pub media: Vec<String>,
    /// Plugin-declared custom actions.
    pub actions: Vec<bb_interface::ItemAction>,
    /// When the item was originally published.
    pub published_at: DateTime<Utc>,
    /// Human-readable source name.
    pub source_name: String,
    /// Engagement score.
    pub score: Option<i64>,
    /// Tags/categories.
    pub tags: Vec<String>,
}

impl CreateFeedItem {
    /// Convert an interface [`FeedItem`](bb_interface::FeedItem) into a database insert input.
    #[tracing::instrument(skip_all)]
    pub fn from_feed_item(item: &bb_interface::FeedItem, feed_id: FeedId) -> Self {
        Self {
            external_id: item.id.to_combined(),
            feed_id,
            busser_id: BusserId::new(&item.id.source),
            bite_author: item.bite.author.clone(),
            bite_text: item.bite.text.clone(),
            bite_secondary: item.bite.secondary.clone(),
            bite_indicator: item.bite.indicator.clone(),
            title: item.content.title.clone(),
            body: item.content.body.clone(),
            url: item.content.url.clone(),
            media: item.content.media.clone(),
            actions: item.content.actions.clone(),
            published_at: DateTime::from_timestamp(item.meta.published_at, 0)
                .unwrap_or_else(Utc::now),
            source_name: item.meta.source_name.clone(),
            score: item.meta.score,
            tags: item.meta.tags.clone(),
        }
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
/// A single condition in a query feed's rules array
pub struct QueryCondition {
    /// The item field to match against.
    ///
    /// Valid values:
    /// - `"title"` — full article/post title (in-memory filter)
    /// - `"author"` — bite display author (in-memory filter)
    /// - `"body"` — full body/content text (in-memory filter)
    /// - `"source"` — busser source ID (fast-path SQL)
    /// - `"tag"` — item-level tag (fast-path SQL)
    /// - `"starred"` — starred boolean state (fast-path SQL)
    /// - `"unread"` — unread boolean state (fast-path SQL)
    pub field: String,
    /// The comparison operator to apply.
    ///
    /// For text fields (title, author, body):
    /// - `"contains"` — case-insensitive substring match
    /// - `"not_contains"` — negated case-insensitive substring match
    /// - `"equals"` — case-insensitive exact match
    /// - `"matches_regex"` — Rust `regex` crate pattern match
    ///
    /// For fast-path fields:
    /// - `"is"` — boolean check, used with starred/unread
    /// - `"equals"` — exact match, used with source/tag
    pub operator: String,
    /// The comparison value.
    ///
    /// For boolean operators (`"is"`), use `"true"` or `"false"`.
    /// For text operators, the value is matched case-insensitively against the
    /// field content (substring for contains, exact for equals, regex pattern
    /// for matches_regex).
    pub value: String,
}

#[derive(Debug, Clone, FromRow, Serialize, Deserialize)]
/// Saved filter ("query feed") stored in the `query_feeds` table
pub struct DbQueryFeed {
    pub id: QueryFeedId,
    pub name: String,
    /// JSON array of [`QueryCondition`].
    pub rules: String,
    pub created_at: String,
    pub updated_at: String,
}

impl DbQueryFeed {
    /// Deserialize the JSON `rules` column into a `Vec<QueryCondition>`.
    #[tracing::instrument(skip_all)]
    pub fn rules_vec(&self) -> Vec<QueryCondition> {
        parse_or_default(
            serde_json::from_str(&self.rules),
            "Failed to parse query feed rules JSON",
        )
    }
}

#[derive(Debug, Clone, Serialize, Deserialize)]
/// Input for creating a new query feed
pub struct CreateQueryFeed {
    /// Human-readable name for this query feed, displayed in the feed list.
    pub name: String,
    /// Filter rules applied with AND logic. See [`QueryCondition`] for valid
    /// field/operator/value combinations.
    pub rules: Vec<QueryCondition>,
}

#[derive(Debug, Clone, FromRow, Serialize, Deserialize)]
/// Bookmark stored in the `bookmarks` table
pub struct DbBookmark {
    /// Internal UUID.
    pub id: BookmarkId,
    /// Bookmarked URL.
    pub url: String,
    /// Display title.
    pub title: String,
    /// Short description or excerpt.
    pub description: String,
    /// Author attribution.
    pub author: String,
    /// Source name for display.
    pub source_name: String,
    /// Optional link to the originating feed item (SET NULL on item deletion).
    pub feed_item_id: Option<String>,
    /// User's personal notes.
    pub notes: String,
    /// Whether this bookmark is pinned to the top.
    pub is_pinned: bool,
    /// Row creation timestamp.
    pub created_at: String,
    /// Row last-modified timestamp.
    pub updated_at: String,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
/// Input for creating a new bookmark
pub struct CreateBookmark {
    /// URL to bookmark.
    pub url: String,
    /// Display title.
    pub title: String,
    /// Short description or excerpt.
    pub description: String,
    /// Author attribution.
    pub author: String,
    /// Source name for display.
    pub source_name: String,
    /// Optional link to an existing feed item.
    pub feed_item_id: Option<String>,
    /// User's personal notes.
    pub notes: String,
    /// Tags to assign.
    pub tags: Vec<String>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
/// Input for updating an existing bookmark
pub struct UpdateBookmark {
    /// Updated title.
    pub title: Option<String>,
    /// Updated description.
    pub description: Option<String>,
    /// Updated notes.
    pub notes: Option<String>,
    /// Updated pin state.
    pub is_pinned: Option<bool>,
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn parse_timestamp_rfc3339() {
        let ts = "2024-01-15T10:30:00Z";
        let dt = parse_timestamp(ts);
        assert_eq!(dt.year(), 2024);
        assert_eq!(dt.month(), 1);
    }

    #[test]
    fn parse_timestamp_sqlite_format() {
        let ts = "2024-06-01 12:00:00";
        let dt = parse_timestamp(ts);
        assert_eq!(dt.year(), 2024);
        assert_eq!(dt.month(), 6);
    }

    #[test]
    fn parse_timestamp_garbage_returns_epoch() {
        let dt = parse_timestamp("not a date");
        assert_eq!(dt, DateTime::UNIX_EPOCH);
    }

    #[test]
    fn parse_timestamp_empty_returns_epoch() {
        let dt = parse_timestamp("");
        assert_eq!(dt, DateTime::UNIX_EPOCH);
    }

    #[test]
    fn parse_timestamp_valid_rfc3339() {
        let dt = parse_timestamp("2025-06-15T10:30:00Z");
        assert_eq!(dt.year(), 2025);
        assert_eq!(dt.month(), 6);
        assert_eq!(dt.day(), 15);
    }

    #[test]
    fn parse_timestamp_valid_sqlite_format() {
        let dt = parse_timestamp("2024-03-20 08:45:00");
        assert_eq!(dt.year(), 2024);
        assert_eq!(dt.month(), 3);
    }

    #[test]
    fn db_feed_id_roundtrip() {
        let expected: FeedId = "550e8400-e29b-41d4-a716-446655440000".parse().unwrap();
        let feed = DbFeed {
            id: expected,
            busser_id: BusserId::new("rss"),
            name: "Test".to_string(),
            config: "{}".to_string(),
            enabled: true,
            last_fetch: None,
            consecutive_failures: 0,
            last_error: None,
            last_success_at: None,
            circuit_broken: false,
            created_at: "2024-01-01 00:00:00".to_string(),
            updated_at: "2024-01-01 00:00:00".to_string(),
        };
        assert_eq!(feed.id, expected);
        assert_eq!(
            feed.id.to_string(),
            "550e8400-e29b-41d4-a716-446655440000"
        );
    }

    #[test]
    fn db_feed_config_json_valid() {
        let feed = DbFeed {
            id: FeedId::new(),
            busser_id: BusserId::new("rss"),
            name: "Test".to_string(),
            config: r#"{"url":"https://example.com"}"#.to_string(),
            enabled: true,
            last_fetch: None,
            consecutive_failures: 0,
            last_error: None,
            last_success_at: None,
            circuit_broken: false,
            created_at: "2024-01-01 00:00:00".to_string(),
            updated_at: "2024-01-01 00:00:00".to_string(),
        };
        let json = feed.config_json();
        assert_eq!(json["url"], "https://example.com");
    }

    #[test]
    fn db_feed_config_json_invalid_returns_default() {
        let feed = DbFeed {
            id: FeedId::new(),
            busser_id: BusserId::new("rss"),
            name: "Test".to_string(),
            config: "not json".to_string(),
            enabled: true,
            last_fetch: None,
            consecutive_failures: 0,
            last_error: None,
            last_success_at: None,
            circuit_broken: false,
            created_at: "2024-01-01 00:00:00".to_string(),
            updated_at: "2024-01-01 00:00:00".to_string(),
        };
        let json = feed.config_json();
        assert!(json.is_null());
    }

    use chrono::Datelike;
}