Beginner

-- File: 001-create-users.sql
-- => DbUp identifies scripts by filename; name must be unique across all scripts
-- => Sequential numeric prefix (001, 002, ...) controls execution order
 
-- Create the users table with essential audit columns
CREATE TABLE users (
    -- => UUID primary key avoids sequential integer exposure and supports distributed inserts
    id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => VARCHAR without explicit length stores up to 1 GB; add CHECK for practical max
    username     VARCHAR      NOT NULL,
    email        VARCHAR      NOT NULL,
    -- => TIMESTAMPTZ stores UTC instant + timezone offset; safe across DST transitions
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);
-- => After execution: table "users" exists; DbUp records "001-create-users.sql" in schemaversions
-- => Subsequent runs skip this script because the journal entry already exists

open DbUp
open System.Reflection
 
// Retrieve connection string from environment (never hardcode credentials)
let connStr =
    System.Environment.GetEnvironmentVariable("DATABASE_URL")
// => connStr is e.g. "Host=localhost;Database=myapp;Username=app;Password=secret"
// => Null if env var absent; validate before passing to builder
 
// Assemble the upgrade engine using the fluent builder
let upgradeEngine =
    DeployChanges.To
        // => Target PostgreSQL; DbUp uses Npgsql internally for all queries
        .PostgresqlDatabase(connStr)
        // => Discovers all *.sql files embedded in the calling assembly's resources
        .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
        // => Attaches console logger; prints "Executing script: 001-create-users.sql" etc.
        .LogToConsole()
        // => Validates configuration and returns UpgradeEngine; no DB work yet
        .Build()
// => upgradeEngine is DbUp.Engine.UpgradeEngine; ready to call PerformUpgrade()
 
let result = upgradeEngine.PerformUpgrade()
// => Connects to DB, queries schemaversions, runs pending scripts in order
// => result.Successful is true when all pending scripts executed without error
// => result.Scripts contains the list of ScriptName strings that were executed this run

open DbUp
 
// Option A: inline connection string (development only, never commit passwords)
let engineDev =
    DeployChanges.To
        // => Npgsql connection string; DbUp opens this connection when PerformUpgrade runs
        .PostgresqlDatabase("Host=localhost;Database=myapp_dev;Username=dev;Password=dev")
        .WithScriptsEmbeddedInAssembly(System.Reflection.Assembly.GetExecutingAssembly())
        .LogToConsole()
        .Build()
// => engineDev targets the local dev database; use for quick local testing
 
// Option B: environment variable (production pattern)
let connStr = System.Environment.GetEnvironmentVariable("DATABASE_URL")
// => Reads DATABASE_URL; typical in Docker, Kubernetes, Heroku, Vercel, and Fly.io
// => connStr is null if not set; handle null before passing to avoid NullReferenceException
 
let engineProd =
    DeployChanges.To
        // => Npgsql interprets "sslmode=require" for TLS-enforced managed PostgreSQL
        .PostgresqlDatabase(connStr + ";sslmode=require;Pooling=false")
        // => Pooling=false recommended for migration runs; migrations are one-shot not long-lived
        .WithScriptsEmbeddedInAssembly(System.Reflection.Assembly.GetExecutingAssembly())
        .LogToConsole()
        .Build()
// => engineProd targets the production database via environment-injected credentials

// ─── In your .fsproj file ────────────────────────────────────────────────────
// <ItemGroup>
//   <!-- Embed all SQL files under db/migrations/ as assembly resources -->
//   <EmbeddedResource Include="db/migrations/**/*.sql" />
// </ItemGroup>
// => Build embeds each .sql file with resource name: "MyApp.db.migrations.001-create-users.sql"
// => DbUp uses the resource name as the script name stored in schemaversions
 
open DbUp
open System.Reflection
 
// Get reference to the assembly that contains the embedded SQL resources
let asm = Assembly.GetAssembly(typeof<MyApp.Program.Marker>)
// => Assembly.GetAssembly locates the assembly where Program.Marker type is defined
// => Use a dedicated Marker type to pin the assembly reference (avoids "executing assembly" ambiguity)
// => typeof<Program.Marker> is an F# type defined in your application project
 
let engine =
    DeployChanges.To
        .PostgresqlDatabase(System.Environment.GetEnvironmentVariable("DATABASE_URL"))
        // => Scans asm for all embedded resources ending in ".sql"
        // => Resources discovered: "MyApp.db.migrations.001-create-users.sql", "002-...", etc.
        .WithScriptsEmbeddedInAssembly(asm)
        .LogToConsole()
        .Build()
