-- Tabelas hubee_wpp — espelho analítico (não é o Prisma da Evolution)

CREATE TABLE IF NOT EXISTS hubee_wpp.instances (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  hubee_usuario_id text NOT NULL,
  evolution_instance_name text NOT NULL,
  phone_jid text,
  status text NOT NULL DEFAULT 'disconnected'
    CHECK (status IN ('disconnected', 'qr', 'connecting', 'open', 'close')),
  last_qr_base64 text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_hubee_wpp_instances_usuario UNIQUE (hubee_usuario_id),
  CONSTRAINT uq_hubee_wpp_instances_evo_name UNIQUE (evolution_instance_name)
);

CREATE TABLE IF NOT EXISTS hubee_wpp.chats (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  instance_id uuid NOT NULL REFERENCES hubee_wpp.instances (id) ON DELETE CASCADE,
  remote_jid text NOT NULL,
  contact_name text,
  last_message_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_hubee_wpp_chats_instance_jid UNIQUE (instance_id, remote_jid)
);

CREATE TABLE IF NOT EXISTS hubee_wpp.messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  instance_id uuid NOT NULL REFERENCES hubee_wpp.instances (id) ON DELETE CASCADE,
  chat_id uuid NOT NULL REFERENCES hubee_wpp.chats (id) ON DELETE CASCADE,
  wa_message_id text,
  from_me boolean NOT NULL DEFAULT false,
  direction text NOT NULL DEFAULT 'in'
    CHECK (direction IN ('in', 'out')),
  body text,
  message_type text NOT NULL DEFAULT 'conversation',
  sent_at timestamptz NOT NULL,
  raw_json jsonb,
  source text NOT NULL DEFAULT 'evolution'
    CHECK (source IN ('evolution', 'txt_import')),
  dedupe_key text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_hubee_wpp_messages_dedupe UNIQUE (dedupe_key)
);

CREATE TABLE IF NOT EXISTS hubee_wpp.import_batches (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  instance_id uuid NOT NULL REFERENCES hubee_wpp.instances (id) ON DELETE CASCADE,
  hubee_usuario_id text NOT NULL,
  filename text,
  imported_count integer NOT NULL DEFAULT 0,
  skipped_duplicates integer NOT NULL DEFAULT 0,
  error_count integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_hubee_wpp_messages_instance_sent
  ON hubee_wpp.messages (instance_id, sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_hubee_wpp_messages_chat_sent
  ON hubee_wpp.messages (chat_id, sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_hubee_wpp_chats_instance_last
  ON hubee_wpp.chats (instance_id, last_message_at DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_hubee_wpp_instances_status
  ON hubee_wpp.instances (status);

ALTER TABLE hubee_wpp.instances ENABLE ROW LEVEL SECURITY;
ALTER TABLE hubee_wpp.chats ENABLE ROW LEVEL SECURITY;
ALTER TABLE hubee_wpp.messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE hubee_wpp.import_batches ENABLE ROW LEVEL SECURITY;

-- Acesso via Nest/sync com role postgres/service_role (bypass RLS).
-- anon/authenticated: sem policies = sem acesso pela Data API.