use chrono::{DateTime, TimeZone, Utc};
use serde::{Deserialize, Serialize};
use sqlx::SqlitePool;
use uuid::Uuid;

/// Row from the `properties` table. Timestamps are stored as integer ms since
/// epoch; we keep them as `i64` here and convert at the rendering boundary.
#[allow(dead_code)]
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct PropertyRow {
    pub id: Vec<u8>,
    pub url: String,
    pub is_public: i64,
    pub is_protected: i64,

    pub last_run_at: Option<i64>,
    pub next_run_at: Option<i64>,

    pub last_run_at_crawler: Option<i64>,
    pub next_run_at_crawler: Option<i64>,
    pub crawler_insights: Option<String>,
    pub crawl_state: String,
    pub crawl_started_at: Option<i64>,
    pub last_crawl_success_at: Option<i64>,
    pub last_crawl_error: Option<String>,
    pub last_crawl_duration_ms: Option<i64>,
    pub last_crawl_pages_count: Option<i64>,

    pub lighthouse_scores: Option<String>,
    pub lighthouse_details: Option<String>,
    pub last_lighthouse_run_at: Option<i64>,
    pub last_lighthouse_success_at: Option<i64>,
    pub last_lighthouse_error: Option<String>,
    pub last_lighthouse_duration_ms: Option<i64>,
    pub next_lighthouse_run_at: Option<i64>,
    pub lighthouse_state: String,
    pub lighthouse_started_at: Option<i64>,

    pub alert_state: String,
    pub last_alert_sent: Option<i64>,

    pub created_at: i64,
    pub updated_at: i64,
}

impl PropertyRow {
    pub fn uuid(&self) -> Uuid {
        Uuid::from_slice(&self.id).unwrap_or(Uuid::nil())
    }

    /// "example.com" stripped of leading www. Used for display/title.
    pub fn name(&self) -> String {
        self.url
            .split('/')
            .nth(2)
            .unwrap_or(&self.url)
            .trim_start_matches("www.")
            .to_string()
    }
}

/// Minimal serializable shape used in templates. Contains everything every
/// template touches; we lift expensive computations (latest_headers,
/// recent_uptime_pct, etc.) into pre-computed fields fetched alongside.
#[derive(Debug, Serialize)]
pub struct PropertyContext {
    pub id: String,
    pub url: String,
    pub name: String,
    pub is_public: bool,
    pub is_protected: bool,

    pub current_status: i64,
    pub avg_response_time: i64,
    pub recent_uptime_pct: Option<f64>,
    pub recent_tick_stream: Vec<&'static str>,
    pub total_checks: i64,

    pub crawl_state: String,
    pub crawler_insights: serde_json::Value,
    pub last_crawl_success_at: Option<String>,
    pub last_crawl_error: Option<String>,
    pub last_crawl_duration_ms: Option<i64>,
    pub last_crawl_pages_count: Option<i64>,
    pub next_run_at_crawler: Option<String>,
    pub crawl_started_at: Option<String>,

    pub lighthouse_state: String,
    pub lighthouse_scores: serde_json::Value,
    pub lighthouse_details: serde_json::Value,
    pub last_lighthouse_success_at: Option<String>,
    pub last_lighthouse_error: Option<String>,
    pub last_lighthouse_duration_ms: Option<i64>,
    pub next_lighthouse_run_at: Option<String>,
    pub lighthouse_started_at: Option<String>,
    pub avg_lighthouse_score: Option<i64>,

    pub alert_state: String,
    pub created_at: String,
    pub updated_at: String,

    // Security flags derived from the latest response headers.
    pub is_https: bool,
    pub invalid_cert: bool,
    pub has_mime_type: bool,
    pub has_content_sniffing_protection: bool,
    pub has_clickjack_protection: bool,
    pub hides_server_version: bool,
    pub has_hsts: bool,
    pub has_hsts_preload: bool,
    pub has_security_issue: bool,
}

pub fn ms_to_iso(ms: i64) -> String {
    Utc.timestamp_millis_opt(ms)
        .single()
        .map(|d| d.to_rfc3339())
        .unwrap_or_default()
}

pub fn ms_to_iso_opt(ms: Option<i64>) -> Option<String> {
    ms.and_then(|m| Utc.timestamp_millis_opt(m).single().map(|d| d.to_rfc3339()))
}

#[allow(dead_code)]
pub fn ms_to_dt(ms: i64) -> DateTime<Utc> {
    Utc.timestamp_millis_opt(ms).single().unwrap_or_else(Utc::now)
}