// => engine knows exactly which scripts are available; no filesystem paths involved
// => Assembly can be deployed as a single DLL and scripts travel with it

-- File: 001-create-users.sql
-- => First migration; creates the foundational users table
-- => "001" prefix ensures this runs before "002"; lexicographic sort: "001" < "002" < "010"
 
CREATE TABLE users (
    id         UUID  PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => username must be unique; constraint added in this script not a later one
    username   VARCHAR NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => After run: schemaversions contains row with ScriptName = "001-create-users.sql"

-- File: 002-create-expenses.sql
-- => Second migration; depends on users table created in 001
-- => Numeric prefix guarantees 001 runs first; without prefix, "create-expenses" sorts before "create-users"
 
CREATE TABLE expenses (
    id      UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => Foreign key references users(id); DbUp must have run 001 first
    user_id UUID    NOT NULL REFERENCES users (id),
    amount  DECIMAL(18, 6) NOT NULL,
    -- => DATE stores calendar date without time; use for business dates (invoice date, expense date)
    date    DATE    NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => After run: expenses table exists; schemaversions has "002-create-expenses.sql"

-- After running 001-create-users.sql and 002-create-expenses.sql:
-- DbUp creates schemaversions automatically on first run if it does not exist
 
-- => Inspect the journal table to see migration history
SELECT scriptname, applied
FROM   schemaversions
ORDER  BY applied;
-- => Returns:
-- =>   scriptname                   | applied
-- =>   -----------------------------|------------------------------
-- =>   001-create-users.sql         | 2026-03-27 00:01:12.345+07
-- =>   002-create-expenses.sql      | 2026-03-27 00:01:12.389+07
 
-- => On second PerformUpgrade() call, DbUp reads these rows and skips both scripts
-- => Only scripts NOT in schemaversions will execute

// DbUp creates schemaversions before executing any user scripts
// No action needed; just call PerformUpgrade() and DbUp manages the journal
let result =
    DeployChanges.To
        .PostgresqlDatabase(connStr)
        .WithScriptsEmbeddedInAssembly(asm)
        .LogToConsole()
        .Build()
        .PerformUpgrade()
// => On first run: creates schemaversions table, then executes all scripts
// => On subsequent runs: reads schemaversions, skips applied scripts, runs only new ones
// => result.Scripts lists scripts executed THIS run (empty list when all are already applied)

open DbUp
 
let result =
    DeployChanges.To
        .PostgresqlDatabase(connStr)
        .WithScriptsEmbeddedInAssembly(asm)
        // => Registers ConsoleUpgradeLog; writes to Console.Write / Console.Error
        // => Output format: "DbUp: Executing script: 001-create-users.sql"
        // => Errors write to stderr with script name and exception message
        .WithConsoleLogger()
        .Build()
        .PerformUpgrade()
// => Console output during migration:
// =>   Beginning database upgrade
// =>   Checking whether journal table exists..
// =>   Journal table does not exist
// =>   Executing Database Server script '001-create-users.sql'..
// =>   Executing Database Server script '002-create-expenses.sql'..
// =>   Successfully upgraded database to version 002
// => result.Successful is true; result.Scripts has ["001-create-users.sql", "002-create-expenses.sql"]

-- File: 003-create-attachments.sql
-- => Creates the attachments table; depends on expenses table from 002
 
CREATE TABLE attachments (
    -- => UUID primary key; no sequential integer leak, safe for distributed systems
    id           UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
 
    -- => Foreign key to expenses; restricts orphan attachments at the DB level
    expense_id   UUID    NOT NULL REFERENCES expenses (id),
 
    -- => filename stores the original upload name; VARCHAR sufficient, no BYTEA needed
    filename     VARCHAR NOT NULL,
 
    -- => content_type stores MIME type e.g. "image/jpeg"; VARCHAR not ENUM for flexibility
    content_type VARCHAR NOT NULL,
 
    -- => BIGINT for file size in bytes; INT overflows at ~2 GB, BIGINT handles up to 9 EB
    file_size    BIGINT  NOT NULL,
 
    -- => BYTEA stores binary file content inline; consider object storage for files > 1 MB
    data         BYTEA   NOT NULL,
 
    -- => created_at only; attachments are immutable (upload once, never edit)
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => Table created; DbUp records "003-create-attachments.sql" in schemaversions
-- => Foreign key expense_id enforces referential integrity without application-level checks

-- File: 004-add-display-name-to-users.sql
-- => Adds display_name column to existing users table
-- => Pattern: add nullable first, then backfill, then add NOT NULL constraint
 
-- Step 1: Add column as nullable (fast metadata operation, no table lock)
ALTER TABLE users
    ADD COLUMN display_name VARCHAR;
-- => display_name is NULL for all existing rows immediately after this statement
-- => No table rewrite; PostgreSQL 11+ handles nullable ADD COLUMN in O(1)
 
-- Step 2: Backfill existing rows with a sensible default
UPDATE users
SET    display_name = username
WHERE  display_name IS NULL;
-- => Sets display_name to username for every existing row
-- => Runs as a full table scan; consider batching for tables > 1M rows
 
-- Step 3: Add NOT NULL constraint after backfill
ALTER TABLE users
    ALTER COLUMN display_name SET NOT NULL;
-- => Validates that no NULL values remain (fast CHECK scan, not full rewrite in PostgreSQL 12+)
-- => display_name is now NOT NULL for all existing and future rows

-- File: 005-add-indexes-to-users.sql
-- => Adds performance indexes to the users table
-- => Run after 001-create-users.sql; users table must already exist
 
-- Unique index on email: enforces uniqueness AND speeds up login lookups
CREATE UNIQUE INDEX ix_users_email
    ON users (email);
-- => Creates B-tree index; UNIQUE means duplicate emails fail at the DB level
-- => INSERT/UPDATE with duplicate email returns error code 23505 (unique_violation)
-- => Lookup by email: O(log n) instead of O(n) full scan
 
-- Unique index on username: login and mention lookups by username
CREATE UNIQUE INDEX ix_users_username
    ON users (username);
-- => Same structure; username lookups are now O(log n)
-- => Two unique indexes: any INSERT must pass both uniqueness checks
 
-- Non-unique index on created_at: speeds up date-range queries
CREATE INDEX ix_users_created_at
    ON users (created_at DESC);
-- => DESC order matches "ORDER BY created_at DESC" queries (newest first)
-- => Useful for admin dashboards showing recently registered users
-- => After this script: schemaversions records "005-add-indexes-to-users.sql"

-- File: 006-add-foreign-key-expenses-to-users.sql
-- => Adds foreign key from expenses.user_id to users.id
-- => Requires: users table (001) and expenses table (002) already applied
 
ALTER TABLE expenses
    ADD CONSTRAINT fk_expenses_user_id
    FOREIGN KEY (user_id)
    REFERENCES users (id);
-- => Validates: all existing expenses.user_id values exist in users.id
-- => If any orphan rows exist, this statement FAILS with error 23503 (foreign_key_violation)
-- => After constraint added: INSERT/UPDATE with non-existent user_id is rejected by DB
 
-- Verify the constraint exists
-- SELECT conname, contype
-- FROM   pg_constraint
-- WHERE  conrelid = 'expenses'::regclass;
-- => Returns: fk_expenses_user_id | f  (f = foreign key)

-- File: 007-add-unique-constraint-revoked-tokens.sql
-- => Adds unique constraint on jti column of revoked_tokens table
-- => Prevents duplicate token revocations from being recorded
 
-- Add unique constraint on token jti (JWT ID)
ALTER TABLE revoked_tokens
    ADD CONSTRAINT uq_revoked_tokens_jti
    UNIQUE (jti);
-- => Creates unique B-tree index named uq_revoked_tokens_jti
-- => Duplicate INSERT with same jti fails with error 23505 (unique_violation)
-- => Application should catch 23505 and treat it as idempotent (token already revoked)
 
-- Multi-column unique constraint example: composite uniqueness
-- ALTER TABLE refresh_tokens
--     ADD CONSTRAINT uq_refresh_tokens_user_hash
--     UNIQUE (user_id, token_hash);
-- => Uniqueness enforced across the combination of user_id AND token_hash
-- => Same token_hash allowed for different user_id values (intended behavior)
-- => After script: schemaversions records "007-add-unique-constraint-revoked-tokens.sql"

-- File: 008-add-not-null-created-by.sql
-- => Adds created_by and updated_by audit columns to users table
-- => Uses safe three-step pattern: add nullable, backfill, enforce NOT NULL
 
-- Step 1: Add columns as nullable with empty-string defaults for new rows
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS created_by VARCHAR(255) NOT NULL DEFAULT 'system',
    ADD COLUMN IF NOT EXISTS updated_by VARCHAR(255) NOT NULL DEFAULT 'system';
-- => IF NOT EXISTS prevents error if column already exists (idempotent guard)
-- => DEFAULT 'system' fills existing rows immediately in PostgreSQL 11+
-- => New rows without explicit created_by/updated_by get 'system' automatically
-- => After this statement: all rows have created_by = 'system' and updated_by = 'system'
 
-- Step 2 (optional): Override default for specific rows if business logic requires
-- UPDATE users SET created_by = 'migration' WHERE created_at < '2026-01-01';
-- => Backfill can be scoped to date ranges or other predicates for partial overrides
 
-- No Step 3 needed: DEFAULT 'system' with NOT NULL added atomically in PostgreSQL 11+
-- => PostgreSQL 11+ handles NOT NULL + DEFAULT in ADD COLUMN as a metadata-only operation
-- => No table rewrite; no lock on existing rows

open DbUp
 
let runMigrations (connStr: string) (asm: System.Reflection.Assembly) =
    // Build the upgrade engine with all required configuration
    let engine =
        DeployChanges.To
            .PostgresqlDatabase(connStr)
            // => Discovers all embedded .sql resources from the provided assembly
            .WithScriptsEmbeddedInAssembly(asm)
            // => Console logger prints each script name and success/failure status
            .WithConsoleLogger()
            .Build()
    // => engine is configured but no DB work has happened yet
 
    // Execute pending scripts and capture the result
    let result = engine.PerformUpgrade()
    // => Opens Npgsql connection to PostgreSQL
    // => Queries schemaversions to find pending scripts
    // => Executes each pending script in order inside its own implicit transaction
    // => Inserts journal row on each successful script execution
    // => Returns immediately on first script failure (does not continue)
 
    result
// => Returns DatabaseUpgradeResult with:
// =>   .Successful: bool (true = all pending scripts ran without error)
// =>   .Scripts: seq<SqlScript> (scripts executed this run)
// =>   .Error: Exception (null when Successful; populated on failure)
// =>   .ErrorScript: SqlScript (the script that caused the failure; null when Successful)

open DbUp
 
let startWithMigrations (connStr: string) (asm: System.Reflection.Assembly) =
    let result =
        DeployChanges.To
            .PostgresqlDatabase(connStr)
            .WithScriptsEmbeddedInAssembly(asm)
            .WithConsoleLogger()
            .Build()
            .PerformUpgrade()
    // => result is DatabaseUpgradeResult; check .Successful before proceeding
 
    if result.Successful then
        // => All pending scripts ran successfully
        // => result.Scripts contains the SqlScript list executed this run
        let count = result.Scripts |> Seq.length
        // => count is 0 when database was already up to date (no pending scripts)
        // => count > 0 when new migrations were applied
        printfn "Migration complete. %d script(s) applied." count
        // => Application can now safely start; schema is at expected version
        true
    else
        // => At least one script failed; application should NOT start
        let scriptName =
            if result.ErrorScript <> null then result.ErrorScript.Name
            // => ErrorScript.Name is the filename e.g. "009-add-index.sql"
            else "unknown"
        // => result.Error is the Exception thrown during script execution
        eprintfn "Migration FAILED on script '%s': %s" scriptName result.Error.Message
        // => Log to stderr; captured by container orchestration and APM tools
        false
// => Returns true on success, false on failure; caller decides whether to exit(1) or retry

open Npgsql
open DbUp
 
// Build explicit NpgsqlConnection for scenarios requiring shared connection control
let buildConnection (connStr: string) =
    // => NpgsqlConnection wraps Npgsql driver; requires Npgsql NuGet package
    let conn = new NpgsqlConnection(connStr)
    // => Connection is closed at this point; Open() or OpenAsync() not called yet
    // => DbUp PostgresqlDatabase(NpgsqlConnection) overload opens/closes internally
    conn
// => Returns NpgsqlConnection instance; caller controls lifecycle
 
// Use explicit connection with DbUp
let runWithExplicitConnection (connStr: string) (asm: System.Reflection.Assembly) =
    use conn = buildConnection connStr
    // => use binding: F# disposes conn when this function exits (Dispose called automatically)
 
    let result =
        DeployChanges.To
            // => NpgsqlConnection overload; DbUp uses this exact connection object
            .PostgresqlDatabase(conn)
            .WithScriptsEmbeddedInAssembly(asm)
            .WithConsoleLogger()
            .Build()
            .PerformUpgrade()
    // => conn is managed by DbUp during PerformUpgrade; do not call conn.Open() manually
    // => After PerformUpgrade: conn is still open; use keyword disposes it on exit
 
    result.Successful
// => Returns bool; connection automatically disposed after function returns

-- File: 001-create-users.sql
-- => First: foundation table; no dependencies
CREATE TABLE users (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    username   VARCHAR     NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => Completes; schemaversions: ["001-create-users.sql"]

-- File: 002-create-expenses.sql
-- => Second: depends on users; runs after 001
CREATE TABLE expenses (
    id      UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => Foreign key references users.id; 001 must have run first
    user_id UUID    NOT NULL REFERENCES users (id),
    amount  DECIMAL(18, 6) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => Completes; schemaversions: ["001-create-users.sql", "002-create-expenses.sql"]

-- File: 003-create-attachments.sql
-- => Third: depends on expenses; runs after 002
CREATE TABLE attachments (
    id         UUID  PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => Foreign key references expenses.id; 002 must have run first
    expense_id UUID  NOT NULL REFERENCES expenses (id),
    filename   VARCHAR NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => Completes; schemaversions: ["001...", "002...", "003-create-attachments.sql"]
-- => All three tables now exist; dependency chain satisfied by ordering guarantees

-- File: 010-drop-url-from-attachments.sql
-- => Removes the url column that was superseded by object storage URLs computed at runtime
-- => Safe to drop: application code no longer reads or writes attachments.url
 
-- Drop dependent index first (if any exist on this column)
DROP INDEX IF EXISTS ix_attachments_url;
-- => IF EXISTS prevents error if index was never created or was already dropped
-- => Must drop before column; column drop with dependent index fails
 
-- Drop the column
ALTER TABLE attachments
    DROP COLUMN IF EXISTS url;
-- => IF EXISTS: idempotent; no error if column was already dropped (re-run safe)
-- => Removes url column and any index referencing only url
-- => Existing rows are unaffected; row size decreases by one column
-- => After execution: SELECT url FROM attachments fails with "column does not exist"

-- File: 011-drop-legacy-sessions.sql
-- => Drops the legacy sessions table replaced by refresh_tokens in migration 005
-- => Dependency check: no other table has a foreign key referencing sessions
 
-- Drop any indexes on the table first (CASCADE handles this, but explicit is clearer)
DROP INDEX IF EXISTS ix_sessions_user_id;
DROP INDEX IF EXISTS ix_sessions_token;
-- => Explicit index drops document what is being removed; CASCADE would handle them too
 
-- Drop the table itself
DROP TABLE IF EXISTS sessions;
-- => IF EXISTS: safe to run even if table was already dropped or never existed
-- => Removes table definition, all rows, all column constraints, all table-level grants
-- => Does NOT drop functions or triggers referencing this table (rare but possible)
-- => After execution: SELECT * FROM sessions fails with "relation does not exist"
-- => schemaversions records "011-drop-legacy-sessions.sql" as applied

-- File: 001-create-users-uuid.sql
-- => Demonstrates UUID primary key configuration for PostgreSQL
-- => gen_random_uuid() is built-in since PostgreSQL 13; use pgcrypto extension for < 13
 
-- Enable pgcrypto for PostgreSQL < 13 (safe to run on 13+ too; no-op if already installed)
-- CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- => Uncomment if targeting PostgreSQL 12 or older
-- => gen_random_uuid() becomes available after extension load
 
CREATE TABLE users (
    -- => UUID type: 128-bit value stored as 16 bytes; displayed as 32 hex + 4 dashes
    -- => DEFAULT gen_random_uuid(): database generates version-4 UUID on INSERT
    -- => Version-4 UUID is cryptographically random; collision probability negligible
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
 
    username   VARCHAR     NOT NULL,
 
    -- => UNIQUE ensures no two users share the same email
    email      VARCHAR     NOT NULL UNIQUE,
 
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => After INSERT without explicit id: id is e.g. "550e8400-e29b-41d4-a716-446655440000"
-- => Query by id: WHERE id = '550e8400-e29b-41d4-a716-446655440000'::uuid
-- => PostgreSQL casts text literals to UUID automatically with ::uuid cast operator

-- File: 012-add-timestamps-to-expenses.sql
-- => Demonstrates TIMESTAMPTZ columns with server-side defaults
-- => Use NOW() for created_at; use triggers or application logic for updated_at
 
ALTER TABLE expenses
    -- => Add created_at as TIMESTAMPTZ with server-side default
    -- => IF NOT EXISTS: safe to run even if column was added in a prior migration
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 
    -- => updated_at starts equal to created_at; application must update it on writes
    -- => Some teams use a PostgreSQL trigger to auto-update; see intermediate examples
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
 
-- => TIMESTAMPTZ stores the instant in UTC internally regardless of the session timezone
-- => Displays in local time when session timezone is set (e.g. Asia/Jakarta shows +07:00)
-- => TIMESTAMP (no TZ) stores the literal date-time string; dangerous across DST transitions
-- => Always prefer TIMESTAMPTZ for audit columns in server-side applications

-- File: 013-add-status-to-users.sql
-- => Adds a status column using VARCHAR + CHECK constraint (preferred over ENUM type)
-- => CHECK constraint approach: easy to add new values in future migrations
 
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE';
-- => VARCHAR(20) caps status values at 20 characters; prevents runaway string growth
-- => DEFAULT 'ACTIVE' means existing users are immediately active
 
-- Add CHECK constraint to enforce allowed values
ALTER TABLE users
    ADD CONSTRAINT ck_users_status
    CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED'));
-- => Any INSERT or UPDATE with status outside this list fails with error 23514 (check_violation)
-- => Error message: "new row for relation "users" violates check constraint "ck_users_status""
-- => Adding a new value later: ALTER TABLE users DROP CONSTRAINT ck_users_status;
-- =>                            ALTER TABLE users ADD CONSTRAINT ck_users_status CHECK (status IN ('ACTIVE', 'SUSPENDED', 'DELETED', 'PENDING'));
-- => One migration per change; name "ck_users_status" identifies the constraint in errors

-- File: 014-add-check-constraints-to-expenses.sql
-- => Adds business-rule constraints to the expenses table
-- => Constraints enforce domain invariants that application code cannot guarantee alone
 
-- Constraint 1: amount must be positive (no zero or negative expenses)
ALTER TABLE expenses
    ADD CONSTRAINT ck_expenses_amount_positive
    CHECK (amount > 0);
-- => Any INSERT/UPDATE with amount <= 0 fails with 23514 (check_violation)
-- => Existing rows with amount <= 0 will FAIL this migration; clean data first
 
-- Constraint 2: quantity must be positive when provided
ALTER TABLE expenses
    ADD CONSTRAINT ck_expenses_quantity_positive
    CHECK (quantity IS NULL OR quantity > 0);
-- => IS NULL OR: allows NULL (optional field) but rejects zero/negative when set
-- => Tip: check existing data with: SELECT count(*) FROM expenses WHERE quantity IS NOT NULL AND quantity <= 0
 
-- Constraint 3: date must not be in the future (no future-dated expenses)
ALTER TABLE expenses
    ADD CONSTRAINT ck_expenses_date_not_future
    CHECK (date <= CURRENT_DATE);
-- => CURRENT_DATE evaluates at INSERT/UPDATE time; not a static snapshot
-- => Existing rows with future dates FAIL; audit data before running this migration

-- File: 015-add-composite-indexes-to-expenses.sql
-- => Adds composite indexes to optimize common expense query patterns
-- => Index column order matches the most common WHERE clause column order
 
-- Composite index: user_id + date (supports "get expenses for user in date range")
CREATE INDEX ix_expenses_user_id_date
    ON expenses (user_id, date DESC);
-- => Covers: WHERE user_id = $1 AND date BETWEEN $2 AND $3 ORDER BY date DESC
-- => Also covers: WHERE user_id = $1 (uses leading column ix_expenses_user_id_date)
-- => Does NOT cover: WHERE date = $1 alone (date is trailing column; leading column missing)
-- => DESC on date: matches ORDER BY date DESC for newest-first expense listings
 
-- Composite index: user_id + category (supports "get expenses by category for user")
CREATE INDEX ix_expenses_user_id_category
    ON expenses (user_id, category);
-- => Covers: WHERE user_id = $1 AND category = $2
-- => Useful for expense summary reports grouped by category per user
 
-- After these indexes: schemaversions records "015-add-composite-indexes-to-expenses.sql"
-- => Query planner chooses between ix_expenses_user_id_date and ix_expenses_user_id_category
-- => based on query predicates; run EXPLAIN ANALYZE to verify index usage

-- File: 016-create-expense-tags.sql
-- => Creates the expense_tags junction table for many-to-many expense-to-tag relationship
-- => An expense can have many tags; a tag can apply to many expenses
 
-- First create the tags reference table
CREATE TABLE tags (
    id   UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
    -- => name must be unique; two tags with same name would be duplicates
    name VARCHAR NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => Tags are shared across users; expenses link to tags via junction table
 
-- Create the junction table
CREATE TABLE expense_tags (
    -- => Composite primary key: each (expense_id, tag_id) pair is unique
    expense_id UUID NOT NULL REFERENCES expenses (id) ON DELETE CASCADE,
    -- => ON DELETE CASCADE: when expense is deleted, remove all its tag associations
    tag_id     UUID NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    -- => ON DELETE CASCADE: when tag is deleted, remove all expense associations
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 
    -- => Composite PK prevents duplicate (expense_id, tag_id) associations
    PRIMARY KEY (expense_id, tag_id)
);
-- => Query expenses with their tags: JOIN expense_tags ON expenses.id = expense_tags.expense_id
-- => Query tags for a specific expense: WHERE expense_tags.expense_id = $1
-- => After script: schemaversions records "016-create-expense-tags.sql"

-- File: 017-seed-default-roles.sql
-- => Inserts default user roles required for application authorization
-- => ON CONFLICT DO NOTHING: idempotent; safe to run even if roles already exist
 
CREATE TABLE IF NOT EXISTS roles (
    -- => IF NOT EXISTS: extra safety; role may have been created in a prior migration
    id   UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR NOT NULL UNIQUE,
    description VARCHAR NOT NULL DEFAULT ''
);
-- => Creates roles table if it does not exist; no error if it does
 
-- Seed default roles using ON CONFLICT DO NOTHING for idempotency
INSERT INTO roles (name, description) VALUES
    -- => 'ADMIN' role: full access to all resources
    ('ADMIN',     'Full administrative access'),
    -- => 'USER' role: standard user access
    ('USER',      'Standard user access'),
    -- => 'READONLY' role: view-only access
    ('READONLY',  'Read-only access to all resources')
ON CONFLICT (name) DO NOTHING;
-- => ON CONFLICT (name): if a role with this name already exists, skip the INSERT
-- => DO NOTHING: no update, no error; idempotent upsert for seed data
-- => After execution: roles table has at least these three rows
-- => Re-running this migration adds no duplicate rows (safe for retries)

-- File: 018-add-password-reset-token.sql
-- => Adds password_reset_token column to users table
-- => IF NOT EXISTS guards make this script safe to run multiple times
 
-- Add column with IF NOT EXISTS guard
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS password_reset_token VARCHAR(255);
-- => IF NOT EXISTS: no error if column already exists (e.g., added manually during incident)
-- => Without IF NOT EXISTS: "ERROR: column password_reset_token of relation users already exists"
-- => The column is nullable; NULL means no pending reset; non-NULL means reset in progress
 
-- Create index with IF NOT EXISTS guard
CREATE INDEX IF NOT EXISTS ix_users_password_reset_token
    ON users (password_reset_token)
    WHERE password_reset_token IS NOT NULL;
-- => IF NOT EXISTS: safe to re-run; existing index not recreated
-- => Partial index (WHERE IS NOT NULL): only indexes rows with a pending reset
-- => Partial index is smaller and faster for the common case where most rows have NULL
 
-- Add table-level guard for safe table creation
CREATE TABLE IF NOT EXISTS audit_log (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR     NOT NULL,
    operation  VARCHAR     NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- => IF NOT EXISTS: no error if audit_log was already created by another migration

-- File: 019-add-cascade-delete-to-attachments.sql
-- => Modifies attachments.expense_id foreign key to add ON DELETE CASCADE
-- => When an expense is deleted, all its attachments are automatically deleted
 
-- Step 1: Drop the existing foreign key constraint (no IF EXISTS for constraints)
ALTER TABLE attachments
    DROP CONSTRAINT IF EXISTS attachments_expense_id_fkey;
-- => IF EXISTS: safe even if constraint name differs from PostgreSQL's default naming
-- => PostgreSQL default name: <table>_<column>_fkey (e.g., attachments_expense_id_fkey)
-- => If constraint was named explicitly in a prior migration, use that name instead
 
-- Step 2: Re-add the foreign key with ON DELETE CASCADE
ALTER TABLE attachments
    ADD CONSTRAINT attachments_expense_id_fkey
    FOREIGN KEY (expense_id)
    REFERENCES expenses (id)
    ON DELETE CASCADE;
-- => ON DELETE CASCADE: DELETE FROM expenses WHERE id = $1 also deletes all matching attachments
-- => ON DELETE RESTRICT (default): DELETE FROM expenses fails if attachments reference it
-- => ON DELETE SET NULL: sets expense_id to NULL instead of deleting (only for nullable columns)
-- => After this migration: deleting an expense atomically removes its attachments

open DbUp
open System.Reflection
 
// ─── Marker type for assembly reference ─────────────────────────────────────
// Define a Marker type in the application project (not the test project)
// This provides a stable anchor for Assembly.GetAssembly() calls
// module Marker = type Marker = Marker   (define in Program.fs or similar)
 
// ─── Retrieve the assembly containing the embedded SQL scripts ───────────────
let migrationsAssembly =
    // => GetAssembly(Type): returns the assembly where the given type is defined
    // => Use Marker type from the app project; avoids coupling to string assembly names
    Assembly.GetAssembly(typeof<MyApp.Program.Marker>)
// => migrationsAssembly is the DLL that contains "db/migrations/*.sql" as embedded resources
// => Assembly.GetExecutingAssembly() is an alternative but fragile in test contexts
 
// ─── Build upgrade engine with discovered scripts ────────────────────────────
let engine =
    DeployChanges.To
        .PostgresqlDatabase(System.Environment.GetEnvironmentVariable("DATABASE_URL"))
        // => Discovers all .sql embedded resources from migrationsAssembly
        // => Resource names: "MyApp.db.migrations.001-create-users.sql", "002-...", etc.
        // => DbUp uses the full resource name as the script identifier in schemaversions
        .WithScriptsEmbeddedInAssembly(migrationsAssembly)
        .WithConsoleLogger()
        .Build()
// => engine.GetScriptsToExecute(): returns pending script list without running them
// => engine.GetExecutedScripts(): returns already-applied scripts from schemaversions
// => engine.IsUpgradeRequired(): returns bool; useful for health checks

open DbUp
open System.Reflection
 
// ─── Inspect script execution order before running ────────────────────────────
let inspectMigrationOrder (connStr: string) (asm: Assembly) =
    let engine =
        DeployChanges.To
            .PostgresqlDatabase(connStr)
            .WithScriptsEmbeddedInAssembly(asm)
            .WithConsoleLogger()
            .Build()
 
    // GetScriptsToExecute(): returns pending scripts in the order DbUp will execute them
    let pending = engine.GetScriptsToExecute()
    // => Returns IList<SqlScript>; each SqlScript has .Name (resource name) and .Contents (SQL text)
    // => Names are ordered lexicographically: "001-..." before "002-..." before "010-..."
    // => Zero-padded numbers ensure correct order: "009-..." < "010-..." (not "09..." < "1...")
 
    for script in pending do
        // => Prints each script name in execution order; useful for pre-deployment verification
        printfn "Pending: %s" script.Name
    // => Example output:
    // =>   Pending: MyApp.db.migrations.001-create-users.sql
    // =>   Pending: MyApp.db.migrations.002-create-expenses.sql
    // =>   Pending: MyApp.db.migrations.003-create-attachments.sql
 
    // GetExecutedScripts(): returns scripts already in schemaversions
    let applied = engine.GetExecutedScripts()
    // => Returns IList<string> of script names that DbUp will skip (already applied)
    for name in applied do
        printfn "Applied:  %s" name
    // => Empty list on first run; grows with each successful PerformUpgrade()
 
    // IsUpgradeRequired(): fast check without executing anything
    let upgradeNeeded = engine.IsUpgradeRequired()
    // => Returns true when GetScriptsToExecute() is non-empty
    // => Useful for health check endpoints: "database is at latest version"
    printfn "Upgrade required: %b" upgradeNeeded