Transcryption-phone/setup_postgresql.sh

#!/bin/bash
set -e

# --- НАСТРОЙКИ (ПРОВЕРЬТЕ ПЕРЕД ЗАПУСКОМ) ---
DB_HOST="192.168.254.35"
DB_PORT="5432"
DB_NAME="asr_db"
DB_USER="asr_user"
DB_PASSWORD="poluyan77"
ADMIN_PASSWORD="admin123"
# --- КОНЕЦ НАСТРОЕК ---

echo "=== 1. Установка PostgreSQL 16 и pgvector ==="
sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-16 postgresql-16-pgvector

echo "=== 2. Настройка удалённого доступа ==="
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /etc/postgresql/16/main/postgresql.conf
echo "host    $DB_NAME    $DB_USER    $DB_HOST/32    md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
echo "host    $DB_NAME    $DB_USER    192.168.254.0/24    md5" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf

echo "=== 3. Перезапуск PostgreSQL ==="
sudo systemctl restart postgresql

echo "=== 4. Создание базы данных и пользователя ==="
sudo -u postgres psql -c "CREATE DATABASE $DB_NAME;"
sudo -u postgres psql -c "CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;"
sudo -u postgres psql -c "ALTER DATABASE $DB_NAME OWNER TO $DB_USER;"

echo "=== 5. Установка расширения pgvector ==="
sudo -u postgres psql -d $DB_NAME -c "CREATE EXTENSION IF NOT EXISTS vector;"

echo "=== 6. Создание таблиц ==="
sudo -u postgres psql -d $DB_NAME << EOF
-- Пользователи
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'analyst' CHECK (role IN ('admin', 'manager', 'analyst', 'operator')),
    full_name VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Звонки
CREATE TABLE IF NOT EXISTS calls (
    id BIGSERIAL PRIMARY KEY,
    incoming_number VARCHAR(20),
    outgoing_number VARCHAR(20),
    call_date DATE,
    call_time TIME,
    call_timestamp TIMESTAMPTZ,
    audio_filename VARCHAR(255) NOT NULL,
    audio_filepath TEXT NOT NULL,
    audio_duration_sec FLOAT,
    audio_size_bytes BIGINT,
    operator_name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    error_message TEXT,
    tags_manual TEXT[],
    quality_overall SMALLINT CHECK (quality_overall BETWEEN 1 AND 5),
    quality_comment TEXT,
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    tag_version INT DEFAULT 0
);

-- Транскрипции
CREATE TABLE IF NOT EXISTS transcriptions (
    id BIGSERIAL PRIMARY KEY,
    call_id BIGINT NOT NULL REFERENCES calls(id) ON DELETE CASCADE,
    full_text TEXT,
    processed_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(call_id)
);

-- Сегменты (фразы)
CREATE TABLE IF NOT EXISTS transcription_segments (
    id BIGSERIAL PRIMARY KEY,
    transcript_id BIGINT NOT NULL REFERENCES transcriptions(id) ON DELETE CASCADE,
    segment_index INT NOT NULL,
    speaker VARCHAR(20) NOT NULL CHECK (speaker IN ('абонент', 'оператор')),
    start_time_sec FLOAT NOT NULL,
    end_time_sec FLOAT NOT NULL,
    text TEXT NOT NULL,
    UNIQUE(transcript_id, segment_index)
);

