use super::models::{AlertPref, RetentionPref, SourceRow, StoredClip, StoredDevice};
use super::{Store, StoreError};
use rusqlite::params;

pub fn insert_clip(store: &Store, c: &StoredClip) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute(
            r#"INSERT OR REPLACE INTO clips
               (id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at)
               VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)"#,
            params![
                c.id, c.source, c.source_key, c.content_type, c.content,
                c.media_path, c.byte_size, c.created_at,
                if c.pinned { 1i64 } else { 0 }, c.pinned_at,
            ],
        )?;
        Ok(())
    })
}

pub fn list_clips(
    store: &Store,
    from: Option<&str>,
    limit: Option<i64>,
    since_ms: Option<i64>,
    pinned_only: bool,
    default_limit: i64,
) -> Result<Vec<StoredClip>, StoreError> {
    let mut sql = String::from(
        "SELECT id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at
         FROM clips WHERE 1=1"
    );
    let mut binds: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
    if let Some(s) = from {
        sql.push_str(" AND source = ?");
        binds.push(Box::new(s.to_string()));
    }
    if let Some(t) = since_ms {
        sql.push_str(" AND created_at >= ?");
        binds.push(Box::new(t));
    }
    if pinned_only {
        sql.push_str(" AND pinned = 1");
    }
    sql.push_str(" ORDER BY created_at DESC LIMIT ?");
    binds.push(Box::new(limit.unwrap_or(default_limit)));

    store.with_conn(|conn| {
        let mut stmt = conn.prepare(&sql)?;
        let rows: Vec<StoredClip> = stmt
            .query_map(
                rusqlite::params_from_iter(binds.iter().map(|b| &**b as &dyn rusqlite::ToSql)),
                |r| {
                    Ok(StoredClip {
                        id: r.get(0)?,
                        source: r.get(1)?,
                        source_key: r.get(2)?,
                        content_type: r.get(3)?,
                        content: r.get(4)?,
                        media_path: r.get(5)?,
                        byte_size: r.get(6)?,
                        created_at: r.get(7)?,
                        pinned: r.get::<_, i64>(8)? != 0,
                        pinned_at: r.get(9)?,
                    })
                },
            )?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    })
}

pub fn get_clip(store: &Store, id: &str) -> Result<Option<StoredClip>, StoreError> {
    store.with_conn(|conn| {
        let mut stmt = conn.prepare(
            "SELECT id, source, source_key, content_type, content, media_path, byte_size, created_at, pinned, pinned_at
             FROM clips WHERE id = ?1"
        )?;
        let mut rows = stmt.query_map(params![id], |r| Ok(StoredClip {
            id: r.get(0)?, source: r.get(1)?, source_key: r.get(2)?,
            content_type: r.get(3)?, content: r.get(4)?, media_path: r.get(5)?,
            byte_size: r.get(6)?, created_at: r.get(7)?,
            pinned: r.get::<_, i64>(8)? != 0, pinned_at: r.get(9)?,
        }))?;
        if let Some(row) = rows.next() { Ok(Some(row?)) } else { Ok(None) }
    })
}

pub fn delete_clip(store: &Store, id: &str) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute("DELETE FROM clips WHERE id = ?1", params![id])?;
        Ok(())
    })
}

pub fn set_pinned(store: &Store, id: &str, pinned: bool, when_ms: i64) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute(
            "UPDATE clips SET pinned = ?1, pinned_at = CASE WHEN ?1 = 1 THEN ?2 ELSE NULL END WHERE id = ?3",
            params![if pinned { 1i64 } else { 0 }, when_ms, id],
        )?;
        Ok(())
    })
}

pub fn list_sources(store: &Store) -> Result<Vec<SourceRow>, StoreError> {
    store.with_conn(|conn| {
        let mut stmt = conn.prepare(
            "SELECT source, COUNT(*) AS c, MAX(created_at) AS last_seen
             FROM clips GROUP BY source ORDER BY last_seen DESC NULLS LAST",
        )?;
        let rows: Vec<SourceRow> = stmt
            .query_map([], |r| {
                Ok(SourceRow {
                    source: r.get(0)?,
                    clip_count: r.get(1)?,
                    last_seen: r.get(2)?,
                })
            })?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    })
}

pub fn upsert_device(store: &Store, d: &StoredDevice) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute(
            r#"INSERT OR REPLACE INTO devices
               (id, hostname, nickname, source_key, machine_id, public_key,
                paired_at, last_push_at, online, refreshed_at)
               VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)"#,
            params![
                d.id,
                d.hostname,
                d.nickname,
                d.source_key,
                d.machine_id,
                d.public_key,
                d.paired_at,
                d.last_push_at,
                if d.online { 1i64 } else { 0 },
                d.refreshed_at,
            ],
        )?;
        Ok(())
    })
}

