CREATE TYPE DebitOrCredit AS ENUM ('debit', 'credit');
CREATE TYPE Status AS ENUM ('active', 'locked');
CREATE TYPE Layer AS ENUM ('settled', 'pending', 'encumbrance');

CREATE TABLE cala_accounts (
  id UUID PRIMARY KEY,
  code VARCHAR UNIQUE NOT NULL,
  name VARCHAR NOT NULL,
  external_id VARCHAR UNIQUE,

  normal_balance_type DebitOrCredit NOT NULL, -- For quick lookup when querying balances
  eventually_consistent BOOLEAN NOT NULL, -- For balance locking
  velocity_context_values JSONB NOT NULL, -- Cached for quicker velocity enforcement
  status Status NOT NULL, -- For checking account status
  created_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX idx_cala_accounts_name ON cala_accounts (name);

CREATE TABLE cala_account_events (
  id UUID NOT NULL REFERENCES cala_accounts(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);

CREATE TABLE cala_journals (
  id UUID PRIMARY KEY,
  name VARCHAR NOT NULL,
  code VARCHAR UNIQUE DEFAULT NULL,

  created_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX idx_cala_journals_name ON cala_journals (name);

CREATE TABLE cala_journal_events (
  id UUID NOT NULL REFERENCES cala_journals(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  UNIQUE(id, sequence)
);

CREATE TABLE cala_account_sets (
  id UUID PRIMARY KEY REFERENCES cala_accounts(id),
  journal_id UUID NOT NULL REFERENCES cala_journals(id),
  name VARCHAR NOT NULL,
  external_id VARCHAR UNIQUE,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cala_account_sets_name ON cala_account_sets (name);

CREATE TABLE cala_account_set_events (
  id UUID NOT NULL REFERENCES cala_account_sets(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);

CREATE TABLE cala_account_set_member_accounts (
  account_set_id UUID NOT NULL REFERENCES cala_account_sets(id),
  member_account_id UUID NOT NULL REFERENCES cala_accounts(id),
  transitive BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_set_id, member_account_id)
);

CREATE TABLE cala_account_set_member_account_sets (
  account_set_id UUID NOT NULL REFERENCES cala_account_sets(id),
  member_account_set_id UUID NOT NULL REFERENCES cala_account_sets(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_set_id, member_account_set_id)
);

CREATE TABLE cala_tx_templates (
  id UUID PRIMARY KEY,

  code VARCHAR UNIQUE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE cala_tx_template_events (
  id UUID NOT NULL REFERENCES cala_tx_templates(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);

CREATE TABLE cala_transactions (
  id UUID PRIMARY KEY,

  journal_id UUID NOT NULL,
  tx_template_id UUID NOT NULL,
  external_id VARCHAR DEFAULT NULL UNIQUE,
  correlation_id VARCHAR NOT NULL,
  effective DATE NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cala_transactions_correlation_id ON cala_transactions (correlation_id);

CREATE TABLE cala_transaction_events (
  id UUID NOT NULL REFERENCES cala_transactions(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);


CREATE TABLE cala_entries (
  id UUID PRIMARY KEY,
  journal_id UUID NOT NULL,
  account_id UUID NOT NULL REFERENCES cala_accounts(id),
  transaction_id UUID NOT NULL,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cala_entries_transaction_id ON cala_entries (transaction_id);

CREATE TABLE cala_entry_events (
  id UUID NOT NULL REFERENCES cala_entries(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);

CREATE TABLE cala_current_balances (
  journal_id UUID NOT NULL,
  account_id UUID NOT NULL,
  currency VARCHAR NOT NULL,
  latest_version INT NOT NULL,
  latest_values JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, journal_id, currency)
);

CREATE TABLE cala_balance_history (
  journal_id UUID NOT NULL,
  account_id UUID NOT NULL,
  latest_entry_id UUID NOT NULL,
  currency VARCHAR NOT NULL,
  version INT NOT NULL,
  values JSONB NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, journal_id, currency, version),
  FOREIGN KEY (account_id, journal_id, currency) REFERENCES cala_current_balances(account_id, journal_id, currency)
);
CREATE INDEX idx_cala_balance_history_recorded_at ON cala_balance_history (recorded_at);

CREATE TABLE cala_cumulative_effective_balances (
  journal_id UUID NOT NULL,
  account_id UUID NOT NULL,
  currency VARCHAR NOT NULL,
  effective DATE NOT NULL,
  version INT NOT NULL,
  all_time_version INT NOT NULL,
  latest_entry_id UUID NOT NULL,
  values JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(journal_id, account_id, currency, effective, version)
);

CREATE TABLE cala_velocity_limits (
  id UUID PRIMARY KEY,
  name VARCHAR NOT NULL,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cala_velocity_limits_name ON cala_velocity_limits (name);


CREATE TABLE cala_velocity_limit_events (
  id UUID NOT NULL REFERENCES cala_velocity_limits(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);

CREATE TABLE cala_velocity_controls (
  id UUID PRIMARY KEY,
  name VARCHAR NOT NULL,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cala_velocity_controls_name ON cala_velocity_controls (name);


CREATE TABLE cala_velocity_control_events (
  id UUID NOT NULL REFERENCES cala_velocity_controls(id),
  sequence INT NOT NULL,
  event_type VARCHAR NOT NULL,
  event JSONB NOT NULL,
  context JSONB DEFAULT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(id, sequence)
);

CREATE TABLE cala_velocity_control_limits (
  velocity_control_id UUID NOT NULL REFERENCES cala_velocity_controls(id),
  velocity_limit_id UUID NOT NULL REFERENCES cala_velocity_limits(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(velocity_control_id, velocity_limit_id)
);

CREATE TABLE cala_velocity_account_controls (
  account_id UUID NOT NULL REFERENCES cala_accounts(id),
  velocity_control_id UUID NOT NULL REFERENCES cala_velocity_controls(id),
  values JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, velocity_control_id)
);

CREATE TABLE cala_velocity_current_balances (
  journal_id UUID NOT NULL,
  account_id UUID NOT NULL,
  currency VARCHAR NOT NULL,
  velocity_control_id UUID NOT NULL,
  velocity_limit_id UUID NOT NULL,
  partition_window JSONB NOT NULL,
  latest_version INT NOT NULL,
  latest_values JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, journal_id, currency, velocity_control_id, velocity_limit_id, partition_window)
);

CREATE TABLE cala_velocity_balance_history (
  journal_id UUID NOT NULL,
  account_id UUID NOT NULL,
  currency VARCHAR NOT NULL,
  velocity_control_id UUID NOT NULL,
  velocity_limit_id UUID NOT NULL,
  partition_window JSONB NOT NULL,
  latest_entry_id UUID NOT NULL,
  version INT NOT NULL,
  values JSONB NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(account_id, journal_id, currency, velocity_control_id, velocity_limit_id, partition_window, version),
  FOREIGN KEY (account_id, journal_id, currency, velocity_control_id, velocity_limit_id, partition_window) REFERENCES cala_velocity_current_balances(account_id, journal_id, currency, velocity_control_id, velocity_limit_id, partition_window)
);
