use crate::migration::utils::{
    helpers::col_type_to_method,
    types::{Changes, ParsedColumn, ParsedSchema},
};

pub fn generate_create_file(schema: &ParsedSchema) -> String {
    let cols = build_create_table_cols(schema);
    let fk_stmts = build_fk_create_stmts(schema);
    let idx_stmts = build_index_create_stmts(schema);

    let fk_drops = build_fk_drop_stmts(schema);
    let idx_drops = build_index_drop_stmts(schema);

    format!(
        "use sea_orm_migration::prelude::*;\n\n#[derive(DeriveMigrationName)]\npub struct Migration;\n\n#[async_trait::async_trait]\nimpl MigrationTrait for Migration {{\n    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {{\n        manager\n            .create_table(\n                Table::create()\n                    .table(Alias::new(\"{table}\"))\n                    .if_not_exists()\n{cols}\n                    .to_owned(),\n            )\n            .await?;\n\n{fk_stmts}{idx_stmts}        Ok(())\n    }}\n\n    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {{\n{fk_drops}{idx_drops}        manager\n            .drop_table(Table::drop().table(Alias::new(\"{table}\"))\n                .to_owned())\n            .await?;\n        Ok(())\n    }}\n}}\n",
        table = schema.table_name,
        cols = cols,
        fk_stmts = fk_stmts,
        idx_stmts = idx_stmts,
        fk_drops = fk_drops,
        idx_drops = idx_drops,
    )
}

pub fn generate_alter_file(change: &Changes) -> String {
    let (up_body, down_body) = build_alter_bodies(change);

    format!(
        "use sea_orm_migration::prelude::*;\n\n#[derive(DeriveMigrationName)]\npub struct Migration;\n\n#[async_trait::async_trait]\nimpl MigrationTrait for Migration {{\n    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {{\n{up}\n        Ok(())\n    }}\n\n    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {{\n{down}\n        Ok(())\n    }}\n}}\n",
        up = up_body.trim_end(),
        down = down_body.trim_end()
    )
}

pub fn generate_batch_up_file(changes: &[&Changes], timestamp: &str) -> String {
    let mut body = String::new();
    for change in changes {
        append_up_ops(change, &mut body);
    }
    format!(
        "// Batch up - auto-generated by sea-orm-builder\n// Timestamp: {0}\n// Tables: {1}\nuse sea_orm_migration::prelude::*;\n\n#[derive(DeriveMigrationName)]\npub struct Migration;\n\n#[async_trait::async_trait]\nimpl MigrationTrait for Migration {{\n    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {{\n{2}\n        Ok(())\n    }}\n\n    async fn down(&self, _manager: &SchemaManager) -> Result<(), DbErr> {{\n        Ok(())\n    }}\n}}\n",
        timestamp,
        changes.iter().map(|c| c.table_name.as_str()).collect::<Vec<_>>().join(", "),
        body.trim_end()
    )
}

pub fn generate_batch_down_file(changes: &[&Changes], timestamp: &str) -> String {
    let mut body = String::new();
    for change in changes {
        append_down_ops(change, &mut body);
    }
    format!(
        "// Batch down - auto-generated by sea-orm-builder\n// Timestamp: {0}\n// Tables: {1}\nuse sea_orm_migration::prelude::*;\n\n#[derive(DeriveMigrationName)]\npub struct Migration;\n\n#[async_trait::async_trait]\nimpl MigrationTrait for Migration {{\n    async fn up(&self, _manager: &SchemaManager) -> Result<(), DbErr> {{\n        Ok(())\n    }}\n\n    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {{\n{2}\n        Ok(())\n    }}\n}}\n",
        timestamp,
        changes.iter().map(|c| c.table_name.as_str()).collect::<Vec<_>>().join(", "),
        body.trim_end()
    )
}

fn build_create_table_cols(schema: &ParsedSchema) -> String {
    let mut cols = String::new();

    if let Some(ref pk) = schema.primary_key {
        cols.push_str(&format!("{}\n", render_pk_col(pk)));
    }

    for col in schema.columns.iter().filter(|c| !c.ignored) {
        cols.push_str(&format!(
            "                    .col({})\n",
            render_column_def(col)
        ));
    }

    cols.trim_end().to_string()
}

