use std::path::Path;
use std::time::{SystemTime, UNIX_EPOCH};

use rusqlite::{params, Connection};

use crate::error::Result;
use crate::models::{
    CertificateInfo, NewProbeResult, ProbeResult, ProbeStatus, TimingReport, TlsReport, TlsStatus,
};

#[derive(Debug)]
pub struct SqliteStore {
    conn: Connection,
}

impl SqliteStore {
    pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
        let conn = Connection::open(path)?;
        let store = Self { conn };
        store.init()?;

        Ok(store)
    }

    pub fn in_memory() -> Result<Self> {
        let conn = Connection::open_in_memory()?;
        let store = Self { conn };
        store.init()?;

        Ok(store)
    }

    pub fn init(&self) -> Result<()> {
        self.conn.execute(
            "CREATE TABLE IF NOT EXISTS runs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                target TEXT NOT NULL,
                request_path TEXT NOT NULL DEFAULT '/',
                dns_ms INTEGER NOT NULL,
                tcp_ms INTEGER NOT NULL,
                tls_ms INTEGER NOT NULL,
                ttfb_ms INTEGER NOT NULL,
                download_ms INTEGER NOT NULL DEFAULT 0,
                total_ms INTEGER NOT NULL,
                status TEXT NOT NULL,
                response_status_code INTEGER,
                response_status_text TEXT,
                response_bytes INTEGER,
                error_message TEXT,
                tls_enabled INTEGER,
                tls_status TEXT,
                tls_version TEXT,
                tls_cipher_suite TEXT,
                tls_certificate_subject TEXT,
                tls_certificate_issuer TEXT,
                tls_certificate_not_before TEXT,
                tls_certificate_not_after TEXT,
                tls_error_message TEXT,
                created_at_ms INTEGER NOT NULL
            )",
            [],
        )?;

        self.ensure_column("runs", "request_path", "TEXT NOT NULL DEFAULT '/'")?;
        self.ensure_column("runs", "response_status_code", "INTEGER")?;
        self.ensure_column("runs", "response_status_text", "TEXT")?;
        self.ensure_column("runs", "response_bytes", "INTEGER")?;
        self.ensure_column("runs", "download_ms", "INTEGER NOT NULL DEFAULT 0")?;

        self.conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_runs_target_created_at
             ON runs(target, created_at_ms DESC)",
            [],
        )?;

        Ok(())
    }

    pub fn save_result(&self, result: &NewProbeResult) -> Result<ProbeResult> {
        let created_at_ms = current_time_ms()?;

        self.conn.execute(
            "INSERT INTO runs (
                target,
                request_path,
                dns_ms,
                tcp_ms,
                tls_ms,
                ttfb_ms,
                download_ms,
                total_ms,
                status,
                response_status_code,
                response_status_text,
                response_bytes,
                error_message,
                tls_enabled,
                tls_status,
                tls_version,
                tls_cipher_suite,
                tls_certificate_subject,
                tls_certificate_issuer,
                tls_certificate_not_before,
                tls_certificate_not_after,
                tls_error_message,
                created_at_ms
            ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23)",
            params![
                &result.target,
                &result.request_path,
                result.report.dns_ms as i64,
                result.report.tcp_ms as i64,
                result.report.tls_ms as i64,
                result.report.ttfb_ms as i64,
                result.report.download_ms as i64,
                result.report.total_ms as i64,
                probe_status_as_str(result.status),
                result.response_status_code.map(i64::from),
                &result.response_status_text,
                result.response_bytes.map(|value| value as i64),
                &result.error_message,
                result.tls.as_ref().map(|tls| tls.enabled as i64),
                result.tls.as_ref().map(|tls| tls_status_as_str(tls.status)),
                result.tls.as_ref().and_then(|tls| tls.version.as_deref()),
                result
                    .tls
                    .as_ref()
                    .and_then(|tls| tls.cipher_suite.as_deref()),
                result
                    .tls
                    .as_ref()
                    .and_then(|tls| tls.certificate.as_ref().map(|cert| cert.subject.as_str())),
                result
                    .tls
                    .as_ref()
                    .and_then(|tls| tls.certificate.as_ref().map(|cert| cert.issuer.as_str())),
                result.tls.as_ref().and_then(|tls| {
                    tls.certificate
                        .as_ref()
                        .and_then(|cert| cert.not_before.as_deref())
                }),
                result.tls.as_ref().and_then(|tls| {
                    tls.certificate
                        .as_ref()
                        .and_then(|cert| cert.not_after.as_deref())
                }),
                result
                    .tls
                    .as_ref()
                    .and_then(|tls| tls.error_message.as_deref()),
                created_at_ms,
            ],
        )?;

        Ok(ProbeResult {
            id: self.conn.last_insert_rowid(),
            target: result.target.clone(),
            request_path: result.request_path.clone(),
            report: result.report.clone(),
            status: result.status,
            response_status_code: result.response_status_code,
            response_status_text: result.response_status_text.clone(),
            response_bytes: result.response_bytes,
            error_message: result.error_message.clone(),
            tls: result.tls.clone(),
            created_at_ms,
        })
    }

    pub fn save_report(&self, target: &str, report: &TimingReport) -> Result<ProbeResult> {
        let result = NewProbeResult {
            target: target.to_string(),
            request_path: request_path_from_target(target),
            report: report.clone(),
            status: ProbeStatus::Succeeded,
            response_status_code: None,
            response_status_text: None,
            response_bytes: None,
            error_message: None,
            tls: None,
        };

        self.save_result(&result)
    }

    pub fn recent_results(&self, limit: usize) -> Result<Vec<ProbeResult>> {
        let mut statement = self.conn.prepare(
            "SELECT id, target, request_path, dns_ms, tcp_ms, tls_ms, ttfb_ms, download_ms, total_ms, status,
                    response_status_code, response_status_text, response_bytes, error_message,
                    tls_enabled, tls_status, tls_version, tls_cipher_suite,
                    tls_certificate_subject, tls_certificate_issuer,
                    tls_certificate_not_before, tls_certificate_not_after,
                    tls_error_message, created_at_ms
             FROM runs
             ORDER BY created_at_ms DESC, id DESC
             LIMIT ?1",
        )?;

        let rows = statement.query_map([limit as i64], read_probe_result)?;

        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Into::into)
    }

    pub fn recent_results_for_target(&self, target: &str, limit: usize) -> Result<Vec<ProbeResult>> {
        let mut statement = self.conn.prepare(
            "SELECT id, target, request_path, dns_ms, tcp_ms, tls_ms, ttfb_ms, download_ms, total_ms, status,
                    response_status_code, response_status_text, response_bytes, error_message,
                    tls_enabled, tls_status, tls_version, tls_cipher_suite,
                    tls_certificate_subject, tls_certificate_issuer,
                    tls_certificate_not_before, tls_certificate_not_after,
                    tls_error_message, created_at_ms
             FROM runs
             WHERE target = ?1
             ORDER BY created_at_ms DESC, id DESC
             LIMIT ?2",
        )?;

        let rows = statement.query_map(params![target, limit as i64], read_probe_result)?;

        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Into::into)
    }

    pub fn recent_runs(&self, limit: usize) -> Result<Vec<ProbeResult>> {
        self.recent_results(limit)
    }

    pub fn delete_results_for_target(&self, target: &str) -> Result<usize> {
        let deleted = self
            .conn
            .execute("DELETE FROM runs WHERE target = ?1", params![target])?;

        Ok(deleted)
    }

    pub fn count_results_for_target(&self, target: &str) -> Result<usize> {
        let mut statement = self
            .conn
            .prepare("SELECT COUNT(*) FROM runs WHERE target = ?1")?;
        let count = statement.query_row(params![target], |row| row.get::<_, i64>(0))?;

        Ok(count.max(0) as usize)
    }

    pub fn latest_result_for_target(&self, target: &str) -> Result<Option<ProbeResult>> {
        let mut statement = self.conn.prepare(
            "SELECT id, target, request_path, dns_ms, tcp_ms, tls_ms, ttfb_ms, download_ms, total_ms, status,
                    response_status_code, response_status_text, response_bytes, error_message,
                    tls_enabled, tls_status, tls_version, tls_cipher_suite,
                    tls_certificate_subject, tls_certificate_issuer,
                    tls_certificate_not_before, tls_certificate_not_after,
                    tls_error_message, created_at_ms
             FROM runs
             WHERE target = ?1
             ORDER BY created_at_ms DESC, id DESC
             LIMIT 1",
        )?;

        let mut rows = statement.query([target])?;

        match rows.next()? {
            Some(row) => Ok(Some(read_probe_result(row)?)),
            None => Ok(None),
        }
    }

    fn ensure_column(&self, table: &str, column: &str, definition: &str) -> Result<()> {
        let pragma = format!("PRAGMA table_info({table})");
        let mut statement = self.conn.prepare(&pragma)?;
        let rows = statement.query_map([], |row| row.get::<_, String>(1))?;
        let existing_columns = rows.collect::<std::result::Result<Vec<_>, _>>()?;

        if !existing_columns.iter().any(|existing| existing == column) {
            let alter = format!("ALTER TABLE {table} ADD COLUMN {column} {definition}");
            self.conn.execute(&alter, [])?;
        }

        Ok(())
    }

    pub fn latest_run_for_target(&self, target: &str) -> Result<Option<ProbeResult>> {
        self.latest_result_for_target(target)
    }
}

