-- =============================================================================
-- sql-client-bootstrap.sql — Bootstrap completo para un nuevo proyecto Supabase de cliente
-- =============================================================================
-- EJECUTAR UNA SOLA VEZ en el SQL Editor del proyecto Supabase del CLIENTE.
--
-- Este archivo crea todo lo necesario para que un nuevo cliente funcione:
--   1. Tabla session_history (memoria de sesion por telefono)
--   2. RPC provision_client_schema (crea schema aislado multi-tenant)
--   3. RPCs load_user_memory y save_user_memory (memoria de usuario del bot)
--   4. Tabla client_credentials + RPCs de Vault (gestion de credenciales cifradas)
--   5. RLS habilitado en todas las tablas
--   6. Grants para service_role / revokes para anon
--
-- Todo es idempotente (IF NOT EXISTS / CREATE OR REPLACE) y se puede re-ejecutar
-- de forma segura sin perder datos existentes.
--
-- Prerrequisitos:
--   - pgcrypto habilitado (default en Supabase)
--   - Supabase Vault habilitado (para client_credentials cifradas)
-- =============================================================================


-- ══════════════════════════════════════════════════════════════════════════════
-- 1. session_history — memoria resumida por numero de telefono
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.session_history (
  phone_number TEXT        PRIMARY KEY,
  summary      TEXT,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE public.session_history ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON public.session_history;
CREATE POLICY "service_role_all"
  ON public.session_history FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

GRANT ALL ON TABLE public.session_history TO service_role;
REVOKE ALL ON TABLE public.session_history FROM anon;
REVOKE ALL ON TABLE public.session_history FROM authenticated;


-- ══════════════════════════════════════════════════════════════════════════════
-- 2. Helper: trigger function para updated_at
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;


-- ══════════════════════════════════════════════════════════════════════════════
-- 3. RPC: provision_client_schema
--    Crea el schema aislado c_{slug} con todas las tablas del cliente.
--    Llamado por el pipeline de provisioning de n8n.
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.provision_client_schema(
  p_slug TEXT
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $body$
DECLARE
  v_schema TEXT := 'c_' || replace(p_slug, '-', '_');
  v_already_existed BOOLEAN := false;
BEGIN
  -- Idempotency: check if schema already exists
  SELECT EXISTS (
    SELECT 1 FROM information_schema.schemata WHERE schema_name = v_schema
  ) INTO v_already_existed;

  IF v_already_existed THEN
    RETURN jsonb_build_object(
      'schema',   v_schema,
      'created',  false,
      'message',  'Schema already exists — skipped'
    );
  END IF;

  -- 1. Create isolated schema for the client
  EXECUTE format('CREATE SCHEMA %I', v_schema);

  -- Helper: set_updated_at trigger function
  EXECUTE format($$
    CREATE OR REPLACE FUNCTION %I.set_updated_at()
    RETURNS TRIGGER LANGUAGE plpgsql AS $fn$
    BEGIN
      NEW.updated_at = now();
      RETURN NEW;
    END;
    $fn$
  $$, v_schema);

  -- 2. contacts
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.contacts (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      name       TEXT,
      phone      TEXT        UNIQUE,
      email      TEXT,
      channel    TEXT        NOT NULL DEFAULT 'whatsapp',
      metadata   JSONB       NOT NULL DEFAULT '{}',
      created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  $$, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_contacts_phone ON %I.contacts(phone);
    CREATE INDEX IF NOT EXISTS idx_contacts_email ON %I.contacts(email) WHERE email IS NOT NULL
  $$, v_schema, v_schema);

  EXECUTE format($$
    DROP TRIGGER IF EXISTS trg_contacts_updated_at ON %I.contacts;
    CREATE TRIGGER trg_contacts_updated_at
      BEFORE UPDATE ON %I.contacts
      FOR EACH ROW EXECUTE FUNCTION %I.set_updated_at()
  $$, v_schema, v_schema, v_schema);

  -- 3. appointments
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.appointments (
      id                UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      contact_id        UUID        REFERENCES %I.contacts(id) ON DELETE SET NULL,
      customer_name     TEXT,
      customer_phone    TEXT,
      service_name      TEXT,
      scheduled_at      TIMESTAMPTZ NOT NULL,
      duration_min      INT         NOT NULL DEFAULT 60,
      status            TEXT        NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending','confirmed','cancelled','completed','rescheduled')),
      notes             TEXT,
      reminder_24h_sent BOOLEAN     NOT NULL DEFAULT false,
      reminder_2h_sent  BOOLEAN     NOT NULL DEFAULT false,
      created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  $$, v_schema, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_appointments_contact   ON %I.appointments(contact_id);
    CREATE INDEX IF NOT EXISTS idx_appointments_scheduled ON %I.appointments(scheduled_at);
    CREATE INDEX IF NOT EXISTS idx_appointments_status    ON %I.appointments(status)
      WHERE status IN ('pending','confirmed')
  $$, v_schema, v_schema, v_schema);

  EXECUTE format($$
    DROP TRIGGER IF EXISTS trg_appointments_updated_at ON %I.appointments;
    CREATE TRIGGER trg_appointments_updated_at
      BEFORE UPDATE ON %I.appointments
      FOR EACH ROW EXECUTE FUNCTION %I.set_updated_at()
  $$, v_schema, v_schema, v_schema);

  -- 4. tickets
  EXECUTE format($$
    CREATE SEQUENCE IF NOT EXISTS %I.ticket_number_seq START WITH 1
  $$, v_schema);

  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.tickets (
      id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      contact_id    UUID        REFERENCES %I.contacts(id) ON DELETE SET NULL,
      ticket_number TEXT        UNIQUE NOT NULL,
      type          TEXT        NOT NULL DEFAULT 'queja'
                    CHECK (type IN ('queja','reclamo','sugerencia','escalacion')),
      subject       TEXT,
      description   TEXT,
      priority      TEXT        NOT NULL DEFAULT 'media'
                    CHECK (priority IN ('baja','media','alta','urgente')),
      status        TEXT        NOT NULL DEFAULT 'open'
                    CHECK (status IN ('open','in_progress','resolved','closed')),
      assigned_to   TEXT,
      created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  $$, v_schema, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_tickets_contact ON %I.tickets(contact_id);
    CREATE INDEX IF NOT EXISTS idx_tickets_status  ON %I.tickets(status) WHERE status IN ('open','in_progress');
    CREATE INDEX IF NOT EXISTS idx_tickets_type    ON %I.tickets(type)
  $$, v_schema, v_schema, v_schema);

  EXECUTE format($$
    CREATE OR REPLACE FUNCTION %I.generate_ticket_number()
    RETURNS TRIGGER LANGUAGE plpgsql AS $fn$
    DECLARE
      v_prefix TEXT;
      v_seq    BIGINT;
    BEGIN
      IF NEW.ticket_number IS NOT NULL AND NEW.ticket_number <> '' THEN
        RETURN NEW;
      END IF;
      v_seq := nextval('%s.ticket_number_seq');
      v_prefix := CASE WHEN NEW.type = 'escalacion' THEN 'ESC-' ELSE 'TKT-' END;
      NEW.ticket_number := v_prefix || lpad(v_seq::text, 5, '0');
      RETURN NEW;
    END;
    $fn$
  $$, v_schema, v_schema);

  EXECUTE format($$
    DROP TRIGGER IF EXISTS trg_tickets_generate_number ON %I.tickets;
    CREATE TRIGGER trg_tickets_generate_number
      BEFORE INSERT ON %I.tickets
      FOR EACH ROW EXECUTE FUNCTION %I.generate_ticket_number();

    DROP TRIGGER IF EXISTS trg_tickets_updated_at ON %I.tickets;
    CREATE TRIGGER trg_tickets_updated_at
      BEFORE UPDATE ON %I.tickets
      FOR EACH ROW EXECUTE FUNCTION %I.set_updated_at()
  $$, v_schema, v_schema, v_schema, v_schema, v_schema, v_schema);

  -- 5. user_memory
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.user_memory (
      id                UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      session_id        TEXT        NOT NULL UNIQUE,
      user_name         TEXT,
      user_phone        TEXT,
      user_email        TEXT,
      summary           TEXT,
      interaction_count INT         NOT NULL DEFAULT 1,
      is_returning      BOOLEAN     NOT NULL DEFAULT false,
      metadata          JSONB       NOT NULL DEFAULT '{}',
      first_seen_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
      last_seen_at      TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  $$, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_user_memory_phone ON %I.user_memory(user_phone)
      WHERE user_phone IS NOT NULL
  $$, v_schema);

  -- 6. services
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.services (
      id           UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
      name         TEXT         NOT NULL,
      description  TEXT,
      price        NUMERIC(10,2),
      duration_min INT,
      category     TEXT,
      active       BOOLEAN      NOT NULL DEFAULT true,
      sort_order   INT          NOT NULL DEFAULT 0,
      created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
    )
  $$, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_services_active ON %I.services(active) WHERE active = true
  $$, v_schema);

  -- 7. business_hours
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.business_hours (
      id          UUID    PRIMARY KEY DEFAULT gen_random_uuid(),
      day_of_week INT     NOT NULL CHECK (day_of_week >= 0 AND day_of_week <= 6),
      open_time   TIME,
      close_time  TIME,
      is_closed   BOOLEAN NOT NULL DEFAULT false
    )
  $$, v_schema);

  EXECUTE format($$
    CREATE UNIQUE INDEX IF NOT EXISTS idx_business_hours_day ON %I.business_hours(day_of_week)
  $$, v_schema);

  -- 8. conversations
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.conversations (
      id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      contact_id UUID        REFERENCES %I.contacts(id) ON DELETE SET NULL,
      channel    TEXT        NOT NULL DEFAULT 'whatsapp',
      status     TEXT        NOT NULL DEFAULT 'open'
                 CHECK (status IN ('open','closed','escalated')),
      started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
      closed_at  TIMESTAMPTZ,
      summary    TEXT,
      metadata   JSONB       NOT NULL DEFAULT '{}'
    )
  $$, v_schema, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_conversations_contact ON %I.conversations(contact_id);
    CREATE INDEX IF NOT EXISTS idx_conversations_status  ON %I.conversations(status) WHERE status = 'open'
  $$, v_schema, v_schema);

  -- 9. bot_analytics
  EXECUTE format($$
    CREATE TABLE IF NOT EXISTS %I.bot_analytics (
      id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
      session_id       TEXT,
      action           TEXT        NOT NULL,
      status           TEXT        NOT NULL DEFAULT 'success'
                       CHECK (status IN ('success','error')),
      response_time_ms INT,
      metadata         JSONB       NOT NULL DEFAULT '{}',
      created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  $$, v_schema);

  EXECUTE format($$
    CREATE INDEX IF NOT EXISTS idx_bot_analytics_action  ON %I.bot_analytics(action);
    CREATE INDEX IF NOT EXISTS idx_bot_analytics_created ON %I.bot_analytics(created_at DESC);
    CREATE INDEX IF NOT EXISTS idx_bot_analytics_session ON %I.bot_analytics(session_id)
      WHERE session_id IS NOT NULL
  $$, v_schema, v_schema, v_schema);

  -- RLS: service_role full access on all tables
  EXECUTE format('ALTER TABLE %I.contacts       ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.appointments   ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.tickets        ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.user_memory    ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.services       ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.business_hours ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.conversations  ENABLE ROW LEVEL SECURITY', v_schema);
  EXECUTE format('ALTER TABLE %I.bot_analytics  ENABLE ROW LEVEL SECURITY', v_schema);

  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.contacts',       v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.appointments',   v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.tickets',        v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.user_memory',    v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.services',       v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.business_hours', v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.conversations',  v_schema);
  EXECUTE format('DROP POLICY IF EXISTS "service_role_all" ON %I.bot_analytics',  v_schema);

  EXECUTE format('CREATE POLICY "service_role_all" ON %I.contacts       FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.appointments   FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.tickets        FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.user_memory    FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.services       FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.business_hours FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.conversations  FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);
  EXECUTE format('CREATE POLICY "service_role_all" ON %I.bot_analytics  FOR ALL TO service_role USING (true) WITH CHECK (true)', v_schema);

  EXECUTE format('REVOKE ALL ON TABLE %I.contacts       FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.appointments   FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.tickets        FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.user_memory    FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.services       FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.business_hours FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.conversations  FROM anon', v_schema);
  EXECUTE format('REVOKE ALL ON TABLE %I.bot_analytics  FROM anon', v_schema);

  EXECUTE format('GRANT ALL ON TABLE %I.contacts       TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.appointments   TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.tickets        TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.user_memory    TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.services       TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.business_hours TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.conversations  TO service_role', v_schema);
  EXECUTE format('GRANT ALL ON TABLE %I.bot_analytics  TO service_role', v_schema);

  EXECUTE format(
    'GRANT USAGE, SELECT ON SEQUENCE %I.ticket_number_seq TO service_role',
    v_schema
  );

  RETURN jsonb_build_object(
    'schema',  v_schema,
    'created', true,
    'tables',  ARRAY[
      'contacts','appointments','tickets','user_memory',
      'services','business_hours','conversations','bot_analytics'
    ]
  );

EXCEPTION WHEN OTHERS THEN
  BEGIN
    EXECUTE format('DROP SCHEMA IF EXISTS %I CASCADE', v_schema);
  EXCEPTION WHEN OTHERS THEN
    NULL;
  END;
  RAISE EXCEPTION 'provision_client_schema failed for slug=%: % (SQLSTATE: %)',
    p_slug, SQLERRM, SQLSTATE;
END;
$body$;

GRANT EXECUTE ON FUNCTION public.provision_client_schema(TEXT) TO service_role;
REVOKE ALL ON FUNCTION public.provision_client_schema(TEXT) FROM anon;
REVOKE ALL ON FUNCTION public.provision_client_schema(TEXT) FROM authenticated;


-- ══════════════════════════════════════════════════════════════════════════════
-- 4. RPC: load_user_memory
--    Carga memoria del usuario por session_id desde el schema aislado del cliente.
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.load_user_memory(
  p_client_slug TEXT,
  p_session_id  TEXT
)
RETURNS TABLE (
  session_id        TEXT,
  user_name         TEXT,
  user_phone        TEXT,
  user_email        TEXT,
  summary           TEXT,
  interaction_count INT,
  is_returning      BOOLEAN,
  metadata          JSONB,
  first_seen_at     TIMESTAMPTZ,
  last_seen_at      TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $body$
DECLARE
  v_schema TEXT := 'c_' || replace(p_client_slug, '-', '_');
  v_exists BOOLEAN;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.schemata WHERE schema_name = v_schema
  ) THEN
    RETURN QUERY
      SELECT p_session_id, NULL::TEXT, NULL::TEXT, NULL::TEXT, NULL::TEXT,
             0::INT, false, '{}'::JSONB, now(), now();
    RETURN;
  END IF;

  EXECUTE format(
    'SELECT EXISTS(SELECT 1 FROM %I.user_memory WHERE session_id = $1)',
    v_schema
  ) INTO v_exists USING p_session_id;

  IF v_exists THEN
    EXECUTE format($$
      UPDATE %I.user_memory
      SET interaction_count = interaction_count + 1,
          is_returning      = true,
          last_seen_at      = now()
      WHERE session_id = $1
    $$, v_schema) USING p_session_id;

    RETURN QUERY EXECUTE format($$
      SELECT session_id, user_name, user_phone, user_email,
             summary, interaction_count, is_returning,
             metadata, first_seen_at, last_seen_at
      FROM %I.user_memory
      WHERE session_id = $1
    $$, v_schema) USING p_session_id;
  ELSE
    RETURN QUERY
      SELECT p_session_id AS session_id,
             NULL::TEXT    AS user_name,
             NULL::TEXT    AS user_phone,
             NULL::TEXT    AS user_email,
             NULL::TEXT    AS summary,
             0::INT        AS interaction_count,
             false         AS is_returning,
             '{}'::JSONB   AS metadata,
             now()         AS first_seen_at,
             now()         AS last_seen_at;
  END IF;
END;
$body$;

GRANT EXECUTE ON FUNCTION public.load_user_memory(TEXT, TEXT) TO service_role;
REVOKE ALL ON FUNCTION public.load_user_memory(TEXT, TEXT) FROM anon;


-- ══════════════════════════════════════════════════════════════════════════════
-- 5. RPC: save_user_memory
--    Upsert de memoria de usuario en el schema aislado del cliente.
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.save_user_memory(
  p_client_slug TEXT,
  p_session_id  TEXT,
  p_user_name   TEXT    DEFAULT NULL,
  p_user_phone  TEXT    DEFAULT NULL,
  p_user_email  TEXT    DEFAULT NULL,
  p_summary     TEXT    DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $body$
DECLARE
  v_schema TEXT := 'c_' || replace(p_client_slug, '-', '_');
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.schemata WHERE schema_name = v_schema
  ) THEN
    RETURN;
  END IF;

  EXECUTE format($$
    INSERT INTO %I.user_memory (
      session_id, user_name, user_phone, user_email, summary,
      interaction_count, is_returning, first_seen_at, last_seen_at
    ) VALUES ($1, $2, $3, $4, $5, 1, false, now(), now())
    ON CONFLICT (session_id) DO UPDATE SET
      user_name    = COALESCE(NULLIF($2, ''), %I.user_memory.user_name),
      user_phone   = COALESCE(NULLIF($3, ''), %I.user_memory.user_phone),
      user_email   = COALESCE(NULLIF($4, ''), %I.user_memory.user_email),
      summary      = COALESCE(NULLIF($5, ''), %I.user_memory.summary),
      last_seen_at = now()
  $$, v_schema, v_schema, v_schema, v_schema, v_schema)
  USING p_session_id, p_user_name, p_user_phone, p_user_email, p_summary;
END;
$body$;

GRANT EXECUTE ON FUNCTION public.save_user_memory(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT) TO service_role;
REVOKE ALL ON FUNCTION public.save_user_memory(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT) FROM anon;


-- ══════════════════════════════════════════════════════════════════════════════
-- 6. Tabla client_credentials — Encryption via Supabase Vault
-- ══════════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS public.client_credentials (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  client_slug      TEXT        NOT NULL,
  service          TEXT        NOT NULL,
  credential_type  TEXT        NOT NULL,
  config           JSONB       NOT NULL DEFAULT '{}',
  config_secret_id UUID,
  is_configured    BOOLEAN     NOT NULL DEFAULT false,
  is_active        BOOLEAN     NOT NULL DEFAULT false,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(client_slug, service),
  CONSTRAINT chk_service CHECK (service IN (
    'google_calendar', 'crm', 'payment', 'mailing', 'evolution_api', 'whatsapp_cloud'
  )),
  CONSTRAINT chk_credential_type CHECK (credential_type IN (
    'oauth2', 'api_key', 'webhook', 'smtp', 'custom'
  ))
);

CREATE INDEX IF NOT EXISTS idx_client_creds_slug    ON public.client_credentials(client_slug);
CREATE INDEX IF NOT EXISTS idx_client_creds_service ON public.client_credentials(service);
CREATE INDEX IF NOT EXISTS idx_client_creds_active
  ON public.client_credentials(client_slug, service)
  WHERE is_active = true AND is_configured = true;

-- Trigger: auto-update updated_at
CREATE OR REPLACE FUNCTION public.update_client_credentials_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;

DROP TRIGGER IF EXISTS trg_client_creds_updated_at ON public.client_credentials;
CREATE TRIGGER trg_client_creds_updated_at
  BEFORE UPDATE ON public.client_credentials
  FOR EACH ROW EXECUTE FUNCTION public.update_client_credentials_updated_at();

-- Trigger: enforce config siempre vacio (previene escritura accidental de secrets)
CREATE OR REPLACE FUNCTION public.enforce_config_empty()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.config = '{}'::jsonb;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_enforce_config_empty ON public.client_credentials;
CREATE TRIGGER trg_enforce_config_empty
  BEFORE INSERT OR UPDATE ON public.client_credentials
  FOR EACH ROW EXECUTE FUNCTION public.enforce_config_empty();

-- RPC: set_credential
CREATE OR REPLACE FUNCTION public.set_credential(
  p_slug            TEXT,
  p_service         TEXT,
  p_credential_type TEXT,
  p_config          JSONB,
  p_is_active       BOOLEAN DEFAULT true
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_secret_name        TEXT;
  v_secret_id          UUID;
  v_existing_secret_id UUID;
BEGIN
  v_secret_name := 'cred__' || p_slug || '__' || p_service;

  SELECT config_secret_id INTO v_existing_secret_id
  FROM public.client_credentials
  WHERE client_slug = p_slug AND service = p_service;

  IF v_existing_secret_id IS NOT NULL THEN
    PERFORM vault.update_secret(
      v_existing_secret_id,
      p_config::text,
      v_secret_name,
      'Credential config for client=' || p_slug || ' service=' || p_service
    );
    v_secret_id := v_existing_secret_id;
  ELSE
    v_secret_id := vault.create_secret(
      p_config::text,
      v_secret_name,
      'Credential config for client=' || p_slug || ' service=' || p_service
    );
  END IF;

  INSERT INTO public.client_credentials (
    client_slug, service, credential_type, config, config_secret_id,
    is_configured, is_active
  ) VALUES (
    p_slug, p_service, p_credential_type, '{}'::jsonb, v_secret_id,
    true, p_is_active
  )
  ON CONFLICT (client_slug, service) DO UPDATE SET
    credential_type  = EXCLUDED.credential_type,
    config_secret_id = v_secret_id,
    config           = '{}'::jsonb,
    is_configured    = true,
    is_active        = p_is_active,
    updated_at       = now();

  RETURN v_secret_id;
END;
$$;

-- RPC: get_credential
CREATE OR REPLACE FUNCTION public.get_credential(
  p_slug    TEXT,
  p_service TEXT
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_secret_id   UUID;
  v_secret_text TEXT;
BEGIN
  SELECT config_secret_id INTO v_secret_id
  FROM public.client_credentials
  WHERE client_slug  = p_slug
    AND service       = p_service
    AND is_active     = true
    AND is_configured = true;

  IF v_secret_id IS NULL THEN
    RETURN NULL;
  END IF;

  SELECT decrypted_secret INTO v_secret_text
  FROM vault.decrypted_secrets
  WHERE id = v_secret_id;

  IF v_secret_text IS NULL THEN
    RETURN NULL;
  END IF;

  RETURN v_secret_text::jsonb;
EXCEPTION
  WHEN others THEN
    RAISE EXCEPTION 'credential_read_error';
END;
$$;

-- RPC: delete_credential
CREATE OR REPLACE FUNCTION public.delete_credential(
  p_slug    TEXT,
  p_service TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_secret_id UUID;
BEGIN
  SELECT config_secret_id INTO v_secret_id
  FROM public.client_credentials
  WHERE client_slug = p_slug AND service = p_service;

  IF v_secret_id IS NOT NULL THEN
    DELETE FROM vault.secrets WHERE id = v_secret_id;
  END IF;

  DELETE FROM public.client_credentials
  WHERE client_slug = p_slug AND service = p_service;

  RETURN FOUND;
END;
$$;

-- RPC: list_credentials
CREATE OR REPLACE FUNCTION public.list_credentials(
  p_slug TEXT DEFAULT NULL
)
RETURNS TABLE (
  client_slug      TEXT,
  service          TEXT,
  credential_type  TEXT,
  is_configured    BOOLEAN,
  is_active        BOOLEAN,
  has_vault_secret BOOLEAN,
  created_at       TIMESTAMPTZ,
  updated_at       TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT
    c.client_slug, c.service, c.credential_type,
    c.is_configured, c.is_active,
    (c.config_secret_id IS NOT NULL) AS has_vault_secret,
    c.created_at, c.updated_at
  FROM public.client_credentials c
  WHERE (p_slug IS NULL OR c.client_slug = p_slug)
  ORDER BY c.client_slug, c.service;
END;
$$;

-- RLS para client_credentials
ALTER TABLE public.client_credentials ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON public.client_credentials;
CREATE POLICY "service_role_all"
  ON public.client_credentials FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "authenticated_no_direct_access" ON public.client_credentials;
CREATE POLICY "authenticated_no_direct_access"
  ON public.client_credentials FOR ALL
  TO authenticated
  USING (false)
  WITH CHECK (false);

REVOKE ALL ON TABLE public.client_credentials FROM anon;
REVOKE ALL ON TABLE public.client_credentials FROM authenticated;
GRANT ALL ON TABLE public.client_credentials TO service_role;

-- Grants para RPCs de credenciales
GRANT EXECUTE ON FUNCTION public.set_credential(TEXT, TEXT, TEXT, JSONB, BOOLEAN) TO service_role;
GRANT EXECUTE ON FUNCTION public.get_credential(TEXT, TEXT)                        TO service_role;
GRANT EXECUTE ON FUNCTION public.delete_credential(TEXT, TEXT)                     TO service_role;
GRANT EXECUTE ON FUNCTION public.list_credentials(TEXT)                            TO service_role;

GRANT EXECUTE ON FUNCTION public.get_credential(TEXT, TEXT)  TO authenticated;
GRANT EXECUTE ON FUNCTION public.list_credentials(TEXT)      TO authenticated;

REVOKE ALL ON FUNCTION public.set_credential(TEXT, TEXT, TEXT, JSONB, BOOLEAN) FROM anon;
REVOKE ALL ON FUNCTION public.get_credential(TEXT, TEXT)                        FROM anon;
REVOKE ALL ON FUNCTION public.delete_credential(TEXT, TEXT)                     FROM anon;
REVOKE ALL ON FUNCTION public.list_credentials(TEXT)                            FROM anon;


-- ══════════════════════════════════════════════════════════════════════════════
-- 7. RPC: log_bot_action (analytics)
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.log_bot_action(
  p_session_id       TEXT    DEFAULT NULL,
  p_action           TEXT    DEFAULT 'unknown',
  p_status           TEXT    DEFAULT 'success',
  p_response_time_ms INT     DEFAULT NULL,
  p_metadata         JSONB   DEFAULT '{}'
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_id UUID;
BEGIN
  INSERT INTO public.bot_analytics (session_id, action, status, response_time_ms, metadata)
  VALUES (p_session_id, p_action, p_status, p_response_time_ms, p_metadata)
  RETURNING id INTO v_id;

  RETURN v_id;
END;
$$;

-- bot_analytics tabla (si no se creo via provision_client_schema)
CREATE TABLE IF NOT EXISTS public.bot_analytics (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id       TEXT,
  action           TEXT        NOT NULL,
  status           TEXT        NOT NULL DEFAULT 'success'
                   CHECK (status IN ('success', 'error')),
  response_time_ms INT,
  metadata         JSONB       NOT NULL DEFAULT '{}',
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_bot_analytics_action  ON public.bot_analytics(action);
CREATE INDEX IF NOT EXISTS idx_bot_analytics_created ON public.bot_analytics(created_at);
CREATE INDEX IF NOT EXISTS idx_bot_analytics_session ON public.bot_analytics(session_id) WHERE session_id IS NOT NULL;

ALTER TABLE public.bot_analytics ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON public.bot_analytics;
CREATE POLICY "service_role_all" ON public.bot_analytics
  FOR ALL TO service_role USING (true) WITH CHECK (true);

GRANT ALL ON TABLE public.bot_analytics TO service_role;
REVOKE ALL ON TABLE public.bot_analytics FROM anon;

GRANT EXECUTE ON FUNCTION public.log_bot_action(TEXT, TEXT, TEXT, INT, JSONB) TO service_role;
REVOKE ALL ON FUNCTION public.log_bot_action(TEXT, TEXT, TEXT, INT, JSONB) FROM anon;


-- ══════════════════════════════════════════════════════════════════════════════
-- 8. drop_client_schema — Rollback: borra schema aislado de un cliente
--    Llamado por: n8n Rollback Provisioning (automático al fallar provisioning)
--    POST /rest/v1/rpc/drop_client_schema
--    { "p_slug": "mi-negocio", "p_confirm": "DROP_mi-negocio" }
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.drop_client_schema(
  p_slug    TEXT,
  p_confirm TEXT  -- debe ser exactamente 'DROP_' || p_slug
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_schema  TEXT := 'c_' || replace(p_slug, '-', '_');
  v_existed BOOLEAN;
BEGIN
  IF p_confirm IS DISTINCT FROM ('DROP_' || p_slug) THEN
    RAISE EXCEPTION 'Safety: p_confirm must be DROP_%', p_slug;
  END IF;

  SELECT EXISTS (
    SELECT 1 FROM information_schema.schemata WHERE schema_name = v_schema
  ) INTO v_existed;

  IF v_existed THEN
    EXECUTE format('DROP SCHEMA %I CASCADE', v_schema);
  END IF;

  RETURN jsonb_build_object(
    'schema',  v_schema,
    'dropped', v_existed
  );
END;
$$;


-- ══════════════════════════════════════════════════════════════════════════════
-- 9. seed_services — Inserta servicios/paquetes en el schema del cliente
--    Llamado por: n8n Provisioning Pipeline (nodo "Sembrar Tabla Servicios")
--    POST /rest/v1/rpc/seed_services
--    { "p_slug": "mi-negocio", "p_services": [{"name":"...","price":100,...}] }
-- ══════════════════════════════════════════════════════════════════════════════
CREATE OR REPLACE FUNCTION public.seed_services(
  p_slug     TEXT,
  p_services JSONB
)
RETURNS JSONB
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_schema TEXT := 'c_' || replace(p_slug, '-', '_');
  v_count  INT;
BEGIN
  EXECUTE format(
    'INSERT INTO %I.services (name, price, category, active, sort_order)
     SELECT
       s->>''name'',
       (s->>''price'')::numeric,
       s->>''category'',
       COALESCE((s->>''active'')::boolean, true),
       COALESCE((s->>''sort_order'')::int, 0)
     FROM jsonb_array_elements($1) AS s
     ON CONFLICT DO NOTHING',
    v_schema
  ) USING p_services;

  GET DIAGNOSTICS v_count = ROW_COUNT;

  RETURN jsonb_build_object(
    'seeded', v_count,
    'schema', v_schema
  );
END;
$$;


-- ══════════════════════════════════════════════════════════════════════════════
-- FIN DEL BOOTSTRAP
-- ══════════════════════════════════════════════════════════════════════════════
-- Si todo ejecuto correctamente, el proyecto Supabase del cliente esta listo.
-- Siguiente paso: ejecutar el pipeline de provisioning desde n8n.
