import type { Issue, CheckDefinition } from "./types.js";

function getLines(sql: string): string[] {
  return sql.split("\n");
}

function findLineNumber(lines: string[], index: number): number {
  let count = 0;
  for (let i = 0; i < lines.length; i++) {
    count += lines[i].length + 1;
    if (count > index) return i + 1;
  }
  return lines.length;
}

function statementsWithLines(sql: string): Array<{ stmt: string; line: number }> {
  const lines = getLines(sql);
  const results: Array<{ stmt: string; line: number }> = [];
  // Split on semicolons, track line numbers
  let current = "";
  let startLine = 1;
  let currentLine = 1;
  for (const line of lines) {
    const trimmed = line.trim();
    // Skip comments
    if (trimmed.startsWith("--")) {
      currentLine++;
      continue;
    }
    current += (current ? " " : "") + trimmed;
    if (trimmed.endsWith(";")) {
      const stmt = current.trim();
      if (stmt.length > 1) {
        results.push({ stmt, line: startLine });
      }
      current = "";
      startLine = currentLine + 1;
    }
    currentLine++;
  }
  // Handle statement without trailing semicolon
  if (current.trim().length > 1) {
    results.push({ stmt: current.trim(), line: startLine });
  }
  return results;
}

// ─── DANGER checks ────────────────────────────────────────────────────────────

export function checkCreateIndexWithoutConcurrently(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    const hasIndex = upper.includes("CREATE INDEX") || upper.includes("CREATE UNIQUE INDEX");
    const hasConcurrently = upper.includes("CONCURRENTLY");
    if (hasIndex && !hasConcurrently) {
      issues.push({
        severity: "danger",
        check: "create_index_without_concurrently",
        line,
        statement: stmt,
        message: "CREATE INDEX without CONCURRENTLY locks the table for reads and writes during index creation.",
        suggestion: "Use CREATE INDEX CONCURRENTLY to build the index without locking. Note: cannot run inside a transaction block.",
      });
    }
  }
  return issues;
}

export function checkAddColumnNotNullWithoutDefault(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    if (
      upper.includes("ADD COLUMN") &&
      upper.includes("NOT NULL") &&
      !upper.includes("DEFAULT")
    ) {
      issues.push({
        severity: "danger",
        check: "add_column_not_null_without_default",
        line,
        statement: stmt,
        message: "ADD COLUMN NOT NULL without a DEFAULT value requires a table rewrite and locks the table.",
        suggestion: "Add the column as nullable first (no NOT NULL), backfill the data, then add the NOT NULL constraint with ALTER COLUMN ... SET NOT NULL.",
      });
    }
  }
  return issues;
}

export function checkAlterColumnType(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    if (
      upper.includes("ALTER COLUMN") &&
      upper.includes("TYPE") &&
      upper.includes("ALTER TABLE")
    ) {
      issues.push({
        severity: "danger",
        check: "alter_column_type",
        line,
        statement: stmt,
        message: "Changing a column type rewrites the entire table and acquires a full table lock.",
        suggestion: "Add a new column with the new type, backfill the data, then rename and drop the old column.",
      });
    }
  }
  return issues;
}

export function checkAddForeignKeyWithoutNotValid(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    if (
      upper.includes("FOREIGN KEY") &&
      upper.includes("ADD CONSTRAINT") &&
      !upper.includes("NOT VALID")
    ) {
      issues.push({
        severity: "danger",
        check: "add_foreign_key_without_not_valid",
        line,
        statement: stmt,
        message: "ADD FOREIGN KEY without NOT VALID performs a full table scan and holds a lock while validating.",
        suggestion: "Add with NOT VALID first: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID; then separately: VALIDATE CONSTRAINT ...;",
      });
    }
  }
  return issues;
}

export function checkAddUniqueConstraint(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    // ADD CONSTRAINT ... UNIQUE (not via an existing index)
    if (
      upper.includes("ADD CONSTRAINT") &&
      upper.includes("UNIQUE") &&
      !upper.includes("USING INDEX")
    ) {
      issues.push({
        severity: "danger",
        check: "add_unique_constraint",
        line,
        statement: stmt,
        message: "ADD UNIQUE CONSTRAINT performs a full table scan and locks the table during validation.",
        suggestion: "First create a unique index concurrently: CREATE UNIQUE INDEX CONCURRENTLY ... then attach it: ADD CONSTRAINT ... UNIQUE USING INDEX ...;",
      });
    }
  }
  return issues;
}

