-- Миграция 001: Инфраструктура для фазы лидов и звонков.
-- Дата: 04.06.2026
-- Описание: Расширение существующих таблиц + создание новых.

-- ═══════════════════════════════════════════════════════════════════════════════
-- 1. Новые таблицы (создаём ПЕРВЫМИ, чтобы FK ссылались)
-- ═══════════════════════════════════════════════════════════════════════════════

-- 1.1 Таблица лидов
CREATE TABLE IF NOT EXISTS leads (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    source VARCHAR(20) NOT NULL DEFAULT 'manual',
    company_name VARCHAR(255) DEFAULT '',
    contact_name VARCHAR(255) DEFAULT '',
    contact_phone VARCHAR(50) DEFAULT '',
    contact_email VARCHAR(255) DEFAULT '',
    inn VARCHAR(12) DEFAULT '',
    title VARCHAR(500) DEFAULT '',
    service_type VARCHAR(255) DEFAULT '',
    description TEXT DEFAULT '',
    urgency VARCHAR(10) DEFAULT 'medium',
    status VARCHAR(30) DEFAULT 'new',
    assigned_to INTEGER REFERENCES users(id) ON DELETE SET NULL,
    bot_ticket_id BIGINT,
    deal_amount NUMERIC(12, 2) DEFAULT 0,
    expected_close_date DATE,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_leads_customer ON leads(customer_id);
CREATE INDEX IF NOT EXISTS idx_leads_status ON leads(status);
CREATE INDEX IF NOT EXISTS idx_leads_source ON leads(source);
CREATE INDEX IF NOT EXISTS idx_leads_assigned ON leads(assigned_to);
CREATE INDEX IF NOT EXISTS idx_leads_created ON leads(created_at);

-- 1.2 Таблица звонков (CDR из Yeastar S20)
CREATE TABLE IF NOT EXISTS call_records (
    id SERIAL PRIMARY KEY,
    cdr_uniqueid VARCHAR(32) UNIQUE,
    call_datetime TIMESTAMP NOT NULL,
    caller_number VARCHAR(80) DEFAULT '',
    callee_number VARCHAR(80) DEFAULT '',
    trunk VARCHAR(80) DEFAULT '',
    duration INTEGER DEFAULT 0,
    billable_seconds INTEGER DEFAULT 0,
    disposition VARCHAR(30) DEFAULT '',
    call_type VARCHAR(20) DEFAULT '',
    queue_name VARCHAR(30) DEFAULT '',
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    lead_id INTEGER REFERENCES leads(id) ON DELETE SET NULL,
    answered_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    answered_by_name VARCHAR(100) DEFAULT '',
    has_recording BOOLEAN DEFAULT FALSE,
    recording_path VARCHAR(500) DEFAULT '',
    recording_filename VARCHAR(200) DEFAULT '',
    notification_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_call_records_customer ON call_records(customer_id);
CREATE INDEX IF NOT EXISTS idx_call_records_datetime ON call_records(call_datetime);
CREATE INDEX IF NOT EXISTS idx_call_records_caller ON call_records(caller_number);
CREATE INDEX IF NOT EXISTS idx_call_records_disposition ON call_records(disposition);
CREATE INDEX IF NOT EXISTS idx_call_records_uniqueid ON call_records(cdr_uniqueid);

-- 1.3 Таблица уведомлений
CREATE TABLE IF NOT EXISTS client_notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    lead_id INTEGER REFERENCES leads(id) ON DELETE SET NULL,
    call_record_id INTEGER REFERENCES call_records(id) ON DELETE SET NULL,
    type VARCHAR(30) NOT NULL,
    title VARCHAR(500) NOT NULL,
    message TEXT DEFAULT '',
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_notifications_user ON client_notifications(user_id, is_read);
CREATE INDEX IF NOT EXISTS idx_notifications_unread ON client_notifications(is_read, created_at);

-- 1.4 Таблица настроек АТС (всегда одна запись)
CREATE TABLE IF NOT EXISTS pbx_settings (
    id SERIAL PRIMARY KEY,
    mysql_host VARCHAR(255) DEFAULT '192.168.1.150',
    mysql_port INTEGER DEFAULT 3306,
    mysql_user VARCHAR(100) DEFAULT '',
    mysql_password VARCHAR(255) DEFAULT '',
    mysql_database VARCHAR(100) DEFAULT 'cdr',
    ami_host VARCHAR(255) DEFAULT '192.168.1.150',
    ami_port INTEGER DEFAULT 5038,
    ami_user VARCHAR(100) DEFAULT '',
    ami_password VARCHAR(255) DEFAULT '',
    ftp_host VARCHAR(255) DEFAULT '192.168.1.150',
    ftp_port INTEGER DEFAULT 21,
    ftp_user VARCHAR(100) DEFAULT '',
    ftp_password VARCHAR(255) DEFAULT '',
    web_host VARCHAR(255) DEFAULT '192.168.1.150',
    web_port INTEGER DEFAULT 8088,
    web_user VARCHAR(100) DEFAULT '',
    web_password VARCHAR(255) DEFAULT '',
    serial_number VARCHAR(50) DEFAULT '',
    actual_serial VARCHAR(50) DEFAULT '',
    mysql_status VARCHAR(20) DEFAULT 'unknown',
    ami_status VARCHAR(20) DEFAULT 'unknown',
    ftp_status VARCHAR(20) DEFAULT 'unknown',
    serial_match BOOLEAN DEFAULT FALSE,
    last_sync_at TIMESTAMP,
    sync_interval INTEGER DEFAULT 5,
    updated_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO pbx_settings (id) VALUES (1) ON CONFLICT (id) DO NOTHING;

-- ═══════════════════════════════════════════════════════════════════════════════
-- 2. Изменения существующих таблиц
-- ═══════════════════════════════════════════════════════════════════════════════

-- 2.1 Users: внутренний номер АТС
ALTER TABLE users ADD COLUMN IF NOT EXISTS extension VARCHAR(10) DEFAULT '';

-- 2.2 Customers: поля CRM + расширенный статус
ALTER TABLE customers ADD COLUMN IF NOT EXISTS lead_source VARCHAR(20) DEFAULT '';
ALTER TABLE customers ADD COLUMN IF NOT EXISTS last_call_at TIMESTAMP;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS total_calls INTEGER DEFAULT 0;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS total_leads INTEGER DEFAULT 0;
ALTER TABLE customers DROP CONSTRAINT IF EXISTS ck_customers_status;
ALTER TABLE customers ADD CONSTRAINT ck_customers_status
    CHECK (status IN ('active','inactive','prospect','draft','merged'));

-- 2.3 Tasks: связь с лидом
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS lead_id INTEGER REFERENCES leads(id) ON DELETE SET NULL;

-- 2.4 AuditLog: расширенное аудирование
ALTER TABLE audit_log ADD COLUMN IF NOT EXISTS entity_type VARCHAR(50) DEFAULT '';
ALTER TABLE audit_log ADD COLUMN IF NOT EXISTS entity_id INTEGER DEFAULT 0;
ALTER TABLE audit_log ADD COLUMN IF NOT EXISTS field VARCHAR(100) DEFAULT '';
ALTER TABLE audit_log ADD COLUMN IF NOT EXISTS old_value TEXT DEFAULT '';
ALTER TABLE audit_log ADD COLUMN IF NOT EXISTS new_value TEXT DEFAULT '';
CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id);