fn build_fk_create_stmts(schema: &ParsedSchema) -> String {
    let mut out = String::new();
    for fk in &schema.foreign_keys {
        out.push_str(&format!(
            "        manager\n            .create_foreign_key(\n                ForeignKey::create()\n                    .from(Alias::new(\"{table}\"), Alias::new(\"{from}\"))\n                    .to(Alias::new(\"{to_table}\"), Alias::new(\"{to_col}\"))\n                    .on_delete(ForeignKeyAction::{on_delete})\n                    .on_update(ForeignKeyAction::{on_update})\n                    .to_owned(),\n            )\n            .await?;\n\n",
            table = schema.table_name,
            from = fk.from_column,
            to_table = fk.to_table,
            to_col = fk.to_column,
            on_delete = fk.on_delete,
            on_update = fk.on_update
        ));
    }
    out
}

fn build_index_create_stmts(schema: &ParsedSchema) -> String {
    let mut out = String::new();
    for idx in &schema.indexes {
        out.push_str(&render_create_index_stmt(
            &schema.table_name,
            &idx.name,
            &idx.columns,
            idx.unique,
        ));
        out.push('\n');
    }
    out
}

fn build_fk_drop_stmts(schema: &ParsedSchema) -> String {
    let mut out = String::new();
    for fk in &schema.foreign_keys {
        out.push_str(&format!(
            "        manager\n            .drop_foreign_key(\n                ForeignKey::drop()\n                    .table(Alias::new(\"{table}\"))\n                    .name(\"{table}_{from}_{to}_fkey\")\n                    .to_owned(),\n            )\n            .await?;\n\n",
            table = schema.table_name,
            from = fk.from_column,
            to = fk.to_table
        ));
    }
    out
}

fn build_index_drop_stmts(schema: &ParsedSchema) -> String {
    let mut out = String::new();
    for idx in &schema.indexes {
        out.push_str(&format!(
            "        manager\n            .drop_index(Index::drop().name(\"{idx}\").table(Alias::new(\"{table}\")).to_owned())\n            .await?;\n\n",
            idx = idx.name,
            table = schema.table_name
        ));
    }
    out
}

fn build_alter_bodies(change: &Changes) -> (String, String) {
    let mut up = String::new();
    let mut down = String::new();

    // 1) DROP indexes (up) / DROP added indexes (down)
    for idx in &change.dropped_indexes {
        push_drop_index(&mut up, &change.table_name, &idx.name);
    }
    for idx in &change.added_indexes {
        push_drop_index(&mut down, &change.table_name, &idx.name);
    }

    // 2) DROP FK
    for fk in &change.dropped_fks {
        push_drop_fk(&mut up, &change.table_name, &fk.from_column, &fk.to_table);
    }
    for fk in &change.added_fks {
        push_drop_fk(&mut down, &change.table_name, &fk.from_column, &fk.to_table);
    }

    // 3) DROP columns
    for col in &change.dropped_columns {
        push_drop_column(&mut up, &change.table_name, &col.name);
    }
    for col in &change.added_columns {
        push_drop_column(&mut down, &change.table_name, &col.name);
    }

    // 4) MODIFY columns
    for (old, new) in &change.modified_columns {
        // type change => manual
        if old.col_type != new.col_type {
            up.push_str(&format!(
                "        // WARNING: type change on column '{col}': {old} -> {new}\n        // Manual migration required.\n\n",
                col = new.name,
                old = old.col_type,
                new = new.col_type
            ));
            continue;
        }

        // nullable -> not_null => destructive unless you backfill
        if old.nullable && !new.nullable {
            // Génère quand même le modify_column (risqué si NULL existants)
            push_modify_column(
                &mut up,
                &change.table_name,
                &new.name,
                &new.col_type,
                new.nullable,
                new.unique,
            );
            push_modify_column(
                &mut down,
                &change.table_name,
                &old.name,
                &old.col_type,
                old.nullable,
                old.unique,
            );
            continue;
        }

        // safe modify
        push_modify_column(
            &mut up,
            &change.table_name,
            &new.name,
            &new.col_type,
            new.nullable,
            new.unique,
        );
        push_modify_column(
            &mut down,
            &change.table_name,
            &old.name,
            &old.col_type,
            old.nullable,
            old.unique,
        );
    }

    // 5) ADD columns
    for col in &change.added_columns {
        push_add_column(&mut up, &change.table_name, col);
    }

    // 6) Recreate dropped columns in DOWN (now correct because we store ParsedColumn)
    for col in &change.dropped_columns {
        push_add_column(&mut down, &change.table_name, col);
    }

    // 7) ADD FK
    for fk in &change.added_fks {
        push_create_fk(
            &mut up,
            &change.table_name,
            &fk.from_column,
            &fk.to_table,
            &fk.to_column,
            &fk.on_delete,
            &fk.on_update,
        );
    }
    for fk in &change.dropped_fks {
        push_create_fk(
            &mut down,
            &change.table_name,
            &fk.from_column,
            &fk.to_table,
            &fk.to_column,
            &fk.on_delete,
            &fk.on_update,
        );
    }

    // 8) ADD indexes
    for idx in &change.added_indexes {
        push_create_index(
            &mut up,
            &change.table_name,
            &idx.name,
            &idx.columns,
            idx.unique,
        );
    }
    for idx in &change.dropped_indexes {
        push_create_index(
            &mut down,
            &change.table_name,
            &idx.name,
            &idx.columns,
            idx.unique,
        );
    }

    (up, down)
}

