crm-server/migrations/00001_clients_table.sql

-- +goose Up

CREATE TABLE clients (
  id TEXT PRIMARY KEY,
  old_id TEXT,
  contractor BOOLEAN,
  full_name TEXT,
  type TEXT NOT NULL,
  email TEXT,
  legal_address TEXT,
  physical_address TEXT,
  note TEXT,
  reg_date TEXT,
  income_amount DECIMAL(19, 4),
  income_currency CHAR(3)
);

CREATE TABLE client_tags (
    client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    tag TEXT NOT NULL,
    PRIMARY KEY (client_id, tag)
);

CREATE TABLE client_phones (
    client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    phone TEXT NOT NULL,
    PRIMARY KEY (client_id, phone)
);

CREATE TABLE client_metadata (
    client_id TEXT NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (client_id, key)
);

-- Индексы для производительности
CREATE INDEX idx_clients_type ON clients(type);
CREATE INDEX idx_clients_income ON clients(income_amount);
CREATE INDEX idx_clients_currency ON clients(income_currency);

-- +goose Down
DROP TABLE client_metadata;
DROP TABLE client_phones;
DROP TABLE client_tags;
DROP TABLE clients;