IaC / SwiftFlow

Artifact fc472950887b851844664882de45e3786f5dc5ef698cf2a5320b6ab2ead709e9:

• File backend/src/schema.gleam — part of check-in [bee9efb316] at 2026-10-23 06:37:04 on branch trunk — 同一仓库内移库，并留下波次已占用的数量

  - 把一个批次从源库位转到启用的目标库位，库存立刻变更 - 未完成波次占用的数量不能移走，撤销波次后才能继续移 - 移库记录留在本地，不写回来肯 (user: pluto size: 16556)

import clock
import database.{type Conn, type Database, type DbError, SqliteConn, TextParam}
import gleam/dynamic/decode
import gleam/list
import gleam/result
import gleam/string
import ql361api/json_codec
import rbac

const statements = [
  "CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS sync_cursors (
    entity TEXT PRIMARY KEY,
    cursor_value TEXT NOT NULL,
    updated_at TEXT NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS sync_runs (
    id TEXT PRIMARY KEY,
    entity TEXT NOT NULL,
    trigger_kind TEXT NOT NULL,
    started_at TEXT NOT NULL,
    finished_at TEXT NOT NULL,
    status TEXT NOT NULL,
    fetched_count INTEGER NOT NULL,
    upserted_count INTEGER NOT NULL,
    error_message TEXT NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS warehouses (
    ql_id BIGINT PRIMARY KEY,
    code TEXT NOT NULL,
    name TEXT NOT NULL,
    easycode TEXT NOT NULL,
    operational INTEGER NOT NULL DEFAULT 0,
    synced_at TEXT NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS products (
    ql_id BIGINT PRIMARY KEY,
    code TEXT NOT NULL,
    name TEXT NOT NULL,
    barcode TEXT NOT NULL,
    spec TEXT NOT NULL,
    unit TEXT NOT NULL,
    shelf_life TEXT NOT NULL,
    shelf_life_days INTEGER NOT NULL,
    position_code TEXT NOT NULL,
    disabled INTEGER NOT NULL,
    is_category INTEGER NOT NULL,
    source_modified_at TEXT NOT NULL,
    source_created_at TEXT NOT NULL,
    synced_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_products_code ON products (code)",
  "CREATE INDEX IF NOT EXISTS idx_products_barcode ON products (barcode)",
  "CREATE TABLE IF NOT EXISTS outbound_orders (
    bill_id BIGINT PRIMARY KEY,
    bill_code TEXT NOT NULL,
    customer_name TEXT NOT NULL,
    contact_person TEXT NOT NULL,
    phone TEXT NOT NULL,
    warehouse_name TEXT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    bill_date TEXT NOT NULL,
    status INTEGER NOT NULL,
    status_name TEXT NOT NULL,
    total_qty REAL NOT NULL,
    total_money REAL NOT NULL,
    remark TEXT NOT NULL,
    detail_synced_at TEXT NOT NULL,
    synced_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_outbound_orders_status ON outbound_orders (status)",
  "CREATE TABLE IF NOT EXISTS outbound_order_items (
    bill_id BIGINT NOT NULL,
    line_no INTEGER NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    goods_name TEXT NOT NULL,
    barcode TEXT NOT NULL,
    spec TEXT NOT NULL,
    package_qty REAL NOT NULL,
    package_unit TEXT NOT NULL,
    base_qty REAL NOT NULL,
    base_unit TEXT NOT NULL,
    price REAL NOT NULL,
    total_money REAL NOT NULL,
    position_code TEXT NOT NULL,
    area_code TEXT NOT NULL,
    PRIMARY KEY (bill_id, line_no)
  )",
  "CREATE INDEX IF NOT EXISTS idx_outbound_order_items_bill ON outbound_order_items (bill_id)",
  "CREATE TABLE IF NOT EXISTS inbound_orders (
    bill_id BIGINT PRIMARY KEY,
    bill_code TEXT NOT NULL,
    supplier_name TEXT NOT NULL,
    contact_person TEXT NOT NULL,
    phone TEXT NOT NULL,
    warehouse_name TEXT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    bill_date TEXT NOT NULL,
    status INTEGER NOT NULL,
    status_name TEXT NOT NULL,
    total_qty REAL NOT NULL,
    total_money REAL NOT NULL,
    remark TEXT NOT NULL,
    detail_synced_at TEXT NOT NULL,
    synced_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_inbound_orders_status ON inbound_orders (status)",
  "CREATE TABLE IF NOT EXISTS inbound_order_items (
    bill_id BIGINT NOT NULL,
    line_no INTEGER NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    goods_name TEXT NOT NULL,
    barcode TEXT NOT NULL,
    spec TEXT NOT NULL,
    package_qty REAL NOT NULL,
    package_unit TEXT NOT NULL,
    base_qty REAL NOT NULL,
    base_unit TEXT NOT NULL,
    price REAL NOT NULL,
    total_money REAL NOT NULL,
    position_code TEXT NOT NULL,
    area_code TEXT NOT NULL,
    PRIMARY KEY (bill_id, line_no)
  )",
  "CREATE INDEX IF NOT EXISTS idx_inbound_order_items_bill ON inbound_order_items (bill_id)",
  "CREATE TABLE IF NOT EXISTS locations (
    warehouse_id BIGINT NOT NULL,
    area TEXT NOT NULL,
    aisle TEXT NOT NULL,
    level_code TEXT NOT NULL,
    bin_code TEXT NOT NULL,
    code TEXT NOT NULL,
    barcode TEXT NOT NULL,
    enabled INTEGER NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (warehouse_id, code)
  )",
  "CREATE UNIQUE INDEX IF NOT EXISTS idx_locations_barcode ON locations (barcode)",
  "CREATE TABLE IF NOT EXISTS inbound_receipts (
    id TEXT PRIMARY KEY,
    bill_id BIGINT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    created_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_inbound_receipts_bill ON inbound_receipts (bill_id)",
  "CREATE TABLE IF NOT EXISTS inbound_receipt_lines (
    receipt_id TEXT NOT NULL,
    line_no INTEGER NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    goods_name TEXT NOT NULL,
    barcode TEXT NOT NULL,
    received_qty REAL NOT NULL,
    batch_no TEXT NOT NULL,
    produced_on TEXT NOT NULL,
    expires_on TEXT NOT NULL,
    location_code TEXT NOT NULL,
    PRIMARY KEY (receipt_id, line_no)
  )",
  "CREATE INDEX IF NOT EXISTS idx_inbound_receipt_lines_bill ON inbound_receipt_lines (bill_detail_id)",
  "CREATE TABLE IF NOT EXISTS inventory_batches (
    warehouse_id BIGINT NOT NULL,
    location_code TEXT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    batch_no TEXT NOT NULL,
    produced_on TEXT NOT NULL,
    expires_on TEXT NOT NULL,
    qty REAL NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (
      warehouse_id, location_code, sku_id, batch_no, produced_on, expires_on
    )
  )",
  "CREATE TABLE IF NOT EXISTS outbound_shipments (
    id TEXT PRIMARY KEY,
    bill_id BIGINT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    created_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_outbound_shipments_bill ON outbound_shipments (bill_id)",
  "CREATE TABLE IF NOT EXISTS outbound_shipment_lines (
    shipment_id TEXT NOT NULL,
    line_no INTEGER NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    goods_name TEXT NOT NULL,
    batch_no TEXT NOT NULL,
    produced_on TEXT NOT NULL,
    expires_on TEXT NOT NULL,
    location_code TEXT NOT NULL,
    qty REAL NOT NULL,
    PRIMARY KEY (shipment_id, line_no)
  )",
  "CREATE INDEX IF NOT EXISTS idx_outbound_shipment_lines_detail ON outbound_shipment_lines (bill_detail_id)",
  "CREATE TABLE IF NOT EXISTS inbound_batch_codes (
    bill_id BIGINT NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    batch_code TEXT NOT NULL,
    created_at TEXT NOT NULL,
    PRIMARY KEY (bill_id, bill_detail_id)
  )",
  "CREATE INDEX IF NOT EXISTS idx_inbound_batch_codes_code ON inbound_batch_codes (batch_code)",
  "CREATE TABLE IF NOT EXISTS putaways (
    id TEXT PRIMARY KEY,
    batch_code TEXT NOT NULL,
    bill_id BIGINT NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    goods_name TEXT NOT NULL,
    location_code TEXT NOT NULL,
    produced_on TEXT NOT NULL,
    expires_on TEXT NOT NULL,
    qty REAL NOT NULL,
    created_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_putaways_batch ON putaways (batch_code)",
  "CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    enabled INTEGER NOT NULL,
    failed_attempts INTEGER NOT NULL,
    locked_until TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS roles (
    code TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    terminals TEXT NOT NULL,
    builtin INTEGER NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS permissions (
    code TEXT PRIMARY KEY,
    name TEXT NOT NULL
  )",
  "CREATE TABLE IF NOT EXISTS role_permissions (
    role_code TEXT NOT NULL,
    permission_code TEXT NOT NULL,
    PRIMARY KEY (role_code, permission_code)
  )",
  "CREATE TABLE IF NOT EXISTS user_roles (
    user_id TEXT NOT NULL,
    role_code TEXT NOT NULL,
    PRIMARY KEY (user_id, role_code)
  )",
  "CREATE TABLE IF NOT EXISTS user_warehouses (
    user_id TEXT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, warehouse_id)
  )",
  "CREATE TABLE IF NOT EXISTS user_sessions (
    token_hash TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    terminal TEXT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    created_at TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    last_seen_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions (user_id)",
  "CREATE TABLE IF NOT EXISTS audit_logs (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    username TEXT NOT NULL,
    action TEXT NOT NULL,
    target TEXT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    detail TEXT NOT NULL,
    created_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_audit_logs_created ON audit_logs (created_at)",
  "CREATE TABLE IF NOT EXISTS waves (
    id TEXT PRIMARY KEY,
    warehouse_id BIGINT NOT NULL,
    status TEXT NOT NULL,
    created_by TEXT NOT NULL,
    created_at TEXT NOT NULL,
    cancelled_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_waves_warehouse ON waves (warehouse_id, status, created_at)",
  "CREATE TABLE IF NOT EXISTS wave_orders (
    wave_id TEXT NOT NULL,
    bill_id BIGINT NOT NULL,
    bill_code TEXT NOT NULL,
    customer_name TEXT NOT NULL,
    PRIMARY KEY (wave_id, bill_id)
  )",
  "CREATE INDEX IF NOT EXISTS idx_wave_orders_bill ON wave_orders (bill_id)",
  "CREATE TABLE IF NOT EXISTS wave_picks (
    wave_id TEXT NOT NULL,
    line_no INTEGER NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    goods_name TEXT NOT NULL,
    barcode TEXT NOT NULL,
    spec TEXT NOT NULL,
    base_unit TEXT NOT NULL,
    location_code TEXT NOT NULL,
    batch_no TEXT NOT NULL,
    produced_on TEXT NOT NULL,
    expires_on TEXT NOT NULL,
    qty REAL NOT NULL,
    short_qty REAL NOT NULL,
    PRIMARY KEY (wave_id, line_no)
  )",
  "CREATE TABLE IF NOT EXISTS wave_allocations (
    wave_id TEXT NOT NULL,
    line_no INTEGER NOT NULL,
    bill_id BIGINT NOT NULL,
    bill_code TEXT NOT NULL,
    bill_detail_id BIGINT NOT NULL,
    qty REAL NOT NULL,
    PRIMARY KEY (wave_id, line_no, bill_id, bill_detail_id)
  )",
  "CREATE TABLE IF NOT EXISTS stock_moves (
    id TEXT PRIMARY KEY,
    warehouse_id BIGINT NOT NULL,
    from_location TEXT NOT NULL,
    to_location TEXT NOT NULL,
    sku_id BIGINT NOT NULL,
    goods_code TEXT NOT NULL,
    batch_no TEXT NOT NULL,
    produced_on TEXT NOT NULL,
    expires_on TEXT NOT NULL,
    qty REAL NOT NULL,
    created_by TEXT NOT NULL,
    created_at TEXT NOT NULL
  )",
  "CREATE INDEX IF NOT EXISTS idx_stock_moves_warehouse ON stock_moves (warehouse_id, created_at)",
]