fn current_time_ms() -> Result<i64> {
    let now = SystemTime::now().duration_since(UNIX_EPOCH)?;

    Ok(now.as_millis() as i64)
}

fn read_probe_result(row: &rusqlite::Row<'_>) -> rusqlite::Result<ProbeResult> {
    Ok(ProbeResult {
        id: row.get(0)?,
        target: row.get(1)?,
        request_path: row.get(2)?,
        report: TimingReport {
            dns_ms: row.get::<_, i64>(3)? as u64,
            tcp_ms: row.get::<_, i64>(4)? as u64,
            tls_ms: row.get::<_, i64>(5)? as u64,
            ttfb_ms: row.get::<_, i64>(6)? as u64,
            download_ms: row.get::<_, i64>(7)? as u64,
            total_ms: row.get::<_, i64>(8)? as u64,
        },
        status: probe_status_from_str(&row.get::<_, String>(9)?)?,
        response_status_code: row.get::<_, Option<i64>>(10)?.map(|value| value as u16),
        response_status_text: row.get(11)?,
        response_bytes: row.get::<_, Option<i64>>(12)?.map(|value| value as u64),
        error_message: row.get(13)?,
        tls: read_tls_report(row)?,
        created_at_ms: row.get(23)?,
    })
}

fn probe_status_as_str(status: ProbeStatus) -> &'static str {
    match status {
        ProbeStatus::Succeeded => "succeeded",
        ProbeStatus::Failed => "failed",
    }
}

