common/dal/schema/000010_init.up.sql

CREATE TABLE IF NOT EXISTS tokens (
    AccountID VARCHAR(30) PRIMARY KEY, -- связь с users AccountID неявная посредством join
    RefreshToken TEXT NOT NULL ,
    AccessToken TEXT NOT NULL ,
    AuthCode TEXT NOT NULL , -- код авторизации который получаем при вебхук
    Expiration TIMESTAMP NOT NULL, -- время истечения токенов
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AccountID VARCHAR(30) NOT NULL DEFAULT '', -- id  квизе из токена
    AmoID INT NOT NULL , -- id в амо
    Name VARCHAR(512) NOT NULL DEFAULT '', -- имя в амо
    Email VARCHAR(50) NOT NULL DEFAULT '', -- почта в амо
    Role INT NOT NULL DEFAULT 0, -- роль в амо
    "Group" INT NOT NULL DEFAULT 0, -- вложенная структура так как в амо группы хранятся массивом структур
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Subdomain VARCHAR(50) NOT NULL DEFAULT '',
    AmoUserID INT NOT NULL , -- id пользователя который подключал интеграцию
    Country VARCHAR(50) NOT NULL DEFAULT '' -- страна в амо
);

CREATE TABLE IF NOT EXISTS pipelines (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AmoID INT NOT NULL , --id воронки в амо
    AccountID INT NOT NULL , --id аккаунта в амо связь с таблицей users AmoID неявная посредством join
    Name VARCHAR(512) NOT NULL DEFAULT '', --название воронки в амо
    IsArchive BOOLEAN NOT NULL DEFAULT FALSE, --флаг архивной воронки в амо
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS steps (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AmoID INT NOT NULL, --id шага воронки в амо
    PipelineID INT NOT NULL, --id воронки AmoID pipelines неявная посредством join
    AccountID INT NOT NULL, --id аккаунта в амо связь с таблицей users AmoID неявная посредством join
    Name VARCHAR(512) NOT NULL DEFAULT '', --название воронки в амо
    Color VARCHAR(50) NOT NULL DEFAULT '', --цвет шага в амо*
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DO $$
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'entitytype') THEN
            CREATE TYPE EntityType AS ENUM ('leads', 'contacts', 'companies', 'customers');
    END IF;
END $$;

DO $$
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'fieldtype') THEN
            CREATE TYPE FieldType AS ENUM ('text', 'numeric', 'checkbox', 'select', 'multiselect', 'date', 'url', 'textarea', 'radiobutton', 'streetaddress', 'smart_address', 'birthday', 'legal_entity', 'date_time', 'price', 'category', 'items', 'tracking_data', 'linked_entity', 'chained_list', 'monetary', 'file', 'payer', 'supplier', 'multitext');
    END IF;
END $$;

CREATE TABLE IF NOT EXISTS fields (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AmoID INT NOT NULL, -- айдишник кастомного поля в амо
    Code VARCHAR(255) NOT NULL DEFAULT '', -- кодовое слово в амо
    AccountID INT NOT NULL, -- id аккаунта в амо связь с таблицей users AmoID неявная посредством join
    Name VARCHAR(512) NOT NULL DEFAULT '', -- название воронки в амо
    Entity EntityType NOT NULL, -- тип сущности в амо, для которой это кастомное поле
    Type FieldType NOT NULL, -- тип поля
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tags (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AmoID INT NOT NULL, -- айдишник тега в амо
    AccountID INT NOT NULL, -- id аккаунта в амо связь с таблицей users AmoID неявная посредством join
    Entity EntityType NOT NULL, -- сущность, к которой принадлежит этот тег
    Name VARCHAR(512) NOT NULL DEFAULT '', -- название тега в амо
    Color VARCHAR(50) NOT NULL DEFAULT '', -- цвет тега в амо
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS utms (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AmoFieldID INT NOT NULL DEFAULT 0, -- id field в амо
    QuizID INT NOT NULL, -- id опроса
    AccountID INT NOT NULL, -- id аккаунта в амо AMOID
    Name VARCHAR(512) NOT NULL DEFAULT '', -- название utm
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS rules (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AccountID INT NOT NULL, -- id аккаунта в амо AMOID
    QuizID INT NOT NULL, -- id опроса
    PerformerID INT NOT NULL, -- айдишник ответственного за сделку
    PipelineID INT NOT NULL, --id воронки AmoID pipelines неявная посредством join
    StepID INT NOT NULL , -- id этапа steps AmoID join
    UTMS INTEGER[], -- список UTM для этого опроса id utm
    FieldsRule JSONB NOT NULL DEFAULT '{}', -- вложенная структура
    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX idx_unique_users ON users (amoID);
CREATE UNIQUE INDEX idx_unique_pipeline ON pipelines (amoID, accountID);
CREATE UNIQUE INDEX idx_unique_step ON steps (amoID, accountID, PipelineID);
CREATE UNIQUE INDEX idx_unique_field ON fields (amoID, accountID, entity);
CREATE UNIQUE INDEX idx_unique_tag ON tags (amoID, accountID, entity);
CREATE UNIQUE INDEX idx_unique_rules ON rules (accountID, QuizID);

CREATE TABLE IF NOT EXISTS amoCRMStatuses (
    ID BIGSERIAL UNIQUE NOT NULL PRIMARY KEY,
    AccountID INT NOT NULL, -- id аккаунта в амо
    DealID INT NOT NULL, -- id сделки в которую добавлялось
    AnswerID BIGINT NOT NULL REFERENCES answer(id), -- id true result который вызвал действие
    Status TEXT NOT NULL DEFAULT '', -- запись о ошибке, либо успехе
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);