fn append_up_ops(change: &Changes, buf: &mut String) {
    for idx in &change.dropped_indexes {
        push_drop_index(buf, &change.table_name, &idx.name);
    }
    for fk in &change.dropped_fks {
        push_drop_fk(buf, &change.table_name, &fk.from_column, &fk.to_table);
    }
    for col in &change.dropped_columns {
        push_drop_column(buf, &change.table_name, &col.name);
    }
    for col in &change.added_columns {
        push_add_column(buf, &change.table_name, col);
    }
    for fk in &change.added_fks {
        push_create_fk(
            buf,
            &change.table_name,
            &fk.from_column,
            &fk.to_table,
            &fk.to_column,
            &fk.on_delete,
            &fk.on_update,
        );
    }
    for idx in &change.added_indexes {
        push_create_index(buf, &change.table_name, &idx.name, &idx.columns, idx.unique);
    }
}

fn append_down_ops(change: &Changes, buf: &mut String) {
    for idx in &change.added_indexes {
        push_drop_index(buf, &change.table_name, &idx.name);
    }
    for fk in &change.added_fks {
        push_drop_fk(buf, &change.table_name, &fk.from_column, &fk.to_table);
    }
    for col in &change.added_columns {
        push_drop_column(buf, &change.table_name, &col.name);
    }
    // recreate dropped columns
    for col in &change.dropped_columns {
        push_add_column(buf, &change.table_name, col);
    }
    for fk in &change.dropped_fks {
        push_create_fk(
            buf,
            &change.table_name,
            &fk.from_column,
            &fk.to_table,
            &fk.to_column,
            &fk.on_delete,
            &fk.on_update,
        );
    }
    for idx in &change.dropped_indexes {
        push_create_index(buf, &change.table_name, &idx.name, &idx.columns, idx.unique);
    }
}

fn render_pk_col(pk: &ParsedColumn) -> String {
    let ty = col_type_to_method(&pk.col_type);

    // auto_increment only makes sense for integer-ish PK.
    let autoinc_ok = matches!(
        pk.col_type.as_str(),
        "Integer" | "BigInteger" | "SmallInteger" | "TinyInteger" | "Unsigned" | "BigUnsigned"
    );

    let mut s = format!(
        "                    .col(ColumnDef::new(Alias::new(\"{name}\")).{ty}.not_null()",
        name = pk.name,
        ty = ty
    );

    if autoinc_ok {
        s.push_str(".auto_increment()");
    }

    s.push_str(".primary_key())");
    s
}

fn render_column_def(col: &ParsedColumn) -> String {
    let ty = col_type_to_method(&col.col_type);
    let null = if col.nullable {
        ".null()"
    } else {
        ".not_null()"
    };
    let uniq = if col.unique { ".unique_key()" } else { "" };
    format!(
        "ColumnDef::new(Alias::new(\"{name}\")).{ty}{null}{uniq}",
        name = col.name,
        ty = ty,
        null = null,
        uniq = uniq
    )
}