fn probe_status_from_str(value: &str) -> rusqlite::Result<ProbeStatus> {
    match value {
        "succeeded" => Ok(ProbeStatus::Succeeded),
        "failed" => Ok(ProbeStatus::Failed),
        _ => Err(rusqlite::Error::InvalidQuery),
    }
}

fn tls_status_as_str(status: TlsStatus) -> &'static str {
    match status {
        TlsStatus::NotUsed => "not_used",
        TlsStatus::HandshakeSucceeded => "handshake_succeeded",
        TlsStatus::HandshakeFailed => "handshake_failed",
    }
}

fn tls_status_from_str(value: &str) -> rusqlite::Result<TlsStatus> {
    match value {
        "not_used" => Ok(TlsStatus::NotUsed),
        "handshake_succeeded" => Ok(TlsStatus::HandshakeSucceeded),
        "handshake_failed" => Ok(TlsStatus::HandshakeFailed),
        _ => Err(rusqlite::Error::InvalidQuery),
    }
}

fn read_tls_report(row: &rusqlite::Row<'_>) -> rusqlite::Result<Option<TlsReport>> {
    let enabled = row.get::<_, Option<i64>>(14)?;

    let Some(enabled) = enabled else {
        return Ok(None);
    };

    let subject = row.get::<_, Option<String>>(18)?;
    let issuer = row.get::<_, Option<String>>(19)?;
    let not_before = row.get::<_, Option<String>>(20)?;
    let not_after = row.get::<_, Option<String>>(21)?;

    let certificate = match (subject, issuer, not_before, not_after) {
        (Some(subject), Some(issuer), not_before, not_after) => Some(CertificateInfo {
            subject,
            issuer,
            not_before,
            not_after,
        }),
        _ => None,
    };

    Ok(Some(TlsReport {
        enabled: enabled != 0,
        status: tls_status_from_str(&row.get::<_, String>(15)?)?,
        version: row.get(16)?,
        cipher_suite: row.get(17)?,
        certificate,
        error_message: row.get(22)?,
    }))
}

fn request_path_from_target(target: &str) -> String {
    url::Url::parse(target)
        .ok()
        .map(|url| {
            let mut path = url.path().to_string();
            if path.is_empty() {
                path.push('/');
            }
            if let Some(query) = url.query() {
                path.push('?');
                path.push_str(query);
            }
            path
        })
        .unwrap_or_else(|| "/".to_string())
}

#[cfg(test)]
mod tests {
    use crate::engine::timing::build_report;
    use crate::models::{NewProbeResult, ProbeStatus, TlsReport, TlsStatus};

    use super::SqliteStore;

