cents/services/api/src/db.ts

import pg from "pg";
import { createId } from "./security.js";

const { Pool } = pg;

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL ?? "postgres://cents:cents-dev-only@127.0.0.1:55432/cents",
  max: 10,
});

const schema = `
CREATE TABLE IF NOT EXISTS users (
  id text PRIMARY KEY,
  name varchar(40) NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 40),
  name_key varchar(40),
  password_hash text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE users ADD COLUMN IF NOT EXISTS name_key varchar(40);
ALTER TABLE users ADD COLUMN IF NOT EXISTS password_hash text;
UPDATE users SET name_key = lower(trim(name)) WHERE name_key IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS users_name_key_unique ON users (name_key);

CREATE TABLE IF NOT EXISTS ledgers (
  id text PRIMARY KEY,
  name varchar(40) NOT NULL,
  color varchar(16) NOT NULL DEFAULT '#087f72',
  theme varchar(24) NOT NULL DEFAULT 'jade',
  default_currency varchar(3) NOT NULL DEFAULT 'CNY',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  archived_at timestamptz
);

ALTER TABLE ledgers ADD COLUMN IF NOT EXISTS theme varchar(24) NOT NULL DEFAULT 'jade';
UPDATE ledgers SET theme = CASE lower(color)
  WHEN '#3478e5' THEN 'ocean'
  WHEN '#7559d9' THEN 'berry'
  WHEN '#f05d3f' THEN 'coral'
  WHEN '#d84486' THEN 'berry'
  WHEN '#15956d' THEN 'meadow'
  ELSE 'jade'
END WHERE theme = 'jade' AND lower(color) <> '#087f72';
ALTER TABLE ledgers ADD COLUMN IF NOT EXISTS personal_owner_id text REFERENCES users(id) ON DELETE CASCADE;
CREATE UNIQUE INDEX IF NOT EXISTS ledgers_personal_owner_unique
  ON ledgers (personal_owner_id) WHERE personal_owner_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS ledger_members (
  ledger_id text NOT NULL REFERENCES ledgers(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role varchar(8) NOT NULL CHECK (role IN ('owner', 'member')),
  joined_at timestamptz NOT NULL DEFAULT now(),
  removed_at timestamptz,
  PRIMARY KEY (ledger_id, user_id)
);

CREATE TABLE IF NOT EXISTS ledger_invitations (
  id text PRIMARY KEY,
  ledger_id text NOT NULL REFERENCES ledgers(id) ON DELETE CASCADE,
  key_hash char(64) NOT NULL UNIQUE,
  role varchar(8) NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'member')),
  created_by text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  accepted_by text REFERENCES users(id) ON DELETE SET NULL,
  accepted_at timestamptz,
  revoked_at timestamptz
);

CREATE TABLE IF NOT EXISTS app_invitations (
  id text PRIMARY KEY,
  key_hash char(64) NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  accepted_by text REFERENCES users(id) ON DELETE SET NULL,
  accepted_at timestamptz,
  revoked_at timestamptz
);

CREATE INDEX IF NOT EXISTS app_invitations_lookup
  ON app_invitations (key_hash, expires_at);

CREATE INDEX IF NOT EXISTS ledger_invitations_lookup
  ON ledger_invitations (key_hash, expires_at);

CREATE TABLE IF NOT EXISTS sessions (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash char(64) NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  last_seen_at timestamptz NOT NULL DEFAULT now(),
  revoked_at timestamptz
);

CREATE INDEX IF NOT EXISTS sessions_lookup ON sessions (token_hash, expires_at);

CREATE TABLE IF NOT EXISTS entries (
  id text PRIMARY KEY,
  owner_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type varchar(8) NOT NULL CHECK (type IN ('expense', 'income')),
  amount integer NOT NULL CHECK (amount > 0),
  currency varchar(3) NOT NULL,
  base_currency varchar(3) NOT NULL,
  base_amount integer NOT NULL CHECK (base_amount > 0),
  exchange_rate varchar(32) NOT NULL,
  exchange_rate_source varchar(8) NOT NULL CHECK (exchange_rate_source IN ('manual', 'system')),
  category_id varchar(100) NOT NULL,
  note varchar(500) NOT NULL DEFAULT '',
  occurred_at timestamptz NOT NULL,
  created_by text REFERENCES users(id) ON DELETE SET NULL,
  updated_by text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL,
  deleted_at timestamptz,
  version integer NOT NULL CHECK (version > 0),
  server_updated_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE entries ADD COLUMN IF NOT EXISTS owner_id text REFERENCES users(id) ON DELETE CASCADE;
UPDATE entries SET owner_id = COALESCE(created_by, updated_by) WHERE owner_id IS NULL;
ALTER TABLE entries ALTER COLUMN owner_id SET NOT NULL;

CREATE TABLE IF NOT EXISTS entry_ledgers (
  entry_id text NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
  ledger_id text NOT NULL REFERENCES ledgers(id) ON DELETE CASCADE,
  linked_at timestamptz NOT NULL DEFAULT now(),
  unlinked_at timestamptz,
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (entry_id, ledger_id)
);

ALTER TABLE entry_ledgers ADD COLUMN IF NOT EXISTS unlinked_at timestamptz;
ALTER TABLE entry_ledgers ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
     WHERE table_schema = 'public' AND table_name = 'entries' AND column_name = 'ledger_id'
  ) THEN
    EXECUTE 'INSERT INTO entry_ledgers (entry_id, ledger_id)
             SELECT id, ledger_id FROM entries
             ON CONFLICT (entry_id, ledger_id) DO NOTHING';
  END IF;
END $$;

DROP INDEX IF EXISTS entries_ledger_occurred;
ALTER TABLE entries DROP COLUMN IF EXISTS ledger_id;

CREATE INDEX IF NOT EXISTS entries_owner_occurred
  ON entries (owner_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS entry_ledgers_ledger
  ON entry_ledgers (ledger_id, entry_id);

CREATE TABLE IF NOT EXISTS entry_sync_operations (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  processed_at timestamptz NOT NULL DEFAULT now()
);
`;

export async function initializeDatabase() {
  await pool.query(schema);
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const users = await client.query<{ id: string }>(
      `SELECT u.id FROM users u
        WHERE NOT EXISTS (SELECT 1 FROM ledgers l WHERE l.personal_owner_id = u.id)
        FOR UPDATE`,
    );
    for (const user of users.rows) {
      const ledgerId = createId();
      await client.query(
        `INSERT INTO ledgers (id, name, color, default_currency, personal_owner_id)
         VALUES ($1, '个人账本', '#087f72', 'CNY', $2)`,
        [ledgerId, user.id],
      );
      await client.query(
        "INSERT INTO ledger_members (ledger_id, user_id, role) VALUES ($1, $2, 'owner')",
        [ledgerId, user.id],
      );
    }
    await client.query(
      `INSERT INTO entry_ledgers (entry_id, ledger_id)
       SELECT e.id, l.id FROM entries e JOIN ledgers l ON l.personal_owner_id = e.owner_id
       ON CONFLICT (entry_id, ledger_id) DO UPDATE
         SET unlinked_at = NULL, updated_at = now()
       WHERE entry_ledgers.unlinked_at IS NOT NULL`,
    );
    await client.query("COMMIT");
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}