DO $$
DECLARE
  target_schema text;
BEGIN
  FOREACH target_schema IN ARRAY ARRAY['coco', 'toto']
  LOOP
    EXECUTE format(
      $sql$
      CREATE TABLE IF NOT EXISTS %1$I.memory_tool_usage (
        id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        tool_name text NOT NULL,
        "timestamp" timestamptz NOT NULL DEFAULT now(),
        latency_ms integer NOT NULL DEFAULT 0 CHECK (latency_ms >= 0),
        tokens_estimate integer NOT NULL DEFAULT 0 CHECK (tokens_estimate >= 0),
        agent_body text NOT NULL DEFAULT ''
      )
      $sql$,
      target_schema
    );

    EXECUTE format(
      'CREATE INDEX IF NOT EXISTS idx_memory_tool_usage_timestamp ON %I.memory_tool_usage ("timestamp" DESC)',
      target_schema
    );
    EXECUTE format(
      'CREATE INDEX IF NOT EXISTS idx_memory_tool_usage_tool_timestamp ON %I.memory_tool_usage (tool_name, "timestamp" DESC)',
      target_schema
    );
    EXECUTE format(
      'CREATE INDEX IF NOT EXISTS idx_memory_tool_usage_agent_body_timestamp ON %I.memory_tool_usage (agent_body, "timestamp" DESC)',
      target_schema
    );

    EXECUTE format(
      $fn$
      CREATE OR REPLACE FUNCTION %1$I.memory_tool_usage_daily_summary(
        p_date date DEFAULT (timezone('UTC', now()))::date
      )
      RETURNS TABLE (
        date date,
        recall_count integer,
        insert_count integer,
        ingest_count integer,
        total_count integer
      )
      LANGUAGE sql
      STABLE
      AS $body$
        WITH bounds AS (
          SELECT
            (p_date::timestamp AT TIME ZONE 'UTC') AS start_ts,
            ((p_date + 1)::timestamp AT TIME ZONE 'UTC') AS end_ts
        )
        SELECT
          p_date AS date,
          COUNT(*) FILTER (WHERE u.tool_name = 'recall')::integer AS recall_count,
          COUNT(*) FILTER (WHERE u.tool_name = 'insert_memory')::integer AS insert_count,
          COUNT(*) FILTER (
            WHERE u.tool_name IN (
              'dream_ingest',
              'memory_ingest',
              'coco_memory_ingest',
              'toto_memory_ingest',
              'ingest_marsvault_digest'
            )
          )::integer AS ingest_count,
          COUNT(*)::integer AS total_count
        FROM %1$I.memory_tool_usage u
        CROSS JOIN bounds
        WHERE u."timestamp" >= bounds.start_ts
          AND u."timestamp" < bounds.end_ts;
      $body$;
      $fn$,
      target_schema
    );

    EXECUTE format(
      'GRANT SELECT, INSERT ON TABLE %I.memory_tool_usage TO service_role',
      target_schema
    );
    EXECUTE format(
      'GRANT USAGE, SELECT ON SEQUENCE %I.memory_tool_usage_id_seq TO service_role',
      target_schema
    );
    EXECUTE format(
      'GRANT EXECUTE ON FUNCTION %1$I.memory_tool_usage_daily_summary(date) TO service_role',
      target_schema
    );
  END LOOP;
END
$$;
