CREATE TABLE asksuite_leads (
  -- chave composta para histórico
  fetch_date                        DATE                 NOT NULL,

  -- chaves do lead
  conversation_id                   VARCHAR(255)         NOT NULL,
  group_id                          VARCHAR(255),
  lead_id                           BIGINT               NOT NULL,

  -- timestamps
  updated_at                        DATETIME             NOT NULL,  -- converta ms→DATETIME no ETL
  date_formatted                    DATETIME,                        -- parse de "DD/MM/YYYY HH:mm"
  last_reservation_date             DATETIME,                        -- item["lastReservationDate"]
  last_reservation_date_formatted   VARCHAR(50),                     -- original em texto

  -- contato / atendimento
  platform                          VARCHAR(100),
  source                            VARCHAR(100),
  request_price                     BOOLEAN,
  link_quotation_opened             BOOLEAN,
  human_request                     BOOLEAN,
  attendant                         VARCHAR(255),
  attendant_email                   VARCHAR(255),
  email                             VARCHAR(255),
  name                              VARCHAR(255),
  phone                             VARCHAR(50),

  -- localidade
  custom                            TEXT,
  city                              VARCHAR(255),
  state                             VARCHAR(255),
  country                           VARCHAR(255),

  -- estadia / hóspedes
  arrival_date                      DATE,
  departure_date                    DATE,
  adults                            INT,
  children                          INT,

  -- empresa / campanha
  company_id                        VARCHAR(255),
  company_name                      VARCHAR(255),
  language                          VARCHAR(50),
  utm_source                        VARCHAR(255),
  utm_medium                        VARCHAR(255),
  utm_campaign                      VARCHAR(255),
  page                              VARCHAR(255),

  -- links e formatos
  link_to_live_chat                 TEXT,

  -- histórico de reserva
  last_reservation_currency         VARCHAR(50),
  last_reservation_value            DECIMAL(18,2),
  last_reservation_code             VARCHAR(255),
  last_reservation_salesman         VARCHAR(255),

  -- orçamentos
  min_total_value_price_quote       DECIMAL(18,2),
  max_total_value_price_quote       DECIMAL(18,2),
  currency_price_quote              VARCHAR(10),
  total_reservation                 DECIMAL(18,2),

  -- flags de estado
  finished                          BOOLEAN,
  group_quote                       BOOLEAN,
  event_quote                       BOOLEAN,
  unavailable                       BOOLEAN,
  has_reservation                   BOOLEAN,

  -- listas e objetos brutos (usando JSON nativo do MySQL)
  tags                              JSON,
  tags_string                       TEXT,
  tags_ids                          JSON,
  events                            JSON,

  -- payload completo como JSON
  raw_payload                       JSON            NOT NULL,

  PRIMARY KEY (fetch_date, lead_id, conversation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE INDEX idx_arrival_date    ON asksuite_leads(arrival_date);
CREATE INDEX idx_departure_date  ON asksuite_leads(departure_date);
CREATE INDEX idx_company_id      ON asksuite_leads(company_id);
CREATE INDEX idx_language        ON asksuite_leads(language);
CREATE INDEX idx_updated_at      ON asksuite_leads(updated_at);
