import { createHash } from "node:crypto";
import { readFile } from "node:fs/promises";
import path from "node:path";
import process from "node:process";
import { fileURLToPath } from "node:url";

const scriptDirectory = path.dirname(fileURLToPath(import.meta.url));
const backendDirectory = path.resolve(scriptDirectory, "..");
const upPath = path.join(
  backendDirectory,
  "migrations",
  "001_create_condon_schema.up.sql"
);
const downPath = path.join(
  backendDirectory,
  "migrations",
  "001_create_condon_schema.down.sql"
);
const legacyUpPath = path.join(
  backendDirectory,
  "migrations",
  "002_legacy_import_and_bookings.up.sql"
);
const legacyDownPath = path.join(
  backendDirectory,
  "migrations",
  "002_legacy_import_and_bookings.down.sql"
);
const deleteUpPath = path.join(
  backendDirectory,
  "migrations",
  "003_delete_usage_record.up.sql"
);
const deleteDownPath = path.join(
  backendDirectory,
  "migrations",
  "003_delete_usage_record.down.sql"
);

const [upSql, downSql, legacyUpSql, legacyDownSql, deleteUpSql, deleteDownSql] = await Promise.all([
  readFile(upPath, "utf8"),
  readFile(downPath, "utf8"),
  readFile(legacyUpPath, "utf8"),
  readFile(legacyDownPath, "utf8"),
  readFile(deleteUpPath, "utf8"),
  readFile(deleteDownPath, "utf8")
]);

const checks = [];
const record = (name, ok, detail = "") => {
  checks.push({ name, ok, detail });
};
const count = (source, pattern) => [...source.matchAll(pattern)].length;

record(
  "up creates exact schema without reuse",
  count(upSql, /^CREATE SCHEMA condon;$/gm) === 1
    && !/\bIF\s+NOT\s+EXISTS\b/i.test(upSql)
);
record(
  "up has no cross-schema references",
  !/\b(?:public|booking|finance|ingestion)\s*\./i.test(upSql)
);
record(
  "down has no cross-schema references",
  !/\b(?:public|booking|finance|ingestion)\s*\./i.test(downSql)
);
record(
  "no shared-database mutations",
  !/\b(?:CREATE\s+EXTENSION|ALTER\s+(?:ROLE|DATABASE|SYSTEM)|SET\s+ROLE|COPY|GRANT)\b/i.test(
    `${upSql}\n${downSql}`
  )
);
record(
  "no cascade or broad conditional drop",
  !/\bCASCADE\b/i.test(`${upSql}\n${downSql}`)
    && !/\bDROP\b[\s\S]{0,80}\bIF\s+EXISTS\b/i.test(downSql)
);
record(
  "up does not delete or drop",
  !/^\s*(?:DELETE|TRUNCATE|DROP)\b/im.test(upSql)
);

const upMutationTargets = [
  ...upSql.matchAll(
    /^\s*(?:CREATE\s+(?:TABLE|FUNCTION)|INSERT\s+INTO|UPDATE)\s+([a-z_][a-z0-9_.]*)/gim
  )
].map(match => match[1].toLowerCase());
record(
  "all up mutation targets are condon-qualified",
  upMutationTargets.length > 0
    && upMutationTargets.every(target => target.startsWith("condon.")),
  upMutationTargets.join(", ")
);

const downDropTargets = [
  ...downSql.matchAll(
    /^\s*DROP\s+(?:FUNCTION|INDEX|TABLE|SCHEMA)\s+([a-z_][a-z0-9_.]*)/gim
  )
].map(match => match[1].toLowerCase());
record(
  "all down drop targets are condon-qualified",
  downDropTargets.length === 16
    && downDropTargets.every(target => target === "condon" || target.startsWith("condon.")),
  downDropTargets.join(", ")
);

record(
  "expected table count",
  count(upSql, /^CREATE TABLE condon\./gm) === 6
);
record(
  "expected function count and security mode",
  count(upSql, /^CREATE FUNCTION condon\./gm) === 3
    && count(upSql, /^SECURITY INVOKER$/gm) === 3
    && count(upSql, /^SET search_path = pg_catalog$/gm) === 3
);
record(
  "public execute revoked on new functions",
  count(upSql, /^REVOKE ALL ON FUNCTION condon\./gm) === 3
);
record(
  "indexes are created only on condon tables",
  count(upSql, /^CREATE INDEX /gm) === 6
    && count(upSql, /^\s+ON condon\./gm) === 6
);
record(
  "foreign keys stay inside condon",
  count(upSql, /^\s+REFERENCES condon\./gm) >= 6
    && !/^\s+REFERENCES (?!condon\.)/gim.test(upSql)
);

