///|
fn skip_optional_word(tokens : Array[Token], start : Int, word : String) -> Int {
  if start < tokens.length() && token_is_word(tokens[start], word) {
    start + 1
  } else {
    start
  }
}

///|
fn qualified_name_at(tokens : Array[Token], start : Int) -> (Int, Int, String)? {
  guard start < tokens.length() else { return None }
  let first = tokens[start]
  guard first.kind == Word || first.kind == QuotedIdentifier else {
    return None
  }
  let mut i = start + 1
  let mut name = first.value.to_lower()
  while i + 1 < tokens.length() &&
        token_is_symbol(tokens[i], ".") &&
        (tokens[i + 1].kind == Word || tokens[i + 1].kind == QuotedIdentifier) {
    name = tokens[i + 1].value.to_lower()
    i = i + 2
  }
  Some((i, start, name))
}

///|
fn skip_table_modifiers(tokens : Array[Token], start : Int) -> Int {
  let mut i = skip_optional_word(tokens, start, "TABLE")
  i = skip_optional_word(tokens, i, "IF")
  i = skip_optional_word(tokens, i, "NOT")
  i = skip_optional_word(tokens, i, "EXISTS")
  i
}

///|
fn destructive_target(
  tokens : Array[Token],
  main_index : Int,
  keyword : String,
) -> (Int, Int, String)? {
  if keyword == "DELETE" {
    match top_level_word(tokens, main_index + 1, "FROM") {
      Some(from_index) => qualified_name_at(tokens, from_index + 1)
      None => None
    }
  } else if keyword == "DROP" || keyword == "TRUNCATE" {
    qualified_name_at(tokens, skip_table_modifiers(tokens, main_index + 1))
  } else {
    None
  }
}

///|
fn inbound_foreign_key_sources(
  schema : SchemaCatalog,
  target_table : String,
) -> Array[String] {
  let sources = []
  let target = target_table.to_lower()
  for table in schema.tables {
    for foreign_key in table.foreign_keys {
      if foreign_key.references_table.to_lower() == target &&
        !sources.contains(table.name) {
        sources.push(table.name)
      }
    }
  }
  sources
}

///|
fn check_schema_referenced_table(
  tokens : Array[Token],
  statement_index : Int,
  findings : Array[Finding],
  schema : SchemaCatalog,
) -> Unit {
  let main = main_keyword_index(tokens)
  guard main is Some(main_index) else { return }
  let keyword = tokens[main_index].value
  match destructive_target(tokens, main_index, keyword) {
    None => ()
    Some((_, table_index, table_name)) =>
      match schema.find_table(table_name) {
        None => ()
        Some(table) => {
          let sources = inbound_foreign_key_sources(schema, table.name)
          if !sources.is_empty() {
            add_finding(
              findings,
              tokens,
              statement_index,
              table_index,
              "SQL201",
              "Destructive operation on referenced table",
              Warning,
              "Table '\{table.name}' is referenced by foreign keys from: \{sources.join(", ")}.",
              "Review dependent rows and foreign-key actions before deleting or removing this table.",
            )
          }
        }
      }
  }
}

///|
fn dropped_column(
  tokens : Array[Token],
  main_index : Int,
) -> (Int, Int, String, String)? {
  let table_start = skip_table_modifiers(tokens, main_index + 1)
  match qualified_name_at(tokens, table_start) {
    None => None
    Some((after_table, table_index, table_name)) =>
      match top_level_word(tokens, after_table, "DROP") {
        None => None
        Some(drop_index) => {
          let after_drop = skip_optional_word(tokens, drop_index + 1, "COLUMN")
          match qualified_name_at(tokens, after_drop) {
            Some((_, column_index, column_name)) =>
              Some((table_index, column_index, table_name, column_name))
            None => None
          }
        }
      }
  }
}

///|
fn check_schema_protected_column(
  tokens : Array[Token],
  statement_index : Int,
  findings : Array[Finding],
  schema : SchemaCatalog,
) -> Unit {
  let main = main_keyword_index(tokens)
  guard main is Some(main_index) && tokens[main_index].value == "ALTER" else {
    return
  }
  match dropped_column(tokens, main_index) {
    None => ()
    Some((table_index, column_index, table_name, column_name)) => {
      ignore(table_index)
      match schema.find_table(table_name) {
        None => ()
        Some(table) =>
          if table.has_column(column_name) &&
            (
              table.column_is_protected(column_name) ||
              schema.column_is_referenced(table.name, column_name)
            ) {
            add_finding(
              findings,
              tokens,
              statement_index,
              column_index,
              "SQL202",
              "Dropping schema-protected column",
              Error,
              "Column '\{table.name}.\{column_name}' is used by an index or a foreign-key relationship.",
              "Remove or replace dependent indexes and foreign keys before dropping the column.",
            )
          }
      }
    }
  }
}

///|
fn check_schema_rules(
  tokens : Array[Token],
  statement_index : Int,
  findings : Array[Finding],
  schema : SchemaCatalog,
) -> Unit {
  if schema.tables.is_empty() {
    return
  }
  check_schema_referenced_table(tokens, statement_index, findings, schema)
  check_schema_protected_column(tokens, statement_index, findings, schema)
}