export function checkSetNotNull(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    if (upper.includes("SET NOT NULL") && upper.includes("ALTER TABLE")) {
      issues.push({
        severity: "danger",
        check: "set_not_null",
        line,
        statement: stmt,
        message: "SET NOT NULL scans the entire table to verify no nulls exist, locking the table.",
        suggestion: "Add a CHECK CONSTRAINT ... NOT NULL NOT VALID first, VALIDATE CONSTRAINT, then SET NOT NULL (which skips the scan if a valid NOT NULL check exists).",
      });
    }
  }
  return issues;
}

export function checkDropTable(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    if (/DROP\s+TABLE/i.test(stmt)) {
      issues.push({
        severity: "danger",
        check: "drop_table",
        line,
        statement: stmt,
        message: "DROP TABLE is destructive and irreversible.",
        suggestion: "Ensure all application code referencing this table has been removed and deployed before dropping.",
      });
    }
  }
  return issues;
}

export function checkTruncateTable(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    if (/^\s*TRUNCATE\b/i.test(stmt)) {
      issues.push({
        severity: "danger",
        check: "truncate_table",
        line,
        statement: stmt,
        message: "TRUNCATE removes all rows and acquires an ACCESS EXCLUSIVE lock.",
        suggestion: "Consider using DELETE with batching, or ensure this is intentional and the table can be fully cleared.",
      });
    }
  }
  return issues;
}

// ─── WARNING checks ───────────────────────────────────────────────────────────

export function checkRenameColumn(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    if (/RENAME\s+COLUMN/i.test(stmt)) {
      issues.push({
        severity: "warning",
        check: "rename_column",
        line,
        statement: stmt,
        message: "RENAME COLUMN can break ORM caching and any application code referencing the old column name.",
        suggestion: "Add a new column, update application code to dual-write, migrate data, then drop the old column.",
      });
    }
  }
  return issues;
}

export function checkRenameTable(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    if (/RENAME\s+TO/i.test(stmt) || /RENAME\s+TABLE/i.test(stmt)) {
      issues.push({
        severity: "warning",
        check: "rename_table",
        line,
        statement: stmt,
        message: "RENAME TABLE breaks all queries, ORM models, and application code referencing the old table name.",
        suggestion: "Create a view with the old name during the transition period, update app code, then drop the view.",
      });
    }
  }
  return issues;
}

export function checkDropColumn(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    if (/DROP\s+COLUMN/i.test(stmt)) {
      issues.push({
        severity: "warning",
        check: "drop_column",
        line,
        statement: stmt,
        message: "DROP COLUMN can break ORM attribute caching until the application is restarted.",
        suggestion: "First deploy code that ignores the column (e.g., ignored_columns in ActiveRecord), then drop it in a subsequent migration.",
      });
    }
  }
  return issues;
}

export function checkAddCheckConstraintWithoutNotValid(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase();
    if (
      upper.includes("ADD CONSTRAINT") &&
      upper.includes("CHECK") &&
      !upper.includes("NOT VALID")
    ) {
      issues.push({
        severity: "warning",
        check: "add_check_constraint_without_not_valid",
        line,
        statement: stmt,
        message: "ADD CHECK CONSTRAINT without NOT VALID performs a full table scan to validate existing rows.",
        suggestion: "Add with NOT VALID: ADD CONSTRAINT ... CHECK (...) NOT VALID; then separately: VALIDATE CONSTRAINT ...;",
      });
    }
  }
  return issues;
}

// ─── INFO checks ──────────────────────────────────────────────────────────────

export function checkMultipleOperations(sql: string): Issue[] {
  const alterCount = (sql.match(/ALTER\s+TABLE/gi) || []).length;
  if (alterCount > 3) {
    return [
      {
        severity: "info",
        check: "multiple_operations",
        line: 1,
        statement: `(${alterCount} ALTER TABLE statements found)`,
        message: `This migration contains ${alterCount} ALTER TABLE statements. Multiple operations are harder to roll back if something goes wrong.`,
        suggestion: "Consider splitting into smaller migrations, each doing one logical change.",
      },
    ];
  }
  return [];
}

export function checkBackfillWithoutWhere(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    const upper = stmt.toUpperCase().trim();
    // UPDATE without WHERE clause
    if (
      upper.startsWith("UPDATE") &&
      !upper.includes("WHERE") &&
      !upper.includes("RETURNING") // allow if it's just a single-row pattern
    ) {
      issues.push({
        severity: "info",
        check: "backfill_without_where",
        line,
        statement: stmt,
        message: "UPDATE without a WHERE clause modifies all rows and can timeout on large tables.",
        suggestion: "Do backfills in batches in application code, not in migrations. Example: update in chunks of 1000 rows.",
      });
    }
  }
  return issues;
}

