site_aegisone/py_service/sql/v1.5.5_tables_and_seed.sql

-- Migration v1.5.5: New tables + seed data
-- Run: psql -U aegisone -d aegisone -f v1.5.5_tables_and_seed.sql

BEGIN;

-- ===================== New tables =====================

CREATE TABLE IF NOT EXISTS bot_notifications (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50) NOT NULL DEFAULT 'info',
    recipient_type VARCHAR(20) NOT NULL DEFAULT 'operator',
    recipient_max_user_id BIGINT NULL,
    title VARCHAR(255) DEFAULT '',
    body TEXT DEFAULT '',
    related_type VARCHAR(50) DEFAULT '',
    related_id INTEGER NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CHECK (recipient_type IN ('operator','level2','client'))
);

CREATE TABLE IF NOT EXISTS bot_notification_subscriptions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES bot_users(id) ON DELETE CASCADE,
    notify_on_status_change BOOLEAN NOT NULL DEFAULT FALSE,
    notify_on_reply BOOLEAN NOT NULL DEFAULT FALSE,
    notify_on_broadcast BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE(user_id)
);

CREATE TABLE IF NOT EXISTS bot_risk_questions (
    id SERIAL PRIMARY KEY,
    question_key VARCHAR(100) UNIQUE NOT NULL,
    question VARCHAR(500) NOT NULL,
    weight INTEGER NOT NULL DEFAULT 10,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS bot_tickets (
    id SERIAL PRIMARY KEY,
    ticket_number VARCHAR(20) UNIQUE NOT NULL,
    user_id INTEGER REFERENCES bot_users(id) ON DELETE SET NULL,
    object_id INTEGER NULL,
    customer_id INTEGER NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT '',
    priority VARCHAR(20) DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_by VARCHAR(20) DEFAULT 'bot',
    sla_contract_id INTEGER NULL,
    has_attachment BOOLEAN DEFAULT FALSE,
    attachment_type VARCHAR(50) DEFAULT '',
    attachment_path VARCHAR(500) DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CHECK (priority IN ('low','normal','high','urgent')),
    CHECK (status IN ('open','in_progress','resolved','closed'))
);

CREATE TABLE IF NOT EXISTS bot_ticket_messages (
    id SERIAL PRIMARY KEY,
    ticket_id INTEGER REFERENCES bot_tickets(id) ON DELETE CASCADE NOT NULL,
    direction VARCHAR(10) NOT NULL,
    text TEXT DEFAULT '',
    sender VARCHAR(50) DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bot_ticket_statuses (
    id SERIAL PRIMARY KEY,
    ticket_id INTEGER REFERENCES bot_tickets(id) ON DELETE CASCADE NOT NULL,
    old_status VARCHAR(20) DEFAULT '',
    new_status VARCHAR(20) NOT NULL,
    changed_by VARCHAR(50) DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ===================== Add recipient_ids to broadcasts if missing =====================
ALTER TABLE bot_broadcasts ADD COLUMN IF NOT EXISTS recipient_ids JSONB DEFAULT '[]'::jsonb;

-- ===================== Ensure new columns exist on existing tables =====================
ALTER TABLE bot_notifications ADD COLUMN IF NOT EXISTS recipient_max_user_id BIGINT NULL;
ALTER TABLE bot_notifications ADD COLUMN IF NOT EXISTS related_type VARCHAR(50) DEFAULT '';
ALTER TABLE bot_notifications ADD COLUMN IF NOT EXISTS related_id INTEGER NULL;

ALTER TABLE bot_notification_subscriptions ADD COLUMN IF NOT EXISTS notify_on_status_change BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE bot_notification_subscriptions ADD COLUMN IF NOT EXISTS notify_on_reply BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE bot_notification_subscriptions ADD COLUMN IF NOT EXISTS notify_on_broadcast BOOLEAN NOT NULL DEFAULT FALSE;

-- Drop old subscribe_types column if it exists (replaced by boolean columns)
ALTER TABLE bot_notification_subscriptions DROP COLUMN IF EXISTS subscribe_types;

-- ===================== Seed: enable all features =====================
UPDATE bot_features SET enabled = true WHERE enabled = false;

-- ===================== Seed: risk_questions =====================
INSERT INTO bot_risk_questions (question_key, question, weight, sort_order, is_active) VALUES
    ('no_archive', '📋 Отсутствует архив документации?', 25, 1, true),
    ('no_power_backup', '🔋 Нет резервного питания?', 20, 2, true),
    ('no_regulations', '📜 Нет регламентов обслуживания?', 15, 3, true),
    ('system_failures', '⚠️ Были отказы системы за последний год?', 20, 4, true),
    ('no_documentation', '🗂️ Нет схем и паспортов объекта?', 10, 5, true),
    ('outdated_equipment', '🕰️ Оборудование старше 5 лет?', 5, 6, true),
    ('no_monitoring', '📡 Нет удалённого мониторинга?', 5, 7, true)
ON CONFLICT (question_key) DO NOTHING;

-- ===================== Seed: default role permissions for new menu items =====================
INSERT INTO role_menu_permissions (role, menu_key, visible)
SELECT * FROM (VALUES
    ('engineer', 'bot_tickets', true),
    ('engineer', 'bot_notifications', true),
    ('engineer', 'bot_risk_questions', true)
) AS v(role, menu_key, visible)
WHERE NOT EXISTS (
    SELECT 1 FROM role_menu_permissions r
    WHERE r.role = v.role AND r.menu_key = v.menu_key
);

COMMIT;