fn push_drop_index(buf: &mut String, table: &str, idx_name: &str) {
    buf.push_str(&format!(
        "        manager\n            .drop_index(Index::drop().name(\"{idx}\").table(Alias::new(\"{table}\")).to_owned())\n            .await?;\n\n",
        idx = idx_name,
        table = table
    ));
}

fn push_drop_fk(buf: &mut String, table: &str, from_col: &str, to_table: &str) {
    buf.push_str(&format!(
        "        manager\n            .drop_foreign_key(\n                ForeignKey::drop()\n                    .table(Alias::new(\"{table}\"))\n                    .name(\"{table}_{from}_{to}_fkey\")\n                    .to_owned(),\n            )\n            .await?;\n\n",
        table = table,
        from = from_col,
        to = to_table
    ));
}

fn push_drop_column(buf: &mut String, table: &str, col: &str) {
    buf.push_str(&format!(
        "        manager\n            .alter_table(\n                Table::alter()\n                    .table(Alias::new(\"{table}\"))\n                    .drop_column(Alias::new(\"{col}\"))\n                    .to_owned(),\n            )\n            .await?;\n\n",
        table = table,
        col = col
    ));
}

fn push_modify_column(
    buf: &mut String,
    table: &str,
    col: &str,
    col_type: &str,
    nullable: bool,
    unique: bool,
) {
    let null = if nullable { ".null()" } else { ".not_null()" };
    let uniq = if unique { ".unique_key()" } else { "" };
    buf.push_str(&format!(
        "        manager\n            .alter_table(\n                Table::alter()\n                    .table(Alias::new(\"{table}\"))\n                    .modify_column(ColumnDef::new(Alias::new(\"{col}\")).{ty}{null}{uniq})\n                    .to_owned(),\n            )\n            .await?;\n\n",
        table = table,
        col = col,
        ty = col_type_to_method(col_type),
        null = null,
        uniq = uniq
    ));
}

fn push_add_column(buf: &mut String, table: &str, col: &ParsedColumn) {
    buf.push_str(&format!(
        "        manager\n            .alter_table(\n                Table::alter()\n                    .table(Alias::new(\"{table}\"))\n                    .add_column({coldef})\n                    .to_owned(),\n            )\n            .await?;\n\n",
        table = table,
        coldef = render_column_def(col),
    ));
}

fn push_create_fk(
    buf: &mut String,
    table: &str,
    from_col: &str,
    to_table: &str,
    to_col: &str,
    on_delete: &str,
    on_update: &str,
) {
    buf.push_str(&format!(
        "        manager\n            .create_foreign_key(\n                ForeignKey::create()\n                    .from(Alias::new(\"{table}\"), Alias::new(\"{from}\"))\n                    .to(Alias::new(\"{to_table}\"), Alias::new(\"{to_col}\"))\n                    .on_delete(ForeignKeyAction::{on_delete})\n                    .on_update(ForeignKeyAction::{on_update})\n                    .to_owned(),\n            )\n            .await?;\n\n",
        table = table,
        from = from_col,
        to_table = to_table,
        to_col = to_col,
        on_delete = on_delete,
        on_update = on_update
    ));
}

fn push_create_index(
    buf: &mut String,
    table: &str,
    idx_name: &str,
    columns: &[String],
    unique: bool,
) {
    buf.push_str(&render_create_index_stmt(table, idx_name, columns, unique));
    buf.push('\n');
}

fn render_create_index_stmt(
    table: &str,
    idx_name: &str,
    columns: &[String],
    unique: bool,
) -> String {
    let mut cols_chain = String::new();
    for c in columns {
        cols_chain.push_str(&format!(
            "                    .col(Alias::new(\"{c}\"))\n",
            c = c
        ));
    }

    let uniq_line = if unique {
        "                    .unique_key()\n"
    } else {
        ""
    };

    format!(
        "        manager\n            .create_index(\n                Index::create()\n                    .name(\"{idx}\")\n                    .table(Alias::new(\"{table}\"))\n{cols}{uniq}                    .to_owned(),\n            )\n            .await?;\n",
        idx = idx_name,
        table = table,
        cols = cols_chain,
        uniq = uniq_line
    )
}
