-- ══════════════════════════════════════════════════════════════════
--  Supabase Migration — entgroup-sentiment patch v3.1
--  รันใน Supabase Dashboard → SQL Editor ก่อน git push app.py
-- ══════════════════════════════════════════════════════════════════

-- ── 1. admin_activity table (PATCH 3b) ──────────────────────────
--  แทน in-memory _last_admin_activity ที่หายเมื่อ Space restart
CREATE TABLE IF NOT EXISTS admin_activity (
    id              SERIAL PRIMARY KEY,
    activity_key    TEXT NOT NULL UNIQUE,   -- 'global' หรือ customer_id
    last_active_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_admin_activity_key ON admin_activity(activity_key);

-- ── 2. RPC: increment_daily_stats (PATCH 3) ──────────────────────
--  atomic server-side increment ป้องกัน race condition
--  เรียกจาก Python: sb_post("/rest/v1/rpc/increment_daily_stats", {...})
CREATE OR REPLACE FUNCTION increment_daily_stats(
    p_date          DATE,
    p_is_new        BOOLEAN,
    p_customer_type TEXT
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO daily_stats (
        date, total_customers, new_customers, returning_customers,
        si_count, end_user_count, unknown_count, total_messages
    ) VALUES (
        p_date, 1,
        CASE WHEN p_is_new          THEN 1 ELSE 0 END,
        CASE WHEN NOT p_is_new      THEN 1 ELSE 0 END,
        CASE WHEN p_customer_type = 'si'       THEN 1 ELSE 0 END,
        CASE WHEN p_customer_type = 'end_user' THEN 1 ELSE 0 END,
        CASE WHEN p_customer_type = 'unknown'  THEN 1 ELSE 0 END,
        1
    )
    ON CONFLICT (date) DO UPDATE SET
        total_customers     = daily_stats.total_customers     + 1,
        new_customers       = daily_stats.new_customers       + CASE WHEN p_is_new          THEN 1 ELSE 0 END,
        returning_customers = daily_stats.returning_customers + CASE WHEN NOT p_is_new      THEN 1 ELSE 0 END,
        si_count            = daily_stats.si_count            + CASE WHEN p_customer_type = 'si'       THEN 1 ELSE 0 END,
        end_user_count      = daily_stats.end_user_count      + CASE WHEN p_customer_type = 'end_user' THEN 1 ELSE 0 END,
        unknown_count       = daily_stats.unknown_count       + CASE WHEN p_customer_type = 'unknown'  THEN 1 ELSE 0 END,
        total_messages      = daily_stats.total_messages      + 1;
END;
$$;

-- Grant execute ให้ anon role (ใช้ Supabase key ปกติ)
GRANT EXECUTE ON FUNCTION increment_daily_stats TO anon, authenticated, service_role;

-- ── 3. ตรวจสอบ daily_stats มี unique constraint บน date ──────────
--  RPC ใช้ ON CONFLICT (date) → ต้องมี unique index
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'daily_stats_date_key'
    ) THEN
        ALTER TABLE daily_stats ADD CONSTRAINT daily_stats_date_key UNIQUE (date);
    END IF;
END $$;

-- ── Done ──────────────────────────────────────────────────────────
SELECT 'Migration complete: admin_activity table + increment_daily_stats RPC' AS result;