--- counters


-- channel counters
ALTER TABLE channels
  ADD total_clients INTEGER,
  ADD total_tickets INTEGER,
  ADD total_open_tickets INTEGER;

UPDATE channels
SET
  total_clients      = (
    SELECT count(*)
    FROM clients
    WHERE clients.channel_id = channels.id
  ),
  total_tickets      = (
    SELECT count(*)
    FROM tickets t
      JOIN chats c ON c.id = t.chat_id
    WHERE c.channel_id = channels.id
  ),
  total_open_tickets = (
    SELECT count(*)
    FROM tickets t
      JOIN chats c ON c.id = t.chat_id
    WHERE
      c.channel_id = channels.id
      AND t.open = TRUE
  );

ALTER TABLE channels
  ALTER COLUMN total_clients SET NOT NULL,
  ALTER COLUMN total_tickets SET NOT NULL,
  ALTER COLUMN total_open_tickets SET NOT NULL;

-- project counters
ALTER TABLE projects
  ADD total_tickets INTEGER,
  ADD total_open_tickets INTEGER;


UPDATE projects
SET
  total_tickets      = (
    SELECT count(*)
    FROM tickets t
    WHERE t.project_id = projects.id
  ),
  total_open_tickets = (
    SELECT count(*)
    FROM tickets t
    WHERE
      t.project_id = projects.id
      AND t.open = TRUE
  );

ALTER TABLE projects
  ALTER COLUMN total_tickets SET NOT NULL,
  ALTER COLUMN total_open_tickets SET NOT NULL;

-- client counters
ALTER TABLE clients
  ADD total_tickets INTEGER,
  ADD total_open_tickets INTEGER;

UPDATE clients
SET
  total_tickets      = (
    SELECT count(*)
    FROM tickets t
    WHERE t.client_id = clients.id
  ),
  total_open_tickets = (
    SELECT count(*)
    FROM tickets t
    WHERE
      t.client_id = clients.id
      AND t.open = TRUE
  );

ALTER TABLE clients
  ALTER COLUMN total_tickets SET NOT NULL,
  ALTER COLUMN total_open_tickets SET NOT NULL;