-- Авто-теги
CREATE TABLE IF NOT EXISTS auto_tags (
    id BIGSERIAL PRIMARY KEY,
    call_id BIGINT NOT NULL REFERENCES calls(id) ON DELETE CASCADE,
    tag_name VARCHAR(50) NOT NULL,
    confidence FLOAT CHECK (confidence >= 0 AND confidence <= 1),
    start_time_sec FLOAT,
    end_time_sec FLOAT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Оценки качества
CREATE TABLE IF NOT EXISTS quality_assessments (
    id BIGSERIAL PRIMARY KEY,
    call_id BIGINT NOT NULL REFERENCES calls(id) ON DELETE CASCADE,
    assessed_by BIGINT REFERENCES users(id),
    politeness SMALLINT CHECK (politeness BETWEEN 1 AND 5),
    activity SMALLINT CHECK (activity BETWEEN 1 AND 5),
    script_adherence SMALLINT CHECK (script_adherence BETWEEN 1 AND 5),
    result SMALLINT CHECK (result BETWEEN 1 AND 5),
    comment TEXT,
    assessed_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(call_id, assessed_by)
);

-- Версия тегов
CREATE TABLE IF NOT EXISTS tag_version (
    id INT PRIMARY KEY DEFAULT 1,
    version INT NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO tag_version (id, version) VALUES (1, 1) ON CONFLICT (id) DO NOTHING;

-- Правила тегов
CREATE TABLE IF NOT EXISTS tag_rules (
    id SERIAL PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Ключевые слова тегов
CREATE TABLE IF NOT EXISTS tag_keywords (
    id SERIAL PRIMARY KEY,
    tag_id INT REFERENCES tag_rules(id) ON DELETE CASCADE,
    keyword VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Имена операторов
CREATE TABLE IF NOT EXISTS operator_names (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Индексы
CREATE INDEX IF NOT EXISTS idx_segments_text_search ON transcription_segments USING GIN (to_tsvector('russian', text));
CREATE INDEX IF NOT EXISTS idx_calls_tags_manual ON calls USING GIN (tags_manual);
CREATE INDEX IF NOT EXISTS idx_calls_date ON calls (call_date);
CREATE INDEX IF NOT EXISTS idx_calls_numbers ON calls (incoming_number, outgoing_number);
CREATE INDEX IF NOT EXISTS idx_calls_status ON calls (status);
CREATE INDEX IF NOT EXISTS idx_calls_operator ON calls (operator_name);
CREATE INDEX IF NOT EXISTS idx_calls_created_at ON calls (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_auto_tags_call ON auto_tags (call_id);
CREATE INDEX IF NOT EXISTS idx_auto_tags_name ON auto_tags (tag_name);
CREATE INDEX IF NOT EXISTS idx_tag_keywords_keyword ON tag_keywords(keyword);
EOF

echo "=== 7. Добавление администратора ==="
# Хеш пароля admin123
HASH='$2b$12$unV.F94.vMFwT4IGItVIIuJF4VLZfEqJ8WDalJWUAvwAk6Og4g/tq'
sudo -u postgres psql -d $DB_NAME -c "INSERT INTO users (username, password_hash, role, full_name) VALUES ('admin', '$HASH', 'admin', 'Administrator') ON CONFLICT (username) DO NOTHING;"

echo "=== 8. Добавление начальных тегов ==="
sudo -u postgres psql -d $DB_NAME << EOF
INSERT INTO tag_rules (tag_name, description) VALUES
('мат', 'Нецензурная лексика, ругательства'),
('угроза', 'Упоминание суда, прокуратуры, расторжения договора'),
('недовольство', 'Жалобы на качество услуги, негативные эмоции'),
('техподдержка', 'Проблемы с оборудованием, интернетом')
ON CONFLICT (tag_name) DO NOTHING;

INSERT INTO tag_keywords (tag_id, keyword)
SELECT id, keyword FROM tag_rules, unnest(ARRAY['блин', 'чёрт', 'ёпт', 'ёпрст', 'зараза', 'дурак', 'идиот', 'тупой', 'козёл', 'сволочь', 'редиска', 'жопа', 'хрен', 'фиг', 'хер', 'пипец', 'капец', 'звездец', 'ужас', 'кошмар']) AS keyword
WHERE tag_name = 'мат' AND NOT EXISTS (SELECT 1 FROM tag_keywords WHERE tag_id = tag_rules.id AND keyword = tag_keywords.keyword);

INSERT INTO tag_keywords (tag_id, keyword)
SELECT id, keyword FROM tag_rules, unnest(ARRAY['суд', 'прокуратура', 'жалоба', 'напишу', 'пожалуюсь', 'заявление', 'обращение', 'адвокат', 'юрист', 'статья', 'расторгнуть договор', 'разорвать договор', 'сменим провайдера']) AS keyword
WHERE tag_name = 'угроза' AND NOT EXISTS (SELECT 1 FROM tag_keywords WHERE tag_id = tag_rules.id AND keyword = tag_keywords.keyword);

INSERT INTO tag_keywords (tag_id, keyword)
SELECT id, keyword FROM tag_rules, unnest(ARRAY['не работает', 'плохо работает', 'медленно', 'тормозит', 'недоволен', 'разочарован', 'ужасный', 'отвратительно', 'деньги не платим', 'скидку', 'компенсацию']) AS keyword
WHERE tag_name = 'недовольство' AND NOT EXISTS (SELECT 1 FROM tag_keywords WHERE tag_id = tag_rules.id AND keyword = tag_keywords.keyword);

INSERT INTO tag_keywords (tag_id, keyword)
SELECT id, keyword FROM tag_rules, unnest(ARRAY['роутер', 'кабель', 'свет', 'связи нет', 'интернета нет', 'перезагрузил', 'проверил', 'wi-fi', 'ethernet', 'dsl', 'оптоволокно']) AS keyword
WHERE tag_name = 'техподдержка' AND NOT EXISTS (SELECT 1 FROM tag_keywords WHERE tag_id = tag_rules.id AND keyword = tag_keywords.keyword);
EOF

echo "=== 9. Добавление прав для пользователя ==="
sudo -u postgres psql -d $DB_NAME -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $DB_USER;"
sudo -u postgres psql -d $DB_NAME -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO $DB_USER;"
sudo -u postgres psql -d $DB_NAME -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO $DB_USER;"

echo "=== PostgreSQL готов ==="
echo "Хост: $DB_HOST"
echo "Порт: $DB_PORT"
echo "База: $DB_NAME"
echo "Пользователь: $DB_USER"