///|
fn ident(v : String) -> String {
  "\"\{v}\""
}

///|
fn qualified(schema : String, table : String) -> String {
  "\{ident(schema)}.\{ident(table)}"
}

///|
fn literal(v : String) -> String {
  let escaped = v
    .replace_all(old="\\", new="\\\\")
    .replace_all(old="\n", new="\\n")
    .replace_all(old="\r", new="\\r")
    .replace_all(old="'", new="''")
  "E'\{escaped}'"
}

///|
fn assert_sql(condition : String, message : String) -> String {
  "DO $moonbranch$ BEGIN IF NOT (\{condition}) THEN RAISE EXCEPTION \{literal(message)}; END IF; END $moonbranch$;\n"
}

///|
fn catalog_checks(spec : Spec) -> String {
  let sql = StringBuilder()
  for table in spec.tables {
    let name = qualified(spec.schema, table.name)
    let relation = "\{literal(name)}::pg_catalog.regclass"
    let columns = table.columns.map(literal).join(",")
    let pk = @json.ToJson::to_json(table.key).stringify()
    let key_cols = table.key.copy()
    for fk in table.foreign_keys {
      key_cols.append(fk.columns)
    }
    let keys = key_cols.map(literal).join(",")
    let include_cols = "a.attname IN (\{columns}) AND a.attnum > 0 AND NOT a.attisdropped"
    sql.write_string(
      assert_sql(
        "EXISTS (SELECT 1 FROM pg_catalog.pg_class WHERE oid = \{relation} AND relkind = 'r' AND NOT relispartition AND NOT relhassubclass AND NOT relrowsecurity)",
        "unsupported relation kind or row-level policy: \{table.name}",
      ),
    )
    sql.write_string(
      assert_sql(
        "(SELECT count(*) FROM pg_catalog.pg_attribute a WHERE a.attrelid = \{relation} AND \{include_cols} AND a.attgenerated = '' AND a.attidentity = '') = \{table.columns.length()}",
        "missing, generated or identity column: \{table.name}",
      ),
    )
    sql.write_string(
      assert_sql(
        "NOT EXISTS (SELECT 1 FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_collation c ON c.oid = a.attcollation WHERE a.attrelid = \{relation} AND a.attname IN (\{keys}) AND (a.atttypid NOT IN (20,21,23,25,1043,2950) OR NOT COALESCE(c.collisdeterministic, true)))",
        "unsupported key type or collation: \{table.name}",
      ),
    )
    let local_cols = "(SELECT jsonb_agg(a.attname ORDER BY k.ord) FROM unnest(c.conkey) WITH ORDINALITY k(num, ord) JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.num)"
    let remote = "(SELECT jsonb_agg(a.attname ORDER BY k.ord) FROM unnest(c.confkey) WITH ORDINALITY k(num, ord) JOIN pg_catalog.pg_attribute a ON a.attrelid = c.confrelid AND a.attnum = k.num)"
    sql.write_string(
      assert_sql(
        "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conrelid = \{relation} AND c.contype = 'p' AND \{local_cols} = \{literal(pk)}::jsonb)",
        "primary key differs from recipe: \{table.name}",
      ),
    )
    let fk_checks : Array[String] = []
    for fk in table.foreign_keys {
      let from_cols = @json.ToJson::to_json(fk.columns).stringify()
      let to_cols = @json.ToJson::to_json(fk.references).stringify()
      let parent = literal(qualified(spec.schema, fk.parent))
      fk_checks.push(
        "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conrelid = \{relation} AND c.contype = 'f' AND c.convalidated AND c.confmatchtype = 's' AND c.confrelid = \{parent}::regclass AND \{local_cols} = \{literal(from_cols)}::jsonb AND \{remote} = \{literal(to_cols)}::jsonb)",
      )
    }
    fk_checks.push(
      "(SELECT count(*) FROM pg_catalog.pg_constraint c WHERE c.conrelid = \{relation} AND c.contype = 'f') = \{table.foreign_keys.length()}",
    )
    sql.write_string(
      assert_sql(
        fk_checks.join(" AND "),
        "foreign keys differ from recipe: \{table.name}",
      ),
    )
  }
  sql.to_string()
}

///|
let pg_settings : String =
  #|SET LOCAL search_path = pg_catalog;
  #|SET LOCAL standard_conforming_strings = on;
  #|SET LOCAL statement_timeout = '30s';
  #|SET LOCAL lock_timeout = '5s';
  #|SET LOCAL TimeZone = 'UTC';
  #|SET LOCAL DateStyle = 'ISO, YMD';
  #|SET LOCAL IntervalStyle = 'postgres';
  #|SET LOCAL extra_float_digits = 3;
  #|SET LOCAL bytea_output = 'hex';
  #|

///|
pub let capture_limit : Int32 = 50000

///|
fn lock_list(spec : Spec) -> String {
  let names = spec.tables.map(fn(t) { qualified(spec.schema, t.name) })
  names.sort()
  names.join(", ")
}

///|
/// Read each declared table in one consistent snapshot. Masked columns are
/// replaced in PostgreSQL before any source value reaches the client.
pub fn capture_sql(spec : Spec) -> String raise BranchError {
  validate(spec)
  let queries : Array[String] = []
  for table in spec.tables {
    let name = qualified(spec.schema, table.name)
    let fields = table.columns
      .map(fn(col) {
        let expr = match mask_value(spec, table.name, col) {
          Some(String(value)) => "\{literal(value)}::text"
          Some(Null) => "NULL::text"
          _ => "t.\{ident(col)}::text"
        }
        "\{expr} AS \{ident(col)}"
      })
      .join(", ")
    queries.push(
      "SELECT \{literal(table.name)} AS name, COALESCE(json_agg(r), '[]'::json) AS rows FROM (SELECT \{fields} FROM \{name} t LIMIT \{capture_limit + 1}) r",
    )
  }
  let body = queries.join("\nUNION ALL\n")
  "BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;\n\{pg_settings}LOCK TABLE \{lock_list(spec)} IN ACCESS SHARE MODE;\n\{catalog_checks(spec)}SELECT json_build_object('tables', json_object_agg(name, rows)) FROM (\{body}) AS snapshot;\nCOMMIT;\n"
}

///|
pub fn check_capture_size(data : Dataset) -> Unit raise BranchError {
  for name, rows in data.tables {
    require(
      rows.length() <= capture_limit,
      "capture scan cap exceeded in \{name}; use a smaller source database",
    )
  }
}