Spaces:

Therdpoom
/

entgroup-sentiment

Sleeping

entgroup-sentiment / supabase_migration.sql

Upload 3 files

fd9025a verified 5 months ago

3.67 kB

-- ══════════════════════════════════════════════════════════════════
--  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;

	-- ══════════════════════════════════════════════════════════════════
	-- 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;