pub async fn list_properties(
    pool: &SqlitePool,
    search: Option<&str>,
) -> sqlx::Result<Vec<PropertyRow>> {
    if let Some(q) = search {
        sqlx::query_as::<_, PropertyRow>(
            "SELECT * FROM properties WHERE url LIKE ? ORDER BY url",
        )
        .bind(format!("%{q}%"))
        .fetch_all(pool)
        .await
    } else {
        sqlx::query_as::<_, PropertyRow>("SELECT * FROM properties ORDER BY url")
            .fetch_all(pool)
            .await
    }
}

pub async fn get_property(pool: &SqlitePool, id: Uuid) -> sqlx::Result<Option<PropertyRow>> {
    sqlx::query_as::<_, PropertyRow>("SELECT * FROM properties WHERE id = ?")
        .bind(id.as_bytes().to_vec())
        .fetch_optional(pool)
        .await
}

pub async fn delete_property(pool: &SqlitePool, id: Uuid) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM properties WHERE id = ? AND is_protected = 0")
        .bind(id.as_bytes().to_vec())
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn create_property(pool: &SqlitePool, url: &str) -> sqlx::Result<Uuid> {
    let id = Uuid::new_v4();
    let now = crate::db::now_ms();
    sqlx::query(
        r#"INSERT INTO properties (id, url, created_at, updated_at)
           VALUES (?, ?, ?, ?)"#,
    )
    .bind(id.as_bytes().to_vec())
    .bind(url)
    .bind(now)
    .bind(now)
    .execute(pool)
    .await?;
    Ok(id)
}

pub async fn toggle_public(pool: &SqlitePool, id: Uuid) -> sqlx::Result<bool> {
    let row: Option<(i64,)> = sqlx::query_as("SELECT is_public FROM properties WHERE id = ?")
        .bind(id.as_bytes().to_vec())
        .fetch_optional(pool)
        .await?;
    let Some((cur,)) = row else { return Ok(false) };
    let new = if cur == 0 { 1 } else { 0 };
    sqlx::query("UPDATE properties SET is_public = ?, updated_at = ? WHERE id = ?")
        .bind(new)
        .bind(crate::db::now_ms())
        .bind(id.as_bytes().to_vec())
        .execute(pool)
        .await?;
    Ok(new == 1)
}

#[derive(Debug, Clone, sqlx::FromRow, Serialize, Deserialize)]
pub struct CheckRow {
    pub id: i64,
    pub property_id: Vec<u8>,
    pub status_code: i64,
    pub response_ms: i64,
    pub headers: String,
    pub created_at: i64,
    // Phase-by-phase timings (added in migration 0002). NULL for rows
    // written before the rewrite to a phased prober; new rows always have
    // dns_ms/tcp_ms/ttfb_ms set, and tls_ms set for HTTPS targets only.
    #[serde(default)]
    pub dns_ms: Option<i64>,
    #[serde(default)]
    pub tcp_ms: Option<i64>,
    #[serde(default)]
    pub tls_ms: Option<i64>,
    #[serde(default)]
    pub ttfb_ms: Option<i64>,
}

pub async fn recent_checks(
    pool: &SqlitePool,
    property_id: Uuid,
    limit: i64,
) -> sqlx::Result<Vec<CheckRow>> {
    sqlx::query_as::<_, CheckRow>(
        "SELECT * FROM checks WHERE property_id = ? ORDER BY created_at DESC LIMIT ?",
    )
    .bind(property_id.as_bytes().to_vec())
    .bind(limit)
    .fetch_all(pool)
    .await
}

pub async fn count_status_codes(
    pool: &SqlitePool,
    property_id: Uuid,
) -> sqlx::Result<Vec<(i64, i64)>> {
    sqlx::query_as::<_, (i64, i64)>(
        "SELECT status_code, COUNT(*) FROM checks WHERE property_id = ? GROUP BY status_code",
    )
    .bind(property_id.as_bytes().to_vec())
    .fetch_all(pool)
    .await
}

pub async fn count_checks(pool: &SqlitePool, property_id: Uuid) -> sqlx::Result<i64> {
    let (n,): (i64,) = sqlx::query_as("SELECT COUNT(*) FROM checks WHERE property_id = ?")
        .bind(property_id.as_bytes().to_vec())
        .fetch_one(pool)
        .await?;
    Ok(n)
}

pub async fn count_uptime(
    pool: &SqlitePool,
    property_id: Uuid,
) -> sqlx::Result<(i64, i64)> {
    let (up, down): (i64, i64) = sqlx::query_as(
        "SELECT \
           SUM(CASE WHEN status_code = 200 THEN 1 ELSE 0 END), \
           SUM(CASE WHEN status_code <> 200 THEN 1 ELSE 0 END) \
         FROM checks WHERE property_id = ?",
    )
    .bind(property_id.as_bytes().to_vec())
    .fetch_one(pool)
    .await
    .unwrap_or((0, 0));
    Ok((up, down))
}