CREATE SCHEMA IF NOT EXISTS messaging;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS messaging.whatsapp_simulator_profiles (
    profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    label TEXT NOT NULL,
    business_id TEXT,
    sucursal_id INTEGER,
    user_phone TEXT NOT NULL,
    wa_id TEXT NOT NULL,
    user_whatsapp_name TEXT,
    phone_number_id TEXT NOT NULL,
    bot_phone_number TEXT,
    status TEXT NOT NULL DEFAULT 'active',
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT whatsapp_simulator_profiles_status_chk CHECK (status IN ('active', 'archived'))
);

CREATE UNIQUE INDEX IF NOT EXISTS whatsapp_simulator_profiles_active_identity_idx
    ON messaging.whatsapp_simulator_profiles (user_phone, phone_number_id)
    WHERE status = 'active';

CREATE INDEX IF NOT EXISTS whatsapp_simulator_profiles_business_idx
    ON messaging.whatsapp_simulator_profiles (business_id, sucursal_id);

CREATE TABLE IF NOT EXISTS messaging.whatsapp_simulator_conversations (
    conversation_id TEXT PRIMARY KEY,
    profile_id UUID NOT NULL REFERENCES messaging.whatsapp_simulator_profiles(profile_id) ON DELETE CASCADE,
    business_id TEXT,
    sucursal_id INTEGER,
    status TEXT NOT NULL DEFAULT 'active',
    greeting_text TEXT,
    goodbye_text TEXT,
    started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    ended_at TIMESTAMPTZ,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT whatsapp_simulator_conversations_status_chk CHECK (status IN ('active', 'ended', 'archived'))
);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_conversations_profile_idx
    ON messaging.whatsapp_simulator_conversations (profile_id, status, started_at DESC);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_conversations_updated_idx
    ON messaging.whatsapp_simulator_conversations (updated_at DESC);

CREATE TABLE IF NOT EXISTS messaging.whatsapp_simulator_media (
    media_id TEXT PRIMARY KEY,
    profile_id UUID REFERENCES messaging.whatsapp_simulator_profiles(profile_id) ON DELETE SET NULL,
    conversation_id TEXT REFERENCES messaging.whatsapp_simulator_conversations(conversation_id) ON DELETE SET NULL,
    media_type TEXT NOT NULL,
    content_type TEXT NOT NULL,
    original_filename TEXT,
    storage_path TEXT NOT NULL,
    byte_size BIGINT NOT NULL,
    sha256 TEXT NOT NULL,
    caption TEXT,
    status TEXT NOT NULL DEFAULT 'active',
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT whatsapp_simulator_media_status_chk CHECK (status IN ('active', 'deleted'))
);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_media_conversation_idx
    ON messaging.whatsapp_simulator_media (conversation_id, created_at DESC);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_media_sha256_idx
    ON messaging.whatsapp_simulator_media (sha256);

CREATE TABLE IF NOT EXISTS messaging.whatsapp_simulator_events (
    event_id BIGSERIAL PRIMARY KEY,
    event_uid TEXT NOT NULL UNIQUE,
    profile_id UUID REFERENCES messaging.whatsapp_simulator_profiles(profile_id) ON DELETE SET NULL,
    conversation_id TEXT REFERENCES messaging.whatsapp_simulator_conversations(conversation_id) ON DELETE SET NULL,
    direction TEXT NOT NULL,
    event_type TEXT NOT NULL,
    message_type TEXT,
    status TEXT NOT NULL DEFAULT 'accepted',
    channel_message_id TEXT,
    user_phone TEXT,
    phone_number_id TEXT,
    bot_phone_number TEXT,
    text TEXT,
    media_id TEXT REFERENCES messaging.whatsapp_simulator_media(media_id) ON DELETE SET NULL,
    caption TEXT,
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT whatsapp_simulator_events_direction_chk CHECK (direction IN ('inbound', 'outbound', 'system')),
    CONSTRAINT whatsapp_simulator_events_status_chk CHECK (status IN ('accepted', 'queued', 'delivered', 'failed', 'ignored'))
);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_events_conversation_cursor_idx
    ON messaging.whatsapp_simulator_events (conversation_id, event_id);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_events_profile_created_idx
    ON messaging.whatsapp_simulator_events (profile_id, created_at DESC);

CREATE INDEX IF NOT EXISTS whatsapp_simulator_events_channel_message_idx
    ON messaging.whatsapp_simulator_events (channel_message_id);

COMMENT ON TABLE messaging.whatsapp_simulator_profiles IS 'Perfiles locales del simulador WhatsApp para pruebas dev-only contra Brain.';
COMMENT ON TABLE messaging.whatsapp_simulator_conversations IS 'Conversaciones locales del simulador WhatsApp, con inicio/cierre persistente.';
COMMENT ON TABLE messaging.whatsapp_simulator_media IS 'Media local subido por el simulador WhatsApp y resuelto por sim_media_id sin Meta Graph.';
COMMENT ON TABLE messaging.whatsapp_simulator_events IS 'Historial canonico local de eventos inbound/outbound/system del simulador WhatsApp.';
