Database

pub struct Db {
    pub(crate) connection: Connection,
}

impl Db {
    pub fn new(path: PathBuf) -> Result<Self> {
        debug!("Loading database at location {:?}", path.to_str());
        let conn = Connection::open(path)?;
        Ok(Self { connection: conn })
    }
}

pub fn unlock_with_password(&mut self, password: String) -> Result<()> {
    self.connection.pragma_update(None, "key", password)?;

    // Apply migrations
    info!("Running Migrations");
    MIGRATIONS.to_latest(&mut self.connection)?;

    Ok()
}

pub fn format_unlock_error(e: &anyhow::Error) -> String {
    match e.downcast_ref::<rusqlite_migration::Error>() {
        Some(rusqlite_migration::Error::RusqliteError { err, .. }) => {
            match err.sqlite_error_code() {
                Some(rusqlite::ErrorCode::NotADatabase) => "Wrong password".to_string(),
                _ => format!("Database error: {}", e),
            }
        }
        _ => format!("Database error: {}", e),
    }
}

static MIGRATIONS_DIR: Dir = include_dir!("$CARGO_MANIFEST_DIR/migrations");

static MIGRATIONS: LazyLock<Migrations<'static>> =
    LazyLock::new(|| Migrations::from_directory(&MIGRATIONS_DIR).unwrap());

CREATE TABLE IF NOT EXISTS events (
    id TEXT PRIMARY KEY,
    pubkey TEXT NOT NULL GENERATED ALWAYS AS (jsonb_extract (raw, '$.pubkey')),
    created_at INTEGER NOT NULL GENERATED ALWAYS AS (jsonb_extract (raw, '$.created_at')) VIRTUAL,
    kind INTEGER NOT NULL GENERATED ALWAYS AS (jsonb_extract (raw, '$.kind')) VIRTUAL,
    tags BLOB NOT NULL GENERATED ALWAYS AS (jsonb_extract (raw, '$.tags')) VIRTUAL,
    content TEXT NOT NULL GENERATED ALWAYS AS (jsonb_extract (raw, '$.content')) VIRTUAL,
    sig TEXT GENERATED ALWAYS AS (jsonb_extract (raw, '$.sig')) VIRTUAL,
    raw BLOB NOT NULL
);

CREATE INDEX idx_events_pubkey ON events (pubkey);
CREATE INDEX idx_events_kind ON events (created_at);

// Stored as JSON and cached in HashMap during runtime
pub struct ProfileMetadata {
    pub name: Option<String>,
    pub display_name: Option<String>,
    pub about: Option<String>,
    pub picture: Option<String>,
    pub nip05: Option<String>,
    pub banner: Option<String>,
    pub website: Option<String>,
}

CREATE TABLE IF NOT EXISTS pubkeys (
    pubkey TEXT PRIMARY KEY NOT NULL
);

pub fn store_event(
    &self,
    event: &Event,
    unwrapped: Option<&UnwrappedGift>,
    gift_wrap_recipient: Option<&str>,
) -> Result<()> {
    if let Some(unwrapped) = unwrapped {
        let mut rumor = unwrapped.rumor.clone();
        rumor.ensure_id();

        if unwrapped.sender != rumor.pubkey {
            anyhow::bail!("Seal signer does not match rumor pubkey");
        }

        let id = rumor.id.expect("Invalid Gift Wrapped Event").to_hex();
        let author_pubkey = rumor.pubkey.to_string();
        if self.is_deleted(&id, Some(author_pubkey.as_str()))? {
            return Ok(());
        }
        let raw = json!(rumor).to_string();

        self.connection.execute(
            "INSERT OR IGNORE INTO events (id, raw) VALUES (?1, ?2)",
            (id.clone(), raw),
        )?;

        self.save_gift_wrap_map(&event.id.to_string(), &id, gift_wrap_recipient, event.created_at.as_u64() as i64)?;
        return Ok(());
    }

    let id = event.id.to_string();
    let author_pubkey = event.pubkey.to_string();
    if self.is_deleted(&id, Some(author_pubkey.as_str()))? {
        return Ok(());
    }
    let raw = json!(event).to_string();

    self.connection.execute(
        "INSERT OR IGNORE INTO events (id, raw) VALUES (?1, ?2)",
        (id, raw),
    )?;

    Ok()
}

// Example: Query events by kind and time range
SELECT id, pubkey, created_at, content 
FROM events 
WHERE kind = 2024 
  AND created_at > ? 
ORDER BY created_at DESC;

WITH RECURSIVE thread AS (
  -- Base case: the root event
  SELECT id, pubkey, created_at, content, tags, raw
  FROM events
  WHERE id = ?
  
  UNION ALL
  
  -- Recursive case: events that reference thread members
  SELECT e.id, e.pubkey, e.created_at, e.content, e.tags, e.raw
  FROM events e
  INNER JOIN thread t
  WHERE jsonb_extract(e.tags, '$[?(@[0]=="e")][1]') = t.id
)
SELECT * FROM thread ORDER BY created_at ASC;