    #[test]
    fn save_report_and_read_recent_results() {
        let store = SqliteStore::in_memory().expect("store should initialize");
        let report = build_report(10, 20, 30, 40, 5);

        let saved_result = store
            .save_report("http://localhost:3000", &report)
            .expect("report should save");

        let runs = store
            .recent_results(10)
            .expect("recent results should load");

        assert_eq!(runs.len(), 1);
        assert_eq!(runs[0].id, saved_result.id);
        assert_eq!(runs[0].target, "http://localhost:3000");
        assert_eq!(runs[0].request_path, "/");
        assert_eq!(runs[0].report, report);
        assert_eq!(runs[0].status, ProbeStatus::Succeeded);
        assert_eq!(runs[0].response_status_code, None);
        assert_eq!(runs[0].error_message, None);
        assert_eq!(runs[0].tls, None);
    }

    #[test]
    fn latest_result_for_target_returns_most_recent_match() {
        let store = SqliteStore::in_memory().expect("store should initialize");

        let first_report = build_report(1, 2, 3, 4, 5);
        let second_report = build_report(4, 5, 6, 7, 8);

        store
            .save_report("http://localhost:3000", &first_report)
            .expect("first report should save");
        let latest_result = store
            .save_report("http://localhost:3000", &second_report)
            .expect("second report should save");
        store
            .save_report("http://localhost:4000", &first_report)
            .expect("other target report should save");

        let run = store
            .latest_result_for_target("http://localhost:3000")
            .expect("latest result should load")
            .expect("target should have a run");

        assert_eq!(run.id, latest_result.id);
        assert_eq!(run.target, "http://localhost:3000");
        assert_eq!(run.request_path, "/");
        assert_eq!(run.report, second_report);
        assert_eq!(run.status, ProbeStatus::Succeeded);
    }

    #[test]
    fn recent_results_for_target_filters_by_target() {
        let store = SqliteStore::in_memory().expect("store should initialize");
        let report = build_report(1, 2, 3, 4, 5);

        store
            .save_report("http://localhost:3000", &report)
            .expect("first target report should save");
        store
            .save_report("http://localhost:4000", &report)
            .expect("second target report should save");
        store
            .save_report("http://localhost:3000", &report)
            .expect("third target report should save");

        let runs = store
            .recent_results_for_target("http://localhost:3000", 10)
            .expect("filtered results should load");

        assert_eq!(runs.len(), 2);
        assert!(runs.iter().all(|run| run.target == "http://localhost:3000"));
    }

    #[test]
    fn delete_results_for_target_removes_matching_rows() {
        let store = SqliteStore::in_memory().expect("store should initialize");
        let report = build_report(1, 2, 3, 4, 5);

        store
            .save_report("http://localhost:3000", &report)
            .expect("first target report should save");
        store
            .save_report("http://localhost:4000", &report)
            .expect("second target report should save");
        store
            .save_report("http://localhost:3000", &report)
            .expect("third target report should save");

        let deleted = store
            .delete_results_for_target("http://localhost:3000")
            .expect("deletion should succeed");

        assert_eq!(deleted, 2);
        assert_eq!(
            store
                .recent_results_for_target("http://localhost:3000", 10)
                .expect("filtered results should load")
                .len(),
            0
        );
        assert_eq!(
            store
                .recent_results_for_target("http://localhost:4000", 10)
                .expect("remaining results should load")
                .len(),
            1
        );
        assert_eq!(
            store
                .count_results_for_target("http://localhost:3000")
                .expect("count should load"),
            0
        );
        assert_eq!(
            store
                .count_results_for_target("http://localhost:4000")
                .expect("remaining count should load"),
            1
        );
    }

    #[test]
    fn save_result_persists_failure_details() {
        let store = SqliteStore::in_memory().expect("store should initialize");
        let result = NewProbeResult {
            target: "http://localhost:3000".to_string(),
            request_path: "/".to_string(),
            report: build_report(0, 0, 0, 0, 0),
            status: ProbeStatus::Failed,
            response_status_code: None,
            response_status_text: None,
            response_bytes: None,
            error_message: Some("request timed out".to_string()),
            tls: Some(TlsReport {
                enabled: true,
                status: TlsStatus::HandshakeFailed,
                version: None,
                cipher_suite: None,
                certificate: None,
                error_message: Some("request timed out".to_string()),
            }),
        };

        let saved = store
            .save_result(&result)
            .expect("result should save");

        let loaded = store
            .latest_result_for_target("http://localhost:3000")
            .expect("latest result should load")
            .expect("target should have a result");

        assert_eq!(loaded.id, saved.id);
        assert_eq!(loaded.status, ProbeStatus::Failed);
        assert_eq!(loaded.request_path, "/");
        assert_eq!(loaded.error_message.as_deref(), Some("request timed out"));
        assert_eq!(
            loaded.tls.expect("tls should be stored").status,
            TlsStatus::HandshakeFailed
        );
    }
}
