-- Migration 017: Create ADR-012 Agent Action Ladder tables
-- Tables: agent_context, action_plans, action_outcomes, agent_strategy_weights
-- These are the core multi-agent shared-context and closed-loop learning tables.
-- Author: Antigravity / ADR-012

-- 1. Agent shared context (TTL-aware, cross-agent access)
CREATE TABLE IF NOT EXISTS agent_context (
    id              SERIAL PRIMARY KEY,
    session_id      VARCHAR(64)  NOT NULL,
    agent_name      VARCHAR(50)  NOT NULL,
    context_key     VARCHAR(100) NOT NULL,
    context_val     TEXT,
    created_at      TIMESTAMP    DEFAULT NOW(),
    ttl_minutes     INTEGER      DEFAULT 60
);
CREATE INDEX IF NOT EXISTS idx_agent_context_session_key
    ON agent_context (session_id, agent_name, context_key);
CREATE INDEX IF NOT EXISTS idx_agent_context_session_ttl
    ON agent_context (session_id, created_at);

-- 2. Action plans (NemoTron output, pending review & execution tracking)
CREATE TABLE IF NOT EXISTS action_plans (
    id          SERIAL PRIMARY KEY,
    session_id  VARCHAR(64),
    plan_type   VARCHAR(50),       -- price_adjust / restock / campaign
    sku         VARCHAR(100),
    payload     TEXT,              -- JSON action payload
    status      VARCHAR(20)  DEFAULT 'pending',  -- pending/approved/rejected/executed
    created_by  VARCHAR(50),       -- nemotron / openclaw
    approved_by VARCHAR(100),      -- Telegram user_id
    created_at  TIMESTAMP    DEFAULT NOW(),
    executed_at TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_action_plan_sku_status ON action_plans (sku, status);
CREATE INDEX IF NOT EXISTS idx_action_plan_created    ON action_plans (created_at);

-- 3. Action outcomes (closed-loop learning core)
CREATE TABLE IF NOT EXISTS action_outcomes (
    id          SERIAL PRIMARY KEY,
    plan_id     INTEGER      NOT NULL REFERENCES action_plans(id),
    metric_type VARCHAR(50),       -- sales_7d / price_rank / conversion
    before_val  FLOAT,
    after_val   FLOAT,
    measured_at TIMESTAMP,
    verdict     VARCHAR(20),       -- effective / neutral / backfired
    created_at  TIMESTAMP    DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_action_outcome_plan ON action_outcomes (plan_id);

-- 4. Agent strategy weights (OpenClaw learning accumulation)
CREATE TABLE IF NOT EXISTS agent_strategy_weights (
    id           SERIAL PRIMARY KEY,
    strategy_key VARCHAR(100) UNIQUE NOT NULL,  -- e.g. price_cut_when_gap_gt_5pct
    weight       FLOAT        DEFAULT 1.0,
    success_cnt  INTEGER      DEFAULT 0,
    fail_cnt     INTEGER      DEFAULT 0,
    updated_at   TIMESTAMP    DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_strategy_key ON agent_strategy_weights (strategy_key);

DO $$
BEGIN
    RAISE NOTICE 'Migration 017 done: agent_context / action_plans / action_outcomes / agent_strategy_weights';
END $$;