use std::error::Error;
use turso::{Builder, Connection, Database};

// set up db connection
pub async fn connect() -> Result<Connection, Box<dyn Error>> {
    let db: Database = Builder::new_local("puppycat.db").build().await.unwrap();
    let conn: Connection = db.connect().unwrap();

    Ok(conn)
}

// initialize tables
pub async fn initialize(conn: &Connection) -> Result<(), Box<dyn Error>> {
    create_tables(conn).await?;

    Ok(())
}

// reset db
pub async fn reset(conn: &Connection) -> Result<(), Box<dyn Error>> {
    drop_tables(conn).await?;
    create_tables(conn).await?;

    Ok(())
}

async fn drop_tables(conn: &Connection) -> Result<(), Box<dyn Error>> {
    conn.execute("DROP TABLE IF EXISTS items", ()).await?;
    conn.execute("DROP TABLE IF EXISTS channels", ()).await?;

    Ok(())
}

async fn create_tables(conn: &Connection) -> Result<(), Box<dyn Error>> {
    create_channels(conn).await?;
    create_items(conn).await?;

    Ok(())
}

async fn create_channels(conn: &Connection) -> Result<(), Box<dyn Error>> {
    conn.execute(
        "CREATE TABLE IF NOT EXISTS channels (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            uri TEXT NOT NULL,
            title TEXT NOT NULL,
            link TEXT NOT NULL,
            description TEXT NOT NULL,
            category TEXT,
            last_build_date TEXT,
            skip_hours TEXT,
            skip_days TEXT,
            UNIQUE(uri)
        )",
        (),
    )
    .await?;

    Ok(())
}

async fn create_items(conn: &Connection) -> Result<(), Box<dyn Error>> {
    conn.execute(
        "CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            channel_id INTEGER,
            title TEXT,
            link TEXT,
            content TEXT,
            author TEXT,
            pub_date TEXT,
            FOREIGN KEY(channel_id) REFERENCES channels(id)
        )",
        (),
    )
    .await?;

    Ok(())
}