use native_tls::TlsConnector;
use postgres_native_tls::MakeTlsConnector;
use std::env;
use tokio_postgres::Config;

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS authors (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        display_name TEXT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 1
}

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS sessions (
        token UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        author_id UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 2
}

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS invite_tickets (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        code TEXT UNIQUE NOT NULL,
        used BOOLEAN NOT NULL DEFAULT FALSE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 3
}

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS audiences (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        slug TEXT UNIQUE NOT NULL,
        name TEXT NOT NULL,
        description TEXT,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 4
}

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS articles (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        title TEXT NOT NULL,
        slug TEXT UNIQUE NOT NULL,
        audience_id UUID NOT NULL REFERENCES audiences(id),
        author_id UUID NOT NULL REFERENCES authors(id),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 5
}

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS commits (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
        content TEXT NOT NULL,
        message TEXT,
        parent_id UUID REFERENCES commits(id),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 6
}

migs::migs! {
    sql = r#"CREATE TABLE IF NOT EXISTS published (
        article_id UUID PRIMARY KEY REFERENCES articles(id) ON DELETE CASCADE,
        commit_id UUID NOT NULL REFERENCES commits(id),
        published_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );"#,
    scope = "init",
    order = 7
}

migs::migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_sessions_author ON sessions(author_id);"#,
    scope = "init",
    order = 8
}

migs::migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_articles_audience ON articles(audience_id);"#,
    scope = "init",
    order = 9
}

migs::migs! {
    sql = r#"CREATE INDEX IF NOT EXISTS idx_commits_article ON commits(article_id);"#,
    scope = "init",
    order = 10
}

#[derive(Clone)]
pub struct DbConfig {
    config_str: String,
}

impl DbConfig {
    pub fn from_env() -> Result<Self, Box<dyn std::error::Error + Send + Sync>> {
        let host = env::var("PGHOST").map_err(|_| "PGHOST not set".to_string())?;
        let user = env::var("PGUSER").map_err(|_| "PGUSER not set".to_string())?;
        let password = env::var("PGPASSWORD").map_err(|_| "PGPASSWORD not set".to_string())?;
        let dbname = env::var("PGDATABASE").map_err(|_| "PGDATABASE not set".to_string())?;

        let config_str =
            format!("host={host} user={user} password={password} dbname={dbname} sslmode=require");

        Ok(Self { config_str })
    }

    pub async fn connect(
        &self,
    ) -> Result<tokio_postgres::Client, Box<dyn std::error::Error + Send + Sync>> {
        let config: Config = self.config_str.parse()?;
        let tls = MakeTlsConnector::new(TlsConnector::new()?);
        let (client, connection) = config.connect(tls).await?;
        tokio::spawn(async move {
            if let Err(e) = connection.await {
                log::error!("connection error: {e}");
            }
        });
        Ok(client)
    }

    pub async fn run_migrations(&self) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
        let client = self.connect().await?;
        let mut migrations: Vec<_> = migs::collect!();
        migrations.sort_by_key(|m| m.order.unwrap_or(u32::MAX));

        for migration in &migrations {
            log::info!(
                "Running migration: scope={}, order={:?}, source={}",
                migration.scope,
                migration.order,
                migration.source
            );
            client
                .execute(migration.content, &[])
                .await
                .map_err(|e| format!("Migration failed: {e}"))?;
        }

        Ok(())
    }
}