import { test, describe } from "node:test";
import assert from "node:assert/strict";
import {
  checkCreateIndexWithoutConcurrently,
  checkAddColumnNotNullWithoutDefault,
  checkAlterColumnType,
  checkAddForeignKeyWithoutNotValid,
  checkAddUniqueConstraint,
  checkSetNotNull,
  checkDropTable,
  checkTruncateTable,
  checkRenameColumn,
  checkRenameTable,
  checkDropColumn,
  checkAddCheckConstraintWithoutNotValid,
  checkMultipleOperations,
  checkBackfillWithoutWhere,
  checkIndexOnManyColumns,
} from "../checks.js";
import { checkMigration } from "../checker.js";

describe("checkCreateIndexWithoutConcurrently", () => {
  test("flags CREATE INDEX without CONCURRENTLY", () => {
    const sql = "CREATE INDEX idx_users_email ON users (email);";
    const issues = checkCreateIndexWithoutConcurrently(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
    assert.equal(issues[0].check, "create_index_without_concurrently");
  });

  test("does not flag CREATE INDEX CONCURRENTLY", () => {
    const sql = "CREATE INDEX CONCURRENTLY idx_users_email ON users (email);";
    const issues = checkCreateIndexWithoutConcurrently(sql);
    assert.equal(issues.length, 0);
  });

  test("does not flag CREATE UNIQUE INDEX CONCURRENTLY", () => {
    const sql = "CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users (email);";
    const issues = checkCreateIndexWithoutConcurrently(sql);
    assert.equal(issues.length, 0);
  });

  test("flags CREATE UNIQUE INDEX without CONCURRENTLY", () => {
    const sql = "CREATE UNIQUE INDEX idx_users_email ON users (email);";
    const issues = checkCreateIndexWithoutConcurrently(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });
});

describe("checkAddColumnNotNullWithoutDefault", () => {
  test("flags ADD COLUMN NOT NULL without DEFAULT", () => {
    const sql = "ALTER TABLE users ADD COLUMN age INTEGER NOT NULL;";
    const issues = checkAddColumnNotNullWithoutDefault(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });

  test("does not flag ADD COLUMN NOT NULL with DEFAULT", () => {
    const sql = "ALTER TABLE users ADD COLUMN age INTEGER NOT NULL DEFAULT 0;";
    const issues = checkAddColumnNotNullWithoutDefault(sql);
    assert.equal(issues.length, 0);
  });

  test("does not flag ADD COLUMN nullable (no NOT NULL)", () => {
    const sql = "ALTER TABLE users ADD COLUMN bio TEXT;";
    const issues = checkAddColumnNotNullWithoutDefault(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkAlterColumnType", () => {
  test("flags ALTER COLUMN TYPE", () => {
    const sql = "ALTER TABLE users ALTER COLUMN age TYPE BIGINT;";
    const issues = checkAlterColumnType(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });

  test("does not flag ADD COLUMN", () => {
    const sql = "ALTER TABLE users ADD COLUMN score FLOAT;";
    const issues = checkAlterColumnType(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkAddForeignKeyWithoutNotValid", () => {
  test("flags ADD FOREIGN KEY without NOT VALID", () => {
    const sql = "ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);";
    const issues = checkAddForeignKeyWithoutNotValid(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });

  test("does not flag ADD FOREIGN KEY with NOT VALID", () => {
    const sql = "ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;";
    const issues = checkAddForeignKeyWithoutNotValid(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkAddUniqueConstraint", () => {
  test("flags ADD CONSTRAINT UNIQUE", () => {
    const sql = "ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);";
    const issues = checkAddUniqueConstraint(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });

  test("does not flag ADD CONSTRAINT UNIQUE USING INDEX", () => {
    const sql = "ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE USING INDEX idx_users_email;";
    const issues = checkAddUniqueConstraint(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkSetNotNull", () => {
  test("flags SET NOT NULL", () => {
    const sql = "ALTER TABLE users ALTER COLUMN email SET NOT NULL;";
    const issues = checkSetNotNull(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });

  test("does not flag DROP NOT NULL", () => {
    const sql = "ALTER TABLE users ALTER COLUMN email DROP NOT NULL;";
    const issues = checkSetNotNull(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkDropTable", () => {
  test("flags DROP TABLE", () => {
    const sql = "DROP TABLE old_sessions;";
    const issues = checkDropTable(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });
});

describe("checkTruncateTable", () => {
  test("flags TRUNCATE", () => {
    const sql = "TRUNCATE TABLE sessions;";
    const issues = checkTruncateTable(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "danger");
  });
});

describe("checkRenameColumn", () => {
  test("flags RENAME COLUMN", () => {
    const sql = "ALTER TABLE users RENAME COLUMN username TO display_name;";
    const issues = checkRenameColumn(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "warning");
  });
});

describe("checkRenameTable", () => {
  test("flags RENAME TO", () => {
    const sql = "ALTER TABLE users RENAME TO app_users;";
    const issues = checkRenameTable(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "warning");
  });
});

describe("checkDropColumn", () => {
  test("flags DROP COLUMN", () => {
    const sql = "ALTER TABLE users DROP COLUMN legacy_field;";
    const issues = checkDropColumn(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "warning");
  });
});

describe("checkAddCheckConstraintWithoutNotValid", () => {
  test("flags ADD CHECK CONSTRAINT without NOT VALID", () => {
    const sql = "ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0);";
    const issues = checkAddCheckConstraintWithoutNotValid(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "warning");
  });

  test("does not flag ADD CHECK CONSTRAINT with NOT VALID", () => {
    const sql = "ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount > 0) NOT VALID;";
    const issues = checkAddCheckConstraintWithoutNotValid(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkMultipleOperations", () => {
  test("flags more than 3 ALTER TABLE statements", () => {
    const sql = `
      ALTER TABLE users ADD COLUMN a TEXT;
      ALTER TABLE users ADD COLUMN b TEXT;
      ALTER TABLE users ADD COLUMN c TEXT;
      ALTER TABLE users ADD COLUMN d TEXT;
    `;
    const issues = checkMultipleOperations(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "info");
  });

  test("does not flag 3 or fewer ALTER TABLE statements", () => {
    const sql = `
      ALTER TABLE users ADD COLUMN a TEXT;
      ALTER TABLE users ADD COLUMN b TEXT;
    `;
    const issues = checkMultipleOperations(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkBackfillWithoutWhere", () => {
  test("flags UPDATE without WHERE", () => {
    const sql = "UPDATE users SET migrated = true;";
    const issues = checkBackfillWithoutWhere(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "info");
  });

  test("does not flag UPDATE with WHERE", () => {
    const sql = "UPDATE users SET migrated = true WHERE id > 1000;";
    const issues = checkBackfillWithoutWhere(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkIndexOnManyColumns", () => {
  test("flags index on 4 columns", () => {
    const sql = "CREATE INDEX idx ON events (a, b, c, d);";
    const issues = checkIndexOnManyColumns(sql);
    assert.equal(issues.length, 1);
    assert.equal(issues[0].severity, "info");
  });

  test("does not flag index on 3 columns", () => {
    const sql = "CREATE INDEX idx ON events (a, b, c);";
    const issues = checkIndexOnManyColumns(sql);
    assert.equal(issues.length, 0);
  });
});

describe("checkMigration (integration)", () => {
  test("returns safe=true for safe migration", () => {
    const sql = `
      CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
      ALTER TABLE users ADD COLUMN bio TEXT;
    `;
    const result = checkMigration(sql);
    assert.equal(result.safe, true);
    assert.equal(result.summary.danger, 0);
    assert.equal(result.summary.warning, 0);
  });

  test("returns safe=false for unsafe migration", () => {
    const sql = "CREATE INDEX idx_users_email ON users (email);";
    const result = checkMigration(sql);
    assert.equal(result.safe, false);
    assert.equal(result.summary.danger, 1);
  });

  test("detects multiple issues in one file", () => {
    const sql = `
      CREATE INDEX idx ON users (email);
      ALTER TABLE users ADD COLUMN age INT NOT NULL;
      ALTER TABLE orders RENAME COLUMN total TO amount;
    `;
    const result = checkMigration(sql);
    assert.ok(result.summary.danger >= 2);
    assert.ok(result.summary.warning >= 1);
  });
});