use anyhow::Result;
use rusqlite::{params, Connection, OptionalExtension};

#[derive(Debug, Clone)]
pub(crate) struct MemoryEvidenceRecord {
    pub(crate) id: String,
    pub(crate) scope: String,
    pub(crate) entry_type: String,
    pub(crate) source_kind: String,
    pub(crate) summary: String,
    pub(crate) summary_digest: String,
    pub(crate) observed_at_epoch_s: u64,
    pub(crate) actor_id: Option<String>,
    pub(crate) session_id: Option<String>,
    pub(crate) source_ref: Option<String>,
    pub(crate) host: Option<String>,
    pub(crate) host_hook: Option<String>,
    pub(crate) host_session_id: Option<String>,
    pub(crate) host_run_id: Option<String>,
    pub(crate) host_task_id: Option<String>,
    pub(crate) provider_name: Option<String>,
    pub(crate) provider_ref: Option<String>,
    pub(crate) extracted: bool,
    pub(crate) extracted_candidate_id: Option<String>,
    pub(crate) extracted_at_epoch_s: Option<u64>,
}

pub(crate) fn insert(conn: &Connection, record: &MemoryEvidenceRecord) -> Result<()> {
    conn.execute(
        "INSERT INTO memory_evidence
            (id, scope, entry_type, source_kind, summary, summary_digest, observed_at_epoch_s,
             actor_id, session_id, source_ref, host, host_hook, host_session_id, host_run_id,
             host_task_id, provider_name, provider_ref, extracted, extracted_candidate_id,
             extracted_at_epoch_s)
         VALUES
            (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18,
             ?19, ?20)",
        params![
            record.id,
            record.scope,
            record.entry_type,
            record.source_kind,
            record.summary,
            record.summary_digest,
            record.observed_at_epoch_s,
            record.actor_id,
            record.session_id,
            record.source_ref,
            record.host,
            record.host_hook,
            record.host_session_id,
            record.host_run_id,
            record.host_task_id,
            record.provider_name,
            record.provider_ref,
            i64::from(record.extracted),
            record.extracted_candidate_id,
            record.extracted_at_epoch_s,
        ],
    )?;
    Ok(())
}

pub(crate) fn get_by_id(conn: &Connection, id: &str) -> Result<Option<MemoryEvidenceRecord>> {
    conn.query_row(
        "SELECT id, scope, entry_type, source_kind, summary, summary_digest, observed_at_epoch_s,
                actor_id, session_id, source_ref, host, host_hook, host_session_id, host_run_id,
                host_task_id, provider_name, provider_ref, extracted, extracted_candidate_id,
                extracted_at_epoch_s
         FROM memory_evidence
         WHERE id = ?1",
        [id],
        row_to_record,
    )
    .optional()
    .map_err(Into::into)
}

pub(crate) fn list_pending(conn: &Connection, limit: usize) -> Result<Vec<MemoryEvidenceRecord>> {
    let mut stmt = conn.prepare(
        "SELECT id, scope, entry_type, source_kind, summary, summary_digest, observed_at_epoch_s,
                actor_id, session_id, source_ref, host, host_hook, host_session_id, host_run_id,
                host_task_id, provider_name, provider_ref, extracted, extracted_candidate_id,
                extracted_at_epoch_s
         FROM memory_evidence
         WHERE extracted = 0
         ORDER BY observed_at_epoch_s ASC, id ASC
         LIMIT ?1",
    )?;
    let rows = stmt.query_map([limit as i64], row_to_record)?;
    let mut records = Vec::new();
    for row in rows {
        records.push(row?);
    }
    Ok(records)
}

pub(crate) fn list_pending_recent(
    conn: &Connection,
    limit: usize,
) -> Result<Vec<MemoryEvidenceRecord>> {
    let mut stmt = conn.prepare(
        "SELECT id, scope, entry_type, source_kind, summary, summary_digest, observed_at_epoch_s,
                actor_id, session_id, source_ref, host, host_hook, host_session_id, host_run_id,
                host_task_id, provider_name, provider_ref, extracted, extracted_candidate_id,
                extracted_at_epoch_s
         FROM memory_evidence
         WHERE extracted = 0
         ORDER BY observed_at_epoch_s DESC, id DESC
         LIMIT ?1",
    )?;
    let rows = stmt.query_map([limit as i64], row_to_record)?;
    let mut records = Vec::new();
    for row in rows {
        records.push(row?);
    }
    Ok(records)
}

pub(crate) fn mark_extracted(
    conn: &Connection,
    id: &str,
    candidate_id: &str,
    extracted_at_epoch_s: u64,
) -> Result<()> {
    conn.execute(
        "UPDATE memory_evidence
         SET extracted = 1,
             extracted_candidate_id = ?2,
             extracted_at_epoch_s = ?3
         WHERE id = ?1",
        params![id, candidate_id, extracted_at_epoch_s],
    )?;
    Ok(())
}

fn row_to_record(row: &rusqlite::Row<'_>) -> rusqlite::Result<MemoryEvidenceRecord> {
    Ok(MemoryEvidenceRecord {
        id: row.get(0)?,
        scope: row.get(1)?,
        entry_type: row.get(2)?,
        source_kind: row.get(3)?,
        summary: row.get(4)?,
        summary_digest: row.get(5)?,
        observed_at_epoch_s: row.get(6)?,
        actor_id: row.get(7)?,
        session_id: row.get(8)?,
        source_ref: row.get(9)?,
        host: row.get(10)?,
        host_hook: row.get(11)?,
        host_session_id: row.get(12)?,
        host_run_id: row.get(13)?,
        host_task_id: row.get(14)?,
        provider_name: row.get(15)?,
        provider_ref: row.get(16)?,
        extracted: row.get::<_, i64>(17)? != 0,
        extracted_candidate_id: row.get(18)?,
        extracted_at_epoch_s: row.get(19)?,
    })
}
