Hugging Face's logo

Spaces:
Therdpoom
/
entgroup-sentiment
Sleeping

App Files Community
entgroup-sentiment
File size: 3,667 Bytes
fd9025a
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- ══════════════════════════════════════════════════════════════════
--  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;