const seededRoomTypes = [
  ...upSql.matchAll(/^\s*\('([A-Z0-9]+)',\s*(?:[123]|NULL),\s*(?:true|false)\)[,;]$/gm)
].map(match => match[1]);
record(
  "exact room-type seed set",
  JSON.stringify(seededRoomTypes.sort()) === JSON.stringify(
    ["AC2", "RM1", "RM2", "RM3", "RM4", "SU1", "SU2", "SU3", "SU6", "UG1", "UG2"].sort()
  )
);
record(
  "derived values are function-owned",
  upSql.includes("v_night_count := p_check_out - p_check_in;")
    && upSql.includes("v_use_nights := v_night_count * v_multiplier;")
    && upSql.includes("v_balance_after := v_balance_before - v_use_nights;")
);
record(
  "down removes only exact new object set",
  count(downSql, /^DROP FUNCTION condon\./gm) === 3
    && count(downSql, /^DROP INDEX condon\./gm) === 6
    && count(downSql, /^DROP TABLE condon\./gm) === 6
    && count(downSql, /^DROP SCHEMA condon;$/gm) === 1
);
record(
  "legacy migration stays inside condon",
  !/\b(?:public|booking|finance|ingestion)\s*\./i.test(`${legacyUpSql}\n${legacyDownSql}`)
    && !/\bCASCADE\b/i.test(`${legacyUpSql}\n${legacyDownSql}`)
);
record(
  "legacy migration has booking and source fields",
  /^CREATE TABLE condon\.bookings/gm.test(legacyUpSql)
    && legacyUpSql.includes("raw_used_room_type")
    && legacyUpSql.includes("source_sheet")
    && legacyUpSql.includes("source_sequence")
    && legacyUpSql.includes("usage_records_confirmation_booking_fk")
);
record(
  "legacy migration has 1:N API function",
  /^CREATE FUNCTION condon\.create_usage_record_v2\(/m.test(legacyUpSql)
    && legacyUpSql.includes("ON CONFLICT (confirmation_no) DO NOTHING")
    && legacyUpSql.includes("rule_version = 'legacy-source'")
    && /^REVOKE ALL ON FUNCTION condon\.create_usage_record_v2/gm.test(legacyUpSql)
);
record(
  "legacy migration down reverses its objects",
  /^DROP TABLE condon\.bookings;$/gm.test(legacyDownSql)
    && /^DROP FUNCTION condon\.create_usage_record_v2/gm.test(legacyDownSql)
    && /^DROP INDEX condon\.usage_records_source_location_key;$/gm.test(legacyDownSql)
);
record(
  "delete migration is an internal transactional function",
  /^CREATE FUNCTION condon\.delete_usage_record\(\s*p_usage_record_id uuid\s*\)/m.test(deleteUpSql)
    && /^RETURNS void$/m.test(deleteUpSql)
    && /^SECURITY INVOKER$/m.test(deleteUpSql)
    && deleteUpSql.includes("FOR UPDATE")
    && deleteUpSql.includes("DELETE FROM condon.entitlement_ledger")
    && deleteUpSql.includes("UPDATE condon.entitlement_periods")
    && /^REVOKE ALL ON FUNCTION condon\.delete_usage_record\(uuid\) FROM PUBLIC;$/m.test(deleteUpSql)
);
record(
  "delete migration down reverses its function",
  /^DROP FUNCTION condon\.delete_usage_record\(uuid\);$/m.test(deleteDownSql)
    && !/\bCASCADE\b/i.test(deleteDownSql)
);

for (const check of checks) {
  process.stdout.write(
    `${check.ok ? "PASS" : "FAIL"} ${check.name}${check.ok || !check.detail ? "" : `: ${check.detail}`}\n`
  );
}

const checksum = createHash("sha256").update(upSql).digest("hex");
process.stdout.write(`migration checksum ${checksum}\n`);

if (checks.some(check => !check.ok)) process.exitCode = 1;