pub fn list_devices(store: &Store) -> Result<Vec<StoredDevice>, StoreError> {
    store.with_conn(|conn| {
        let mut stmt = conn.prepare(
            "SELECT id, hostname, nickname, source_key, machine_id, public_key,
                    paired_at, last_push_at, online, refreshed_at
             FROM devices ORDER BY last_push_at DESC NULLS LAST",
        )?;
        let rows: Vec<StoredDevice> = stmt
            .query_map([], |r| {
                Ok(StoredDevice {
                    id: r.get(0)?,
                    hostname: r.get(1)?,
                    nickname: r.get(2)?,
                    source_key: r.get(3)?,
                    machine_id: r.get(4)?,
                    public_key: r.get(5)?,
                    paired_at: r.get(6)?,
                    last_push_at: r.get(7)?,
                    online: r.get::<_, i64>(8)? != 0,
                    refreshed_at: r.get(9)?,
                })
            })?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    })
}

pub fn set_retention(store: &Store, device_id: &str, days: i64) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute(
            "INSERT INTO retention_prefs(device_id, days) VALUES(?1, ?2)
             ON CONFLICT(device_id) DO UPDATE SET days = excluded.days",
            params![device_id, days],
        )?;
        Ok(())
    })
}

pub fn list_retention(store: &Store) -> Result<Vec<RetentionPref>, StoreError> {
    store.with_conn(|conn| {
        let mut stmt = conn.prepare("SELECT device_id, days FROM retention_prefs")?;
        let rows: Vec<RetentionPref> = stmt
            .query_map([], |r| {
                Ok(RetentionPref {
                    device_id: r.get(0)?,
                    days: r.get(1)?,
                })
            })?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    })
}

pub fn set_alert_pref(store: &Store, source: &str, enabled: bool) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute(
            "INSERT INTO alert_prefs(source, enabled) VALUES(?1, ?2)
             ON CONFLICT(source) DO UPDATE SET enabled = excluded.enabled",
            params![source, if enabled { 1i64 } else { 0 }],
        )?;
        Ok(())
    })
}

pub fn list_alert_prefs(store: &Store) -> Result<Vec<AlertPref>, StoreError> {
    store.with_conn(|conn| {
        let mut stmt = conn.prepare("SELECT source, enabled FROM alert_prefs")?;
        let rows: Vec<AlertPref> = stmt
            .query_map([], |r| {
                Ok(AlertPref {
                    source: r.get(0)?,
                    enabled: r.get::<_, i64>(1)? != 0,
                })
            })?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    })
}

pub fn watermark(store: &Store) -> Result<Option<String>, StoreError> {
    store.with_conn(|conn| {
        conn.query_row(
            "SELECT value FROM meta WHERE key='last_sync_watermark'",
            [],
            |r| r.get::<_, String>(0),
        )
        .map(Some)
        .or_else(|e| match e {
            rusqlite::Error::QueryReturnedNoRows => Ok(None),
            other => Err(other),
        })
    })
}

pub fn set_watermark(store: &Store, ulid: &str) -> Result<(), StoreError> {
    store.with_conn(|conn| {
        conn.execute(
            "INSERT INTO meta(key, value) VALUES('last_sync_watermark', ?1)
             ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            params![ulid],
        )?;
        Ok(())
    })
}

/// Return the total number of clips in the store.
pub fn clip_count(store: &Store) -> Result<i64, StoreError> {
    store.with_conn(|conn| conn.query_row("SELECT COUNT(*) FROM clips", [], |r| r.get::<_, i64>(0)))
}

/// Return the number of clips whose `created_at` (ms) is before `cutoff_ms`.
/// Used by the Settings pane "preview retention change" dialog.
pub fn count_clips_before(store: &Store, cutoff_ms: i64) -> Result<i64, StoreError> {
    store.with_conn(|conn| {
        conn.query_row(
            "SELECT COUNT(*) FROM clips WHERE created_at < ?1",
            rusqlite::params![cutoff_ms],
            |r| r.get::<_, i64>(0),
        )
    })
}

/// Delete all clips from the store. Returns the number of rows deleted.
pub fn clear_all_clips(store: &Store) -> Result<i64, StoreError> {
    store.with_conn(|conn| {
        let n = conn.execute("DELETE FROM clips", [])?;
        Ok(n as i64)
    })
}

pub fn search_clips(store: &Store, query: &str, limit: i64) -> Result<Vec<StoredClip>, StoreError> {
    store.with_conn(|conn| {
        let mut stmt = conn.prepare(
            "SELECT c.id, c.source, c.source_key, c.content_type, c.content, c.media_path,
                    c.byte_size, c.created_at, c.pinned, c.pinned_at
             FROM clips c JOIN clips_fts f ON f.rowid = c.rowid
             WHERE clips_fts MATCH ?1 ORDER BY rank LIMIT ?2",
        )?;
        let rows: Vec<StoredClip> = stmt
            .query_map(params![query, limit], |r| {
                Ok(StoredClip {
                    id: r.get(0)?,
                    source: r.get(1)?,
                    source_key: r.get(2)?,
                    content_type: r.get(3)?,
                    content: r.get(4)?,
                    media_path: r.get(5)?,
                    byte_size: r.get(6)?,
                    created_at: r.get(7)?,
                    pinned: r.get::<_, i64>(8)? != 0,
                    pinned_at: r.get(9)?,
                })
            })?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    })
}