pub fn migrate(db: Database) -> Result(Nil, DbError) {
  database.transaction(db, fn(conn) {
    use _ <- result.try(
      list.try_fold(statements, Nil, fn(_, statement) {
        database.exec_on(conn, statement, [])
      }),
    )
    use _ <- result.try(ensure_operational_column(conn))
    use _ <- result.try(ensure_bill_type_column(conn))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('001_init', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('002_inbound_orders', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('003_locations', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('004_receipts', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('005_shipments', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('006_purchase_receipts', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('007_putaways', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(sync_builtin_roles(conn))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('008_rbac', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('009_waves', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    ))
    database.exec_on(
      conn,
      "INSERT INTO schema_migrations (version, applied_at)
       VALUES ('010_moves', ?)
       ON CONFLICT (version) DO NOTHING",
      [TextParam(clock.now_local())],
    )
  })
}

/// 预设角色和权限点以代码为准，每次启动覆盖一次。
fn sync_builtin_roles(conn: Conn) -> Result(Nil, DbError) {
  use _ <- result.try(
    list.try_fold(rbac.permissions(), Nil, fn(_, permission) {
      database.exec_on(
        conn,
        "INSERT INTO permissions (code, name) VALUES (?, ?)
         ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name",
        [TextParam(permission.code), TextParam(permission.name)],
      )
    }),
  )
  list.try_fold(rbac.roles(), Nil, fn(_, role) {
    use _ <- result.try(database.exec_on(
      conn,
      "INSERT INTO roles (code, name, terminals, builtin) VALUES (?, ?, ?, 1)
       ON CONFLICT (code) DO UPDATE SET
         name = EXCLUDED.name,
         terminals = EXCLUDED.terminals,
         builtin = 1",
      [
        TextParam(role.code),
        TextParam(role.name),
        TextParam(string.join(role.terminals, ",")),
      ],
    ))
    use _ <- result.try(database.exec_on(
      conn,
      "DELETE FROM role_permissions WHERE role_code = ?",
      [TextParam(role.code)],
    ))
    list.try_fold(role.permissions, Nil, fn(_, code) {
      database.exec_on(
        conn,
        "INSERT INTO role_permissions (role_code, permission_code) VALUES (?, ?)",
        [TextParam(role.code), TextParam(code)],
      )
    })
  })
}

fn ensure_operational_column(conn: Conn) -> Result(Nil, DbError) {
  let probe = case conn {
    SqliteConn(_) ->
      "SELECT COUNT(*) FROM pragma_table_info('warehouses') WHERE name = 'operational'"
    _ ->
      "SELECT COUNT(*) FROM information_schema.columns
       WHERE table_name = 'warehouses' AND column_name = 'operational'"
  }
  use rows <- result.try(database.query_on(conn, probe, [], {
    use count <- decode.field(0, json_codec.int())
    decode.success(count)
  }))
  case rows {
    [0] | [] ->
      database.exec_on(
        conn,
        "ALTER TABLE warehouses ADD COLUMN operational INTEGER NOT NULL DEFAULT 0",
        [],
      )
    _ -> Ok(Nil)
  }
}

fn ensure_bill_type_column(conn: Conn) -> Result(Nil, DbError) {
  let probe = case conn {
    SqliteConn(_) ->
      "SELECT COUNT(*) FROM pragma_table_info('inbound_orders') WHERE name = 'bill_type'"
    _ ->
      "SELECT COUNT(*) FROM information_schema.columns
       WHERE table_name = 'inbound_orders' AND column_name = 'bill_type'"
  }
  use rows <- result.try(database.query_on(conn, probe, [], {
    use count <- decode.field(0, json_codec.int())
    decode.success(count)
  }))
  case rows {
    [0] | [] ->
      database.exec_on(
        conn,
        "ALTER TABLE inbound_orders ADD COLUMN bill_type INTEGER NOT NULL DEFAULT 504",
        [],
      )
    _ -> Ok(Nil)
  }
}