-- +goose Up
-- +goose StatementBegin

-- Instâncias WhatsApp gerenciadas pelo whatsmeow
CREATE TABLE IF NOT EXISTS whatsapp_instances (
    id                   TEXT PRIMARY KEY,
    tenant_id            UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name                 TEXT NOT NULL,
    dept                 TEXT NOT NULL DEFAULT '',
    numero               TEXT NOT NULL DEFAULT '',
    jid                  TEXT NOT NULL DEFAULT '',
    status               TEXT NOT NULL DEFAULT 'waiting',
    fila                 INTEGER NOT NULL DEFAULT 0,
    sync_at              TEXT NOT NULL DEFAULT '--:--',
    uptime               TEXT NOT NULL DEFAULT '—',
    mem_mb               TEXT NOT NULL DEFAULT '—',
    latency              TEXT NOT NULL DEFAULT '—',
    reconn               INTEGER NOT NULL DEFAULT 0,
    enviadas             INTEGER NOT NULL DEFAULT 0,
    recebidas            INTEGER NOT NULL DEFAULT 0,
    -- Configurações
    config_max_simul     INTEGER NOT NULL DEFAULT 3,
    config_max_retries   INTEGER NOT NULL DEFAULT 5,
    config_delay_min     INTEGER NOT NULL DEFAULT 3,
    config_delay_max     INTEGER NOT NULL DEFAULT 8,
    config_media_path    TEXT NOT NULL DEFAULT '',
    config_webhook       TEXT NOT NULL DEFAULT '',
    config_proxy         TEXT NOT NULL DEFAULT '',
    config_auto_reconn   BOOLEAN NOT NULL DEFAULT TRUE,
    config_save_media    BOOLEAN NOT NULL DEFAULT TRUE,
    config_notify_email  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_whatsapp_instances_tenant ON whatsapp_instances(tenant_id);

-- Mensagens recebidas e enviadas
CREATE TABLE IF NOT EXISTS whatsapp_messages (
    id           TEXT PRIMARY KEY,
    tenant_id    UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    instance_id  TEXT NOT NULL REFERENCES whatsapp_instances(id) ON DELETE CASCADE,
    chat_jid     TEXT NOT NULL,
    chat_name    TEXT NOT NULL DEFAULT '',
    direction    TEXT NOT NULL DEFAULT 'out', -- 'in' | 'out'
    type         TEXT NOT NULL DEFAULT 'text',
    body         TEXT NOT NULL DEFAULT '',
    media_url    TEXT,
    status       TEXT NOT NULL DEFAULT 'enviada',
    sent_at      TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_instance ON whatsapp_messages(instance_id, chat_jid);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_tenant ON whatsapp_messages(tenant_id);

-- Fila de mensagens a enviar
CREATE TABLE IF NOT EXISTS whatsapp_queue (
    id           TEXT PRIMARY KEY,
    tenant_id    UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    instance_id  TEXT NOT NULL REFERENCES whatsapp_instances(id) ON DELETE CASCADE,
    dest         TEXT NOT NULL,
    dest_jid     TEXT NOT NULL,
    msg          TEXT NOT NULL,
    type         TEXT NOT NULL DEFAULT 'text',
    media_url    TEXT,
    status       TEXT NOT NULL DEFAULT 'pendente', -- pendente | enviando | enviada | erro
    retries      INTEGER NOT NULL DEFAULT 0,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_whatsapp_queue_tenant ON whatsapp_queue(tenant_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_queue_status ON whatsapp_queue(status);

-- Contatos descobertos via mensagens
CREATE TABLE IF NOT EXISTS whatsapp_contacts (
    id           TEXT PRIMARY KEY,
    tenant_id    UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    instance_id  TEXT NOT NULL REFERENCES whatsapp_instances(id) ON DELETE CASCADE,
    nome         TEXT NOT NULL DEFAULT '',
    numero       TEXT NOT NULL DEFAULT '',
    jid          TEXT NOT NULL,
    tags         TEXT NOT NULL DEFAULT '',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (tenant_id, instance_id, jid)
);

CREATE INDEX IF NOT EXISTS idx_whatsapp_contacts_tenant ON whatsapp_contacts(tenant_id);

-- Timeline de eventos
CREATE TABLE IF NOT EXISTS whatsapp_events (
    id           TEXT PRIMARY KEY,
    tenant_id    UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    instance_id  TEXT NOT NULL REFERENCES whatsapp_instances(id) ON DELETE CASCADE,
    type         TEXT NOT NULL,
    icon         TEXT NOT NULL DEFAULT 'info',
    label        TEXT NOT NULL DEFAULT '',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_whatsapp_events_tenant ON whatsapp_events(tenant_id, created_at DESC);

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS whatsapp_events;
DROP TABLE IF EXISTS whatsapp_contacts;
DROP TABLE IF EXISTS whatsapp_queue;
DROP TABLE IF EXISTS whatsapp_messages;
DROP TABLE IF EXISTS whatsapp_instances;
-- +goose StatementEnd