-- 0001_core.sql — generic-ядро registry-схемы (без домена)
-- Применяется один раз при инициализации БД.

CREATE SCHEMA IF NOT EXISTS core;

-- ---- Версия структуры (для инвалидации клиентского OPFS-кэша) ----
CREATE TABLE IF NOT EXISTS core.schema_version (
  id           INT PRIMARY KEY DEFAULT 1 CHECK (id = 1),
  version      INT NOT NULL,
  applied_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---- Реестр процедур (savedproc continuation) ----
-- opertype: 0=reader (SELECT), 1=delete, 2=update, 3=insert
CREATE TABLE IF NOT EXISTS core.saved (
  id             BIGSERIAL PRIMARY KEY,
  name           TEXT NOT NULL UNIQUE,
  schema_name    TEXT NOT NULL DEFAULT 'public',
  proc_name      TEXT NOT NULL,
  opertype       SMALLINT NOT NULL CHECK (opertype BETWEEN 0 AND 3),
  trackedtable   JSONB NOT NULL DEFAULT '[]'::jsonb,
  property       JSONB NOT NULL DEFAULT '{}'::jsonb,
  coltemplate_id BIGINT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---- Шаблоны колонок для UI ----
CREATE TABLE IF NOT EXISTS core.coltemplate (
  id          BIGSERIAL PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  definition  JSONB NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE core.saved
  ADD CONSTRAINT saved_coltemplate_fk
  FOREIGN KEY (coltemplate_id) REFERENCES core.coltemplate(id) ON DELETE SET NULL;

-- ---- Реестр синхронизируемых таблиц ----
-- mode: 'cached-full' | 'cached-rolling' | 'cached-query' | 'local-only'
CREATE TABLE IF NOT EXISTS core.synctable (
  id          BIGSERIAL PRIMARY KEY,
  table_name  TEXT NOT NULL UNIQUE,   -- 'schema.table'
  mode        TEXT NOT NULL CHECK (mode IN ('cached-full','cached-rolling','cached-query','local-only')),
  scope       TEXT NOT NULL DEFAULT 'shared' CHECK (scope IN ('shared','user')),
  rolling     JSONB,                  -- {limit, orderBy, orderDir} для cached-rolling
  property    JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---- ACL ----
CREATE TABLE IF NOT EXISTS core.accesstable (
  id          BIGSERIAL PRIMARY KEY,
  role_name   TEXT NOT NULL,
  table_name  TEXT NOT NULL,
  can_read    BOOLEAN NOT NULL DEFAULT false,
  can_write   BOOLEAN NOT NULL DEFAULT false,
  UNIQUE (role_name, table_name)
);

-- ---- Changelog (append-only, один counter на домен) ----
CREATE TABLE IF NOT EXISTS core.changelog (
  id          BIGSERIAL PRIMARY KEY,
  ts          TIMESTAMPTZ NOT NULL DEFAULT now(),
  table_name  TEXT NOT NULL,
  op          CHAR(1) NOT NULL CHECK (op IN ('I','U','D')),
  row_id      TEXT NOT NULL,         -- TEXT для совместимости с UUID/BIGINT PK
  row         JSONB,                 -- payload (NULL для DELETE опционально)
  actor       TEXT,                  -- user_id/session — заполняет триггер из current_setting
  txid        BIGINT NOT NULL DEFAULT txid_current()
);

CREATE INDEX IF NOT EXISTS changelog_table_id_idx ON core.changelog (table_name, id);
CREATE INDEX IF NOT EXISTS changelog_id_desc_idx  ON core.changelog (id DESC);

-- ---- Bootstrap версии ----
INSERT INTO core.schema_version (id, version)
VALUES (1, 1)
ON CONFLICT (id) DO NOTHING;
