--- data-chats

-- tickets
SET IDENTITY_INSERT tickets ON;
INSERT INTO tickets (
  id,
  project_id,
  client_id,

  title,
  [open],
  chat_id,
  channel_id,

  total_messages,
  total_members,

  created_at,
  updated_at,
  opened_at,
  closed_at
) SELECT
    s.id,
    c.project_id,
    c.client_id,

    clients.name,
    s."open",
    s.chat_id,
    c.channel_id,

    0,
    0,

    s.created_at,
    s.created_at,
    s.opened_at,
    s.closed_at
  FROM chat_sessions s
    JOIN chats c ON c.id = s.chat_id
    JOIN clients ON clients.id = c.client_id;
SET IDENTITY_INSERT tickets OFF;

-- ticket_members
SET IDENTITY_INSERT ticket_members ON;
INSERT INTO ticket_members (
  id,
  ticket_id,
  user_id,
  deleted,
  created_at,
  updated_at,
  assigned_at,
  unassigned_at,
  deleted_at
) SELECT
    m.id,
    c.session_id,
    m.user_id,
    CASE m.present
    WHEN 0
      THEN 1
    ELSE 0
    END,
    m.created_at,
    m.created_at,
    m.assigned_at,
    m.removed_at,
    m.removed_at
  FROM chat_members m
    JOIN chats c ON c.id = m.chat_id
  WHERE c.session_id IS NOT NULL;
SET IDENTITY_INSERT ticket_members OFF;

UPDATE tickets
SET total_members = (
  SELECT count(*)
  FROM ticket_members m
  WHERE
    m.ticket_id = tickets.id
    AND m.deleted = 0
);

-- ticket_messages
SET IDENTITY_INSERT ticket_messages ON;
INSERT INTO ticket_messages (
  id,
  uid,
  ticket_id,
  local_id,

  author,
  client_id,
  user_id,

  [type],
  [text],
  file_id,
  action_id,
  autoreply_id,

  received,
  [read],

  created_at,
  updated_at,
  received_at,
  read_at
) SELECT
    m.id,
    m.uid,
    m.session_id,
    m.local_id,

    m.author,
    m.client_id,
    m.user_id,

    m.payload,
    m.text,
    m.file_id,
    m.notice_id,
    m.autoreply_id,

    m.received,
    m.[read],

    m.created_at,
    m.created_at,
    m.received_at,
    m.read_at
  FROM chat_messages m
  WHERE
    m.session_id IS NOT NULL
    AND m.notice_id IS NULL;
SET IDENTITY_INSERT ticket_messages OFF;

UPDATE tickets
SET total_messages = (
  SELECT count(*)
  FROM ticket_messages m
  WHERE m.ticket_id = tickets.id
);

UPDATE tickets
SET message_id = (
  SELECT c.message_id
  FROM chats c
  WHERE c.id = tickets.chat_id
);

-- chats ticket_id
UPDATE chats
SET ticket_id = session_id
WHERE session_id IS NOT NULL;

-- ticket times
UPDATE tickets
SET total_time = datediff(SECOND, created_at, closed_at)
WHERE closed_at IS NOT NULL;