export function checkIndexOnManyColumns(sql: string): Issue[] {
  const issues: Issue[] = [];
  for (const { stmt, line } of statementsWithLines(sql)) {
    if (/CREATE\s+(UNIQUE\s+)?INDEX/i.test(stmt)) {
      // Extract the column list inside parentheses after ON table_name (...)
      const match = stmt.match(/\(([^)]+)\)/);
      if (match) {
        const cols = match[1].split(",").map((c) => c.trim()).filter((c) => c.length > 0);
        if (cols.length >= 4) {
          issues.push({
            severity: "info",
            check: "index_on_many_columns",
            line,
            statement: stmt,
            message: `Index on ${cols.length} columns. Indexes on 4+ columns have diminishing returns and increase write overhead.`,
            suggestion: "Consider whether all columns are truly needed in this index. Often 2-3 column indexes cover most query patterns.",
          });
        }
      }
    }
  }
  return issues;
}

// ─── Check registry ───────────────────────────────────────────────────────────

export const ALL_CHECK_DEFINITIONS: CheckDefinition[] = [
  { name: "create_index_without_concurrently", severity: "danger", description: "CREATE INDEX without CONCURRENTLY locks the table.", suggestion: "Use CREATE INDEX CONCURRENTLY" },
  { name: "add_column_not_null_without_default", severity: "danger", description: "ADD COLUMN NOT NULL without DEFAULT causes a table rewrite.", suggestion: "Add nullable first, backfill, then add NOT NULL" },
  { name: "alter_column_type", severity: "danger", description: "ALTER COLUMN TYPE rewrites the entire table.", suggestion: "Add new column, backfill, rename" },
  { name: "add_foreign_key_without_not_valid", severity: "danger", description: "ADD FOREIGN KEY without NOT VALID holds a lock during full table scan.", suggestion: "Add with NOT VALID, then VALIDATE CONSTRAINT separately" },
  { name: "add_unique_constraint", severity: "danger", description: "ADD UNIQUE CONSTRAINT performs a full table scan.", suggestion: "Use CREATE UNIQUE INDEX CONCURRENTLY, then USING INDEX" },
  { name: "set_not_null", severity: "danger", description: "SET NOT NULL scans the entire table.", suggestion: "Use CHECK CONSTRAINT NOT VALID pattern first" },
  { name: "drop_table", severity: "danger", description: "DROP TABLE is destructive and irreversible.", suggestion: "Ensure all dependent code is removed first" },
  { name: "truncate_table", severity: "danger", description: "TRUNCATE acquires ACCESS EXCLUSIVE lock.", suggestion: "Use batched DELETE or ensure it's intentional" },
  { name: "rename_column", severity: "warning", description: "RENAME COLUMN breaks ORM caching and app code.", suggestion: "Add new column, dual-write, migrate, remove old" },
  { name: "rename_table", severity: "warning", description: "RENAME TABLE breaks all queries using the old name.", suggestion: "Add a view with the old name during transition" },
  { name: "drop_column", severity: "warning", description: "DROP COLUMN can break ORM attribute caching.", suggestion: "Ignore in app code first, then drop" },
  { name: "add_check_constraint_without_not_valid", severity: "warning", description: "ADD CHECK CONSTRAINT without NOT VALID does a full table scan.", suggestion: "Add with NOT VALID, then validate separately" },
  { name: "multiple_operations", severity: "info", description: "More than 3 ALTER TABLE statements in one migration.", suggestion: "Split into smaller migrations" },
  { name: "backfill_without_where", severity: "info", description: "UPDATE without WHERE modifies all rows.", suggestion: "Do backfills in batches in application code" },
  { name: "index_on_many_columns", severity: "info", description: "Index on 4+ columns has diminishing returns.", suggestion: "Consider fewer columns in the index" },
];

export const ALL_CHECKS = [
  checkCreateIndexWithoutConcurrently,
  checkAddColumnNotNullWithoutDefault,
  checkAlterColumnType,
  checkAddForeignKeyWithoutNotValid,
  checkAddUniqueConstraint,
  checkSetNotNull,
  checkDropTable,
  checkTruncateTable,
  checkRenameColumn,
  checkRenameTable,
  checkDropColumn,
  checkAddCheckConstraintWithoutNotValid,
  checkMultipleOperations,
  checkBackfillWithoutWhere,
  checkIndexOnManyColumns,
];