-- ============================================================================
-- SQL запрос для получения данных контакта из CRM (через n8n)
-- ============================================================================
-- Назначение: Получить актуальные данные контакта из CRM для отображения
--             в форме подтверждения (если данные уже подтверждены)
-- 
-- Использование: В n8n workflow после проверки флага contact_data_confirmed_at
-- ============================================================================

-- ВАЖНО: Этот запрос выполняется через n8n HTTP Request к CRM webservice,
-- а не напрямую в PostgreSQL, т.к. CRM в MySQL

-- Пример запроса к CRM через n8n:
-- POST https://crm.clientright.ru/webservice.php
-- Body: operation=retrieve&sessionName={{sessionName}}&id=12x{{contact_id}}

-- ============================================================================
-- Альтернатива: Хранить кэш данных контакта в PostgreSQL
-- ============================================================================

-- Создаём таблицу для кэширования данных контакта из CRM
CREATE TABLE IF NOT EXISTS clpr_contact_data_cache (
  unified_id VARCHAR NOT NULL PRIMARY KEY,
  contact_id INTEGER,
  firstname VARCHAR,
  lastname VARCHAR,
  middle_name VARCHAR,
  inn VARCHAR,
  birthday DATE,
  birthplace VARCHAR,
  mailingstreet VARCHAR,
  email VARCHAR,
  mobile VARCHAR,
  -- Дополнительные поля из CRM
  data_json JSONB, -- Полные данные из CRM для расширяемости
  synced_at TIMESTAMPTZ DEFAULT NOW(),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  CONSTRAINT fk_unified_id FOREIGN KEY (unified_id) 
    REFERENCES clpr_users(unified_id) ON DELETE CASCADE
);

-- Индекс для быстрого поиска
CREATE INDEX IF NOT EXISTS idx_clpr_contact_data_cache_contact_id 
ON clpr_contact_data_cache(contact_id);

-- Комментарий
COMMENT ON TABLE clpr_contact_data_cache IS 
'Кэш данных контакта из CRM. Обновляется при синхронизации через n8n.';

-- ============================================================================
-- Функция: Получить данные контакта (из кэша или NULL)
-- ============================================================================
CREATE OR REPLACE FUNCTION clpr_get_contact_data(p_unified_id VARCHAR)
RETURNS TABLE(
  contact_id INTEGER,
  firstname VARCHAR,
  lastname VARCHAR,
  middle_name VARCHAR,
  inn VARCHAR,
  birthday DATE,
  birthplace VARCHAR,
  mailingstreet VARCHAR,
  email VARCHAR,
  mobile VARCHAR,
  data_json JSONB
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    c.contact_id,
    c.firstname,
    c.lastname,
    c.middle_name,
    c.inn,
    c.birthday,
    c.birthplace,
    c.mailingstreet,
    c.email,
    c.mobile,
    c.data_json
  FROM clpr_contact_data_cache c
  WHERE c.unified_id = p_unified_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- Функция: Обновить кэш данных контакта
-- ============================================================================
CREATE OR REPLACE FUNCTION clpr_update_contact_data_cache(
  p_unified_id VARCHAR,
  p_contact_id INTEGER,
  p_data JSONB
)
RETURNS VOID AS $$
BEGIN
  INSERT INTO clpr_contact_data_cache (
    unified_id,
    contact_id,
    firstname,
    lastname,
    middle_name,
    inn,
    birthday,
    birthplace,
    mailingstreet,
    email,
    mobile,
    data_json,
    synced_at,
    updated_at
  ) VALUES (
    p_unified_id,
    p_contact_id,
    p_data->>'firstname',
    p_data->>'lastname',
    p_data->>'cf_1157', -- middle_name
    p_data->>'cf_1257', -- inn
    (p_data->>'birthday')::DATE,
    p_data->>'cf_1263', -- birthplace
    p_data->>'mailingstreet',
    p_data->>'email',
    p_data->>'mobile',
    p_data,
    NOW(),
    NOW()
  )
  ON CONFLICT (unified_id) DO UPDATE
  SET 
    contact_id = EXCLUDED.contact_id,
    firstname = EXCLUDED.firstname,
    lastname = EXCLUDED.lastname,
    middle_name = EXCLUDED.middle_name,
    inn = EXCLUDED.inn,
    birthday = EXCLUDED.birthday,
    birthplace = EXCLUDED.birthplace,
    mailingstreet = EXCLUDED.mailingstreet,
    email = EXCLUDED.email,
    mobile = EXCLUDED.mobile,
    data_json = EXCLUDED.data_json,
    synced_at = NOW(),
    updated_at = NOW();
END;
$$ LANGUAGE plpgsql;