-- 008-clients

CREATE TABLE clients (
  id                 NUMBER(19) PRIMARY KEY,
  project_id         NUMBER(19)               NOT NULL REFERENCES projects (id),
  company_id         NUMBER(19) REFERENCES companies (id),
  type               VARCHAR2(64 CHAR)        NOT NULL,
  name               VARCHAR2(255 CHAR)       NOT NULL,
  "online"           NUMBER(1)                NOT NULL,

  customer_id        NUMBER(19) REFERENCES crm_customers (id),
  channel_id         NUMBER(19) REFERENCES channels (id),
  telegram_id        NUMBER(19),
  facebook_id        NUMBER(19),
  vk_id              NUMBER(19),

  total_tickets      NUMBER(10)               NOT NULL,
  total_open_tickets NUMBER(10)               NOT NULL,

  created_at         TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_at         TIMESTAMP WITH TIME ZONE NOT NULL,
  seen_at            TIMESTAMP WITH TIME ZONE,
  messaged_at        TIMESTAMP WITH TIME ZONE
);

CREATE SEQUENCE clients_id_seq;

CREATE INDEX clients_channel_id_name_ix
  ON clients (
    CASE WHEN channel_id IS NOT NULL
      THEN channel_id END,
    CASE WHEN channel_id IS NOT NULL
      THEN lower(name) END
  );

CREATE INDEX clients_company_id_name_ix
  ON clients (
    CASE WHEN company_id IS NOT NULL
      THEN company_id END,
    CASE WHEN company_id IS NOT NULL
      THEN lower(name) END
  );

CREATE INDEX clients_online_seen_at_ix
  ON clients (
    CASE WHEN "online" = 1
      THEN seen_at END
  );

CREATE INDEX clients_project_id_name_ix
  ON clients (project_id, lower(name));

CREATE INDEX clients_project_id_seen_ix
  ON clients (project_id, seen_at);

-- clients unique indexes

CREATE UNIQUE INDEX clients_project_customer_uniq
  ON clients (
    CASE WHEN customer_id IS NOT NULL
      THEN project_id END,
    CASE WHEN customer_id IS NOT NULL
      THEN customer_id END
  );

CREATE UNIQUE INDEX clients_project_facebook_uniq
  ON clients (
    CASE WHEN facebook_id IS NOT NULL
      THEN project_id END,
    CASE WHEN facebook_id IS NOT NULL
      THEN facebook_id END
  );

CREATE UNIQUE INDEX clients_project_telegram_uniq
  ON clients (
    CASE WHEN telegram_id IS NOT NULL
      THEN project_id END,
    CASE WHEN telegram_id IS NOT NULL
      THEN telegram_id END
  );

CREATE UNIQUE INDEX clients_project_vk_uniq
  ON clients (
    CASE WHEN vk_id IS NOT NULL
      THEN project_id END,
    CASE WHEN vk_id IS NOT NULL
      THEN vk_id END
  );

-- client-sessions

CREATE TABLE client_sessions (
  id         NUMBER(19) PRIMARY KEY,
  client_id  NUMBER(19)               NOT NULL REFERENCES clients (id),
  token      VARCHAR2(255 CHAR)       NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE client_sessions_id_seq;

CREATE INDEX client_sessions_client_id_ix
  ON client_sessions (client_id);

CREATE UNIQUE INDEX client_sessions_token_uniq
  ON client_sessions (token);
