-- =====================================================
-- WISDOM SYSTEM MIGRATION
-- Contextual AI-powered coaching for sales dashboard
-- =====================================================

-- Table: wisdom
-- Stores wisdom messages (AI-generated and curated fallbacks)
CREATE TABLE IF NOT EXISTS wisdom (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  message TEXT NOT NULL,
  expanded TEXT,  -- Longer explanation for tap-to-expand
  context_trigger TEXT NOT NULL,
  source TEXT DEFAULT 'ai' CHECK (source IN ('ai', 'curated')),
  thumbs_up INTEGER DEFAULT 0,
  thumbs_down INTEGER DEFAULT 0,
  times_shown INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for wisdom
CREATE INDEX IF NOT EXISTS idx_wisdom_trigger ON wisdom(context_trigger);
CREATE INDEX IF NOT EXISTS idx_wisdom_source ON wisdom(source);

-- Table: wisdom_shown
-- Tracks what wisdom has been shown to whom (for analytics and avoiding repeats)
CREATE TABLE IF NOT EXISTS wisdom_shown (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  wisdom_id UUID REFERENCES wisdom(id) ON DELETE CASCADE,
  context_snapshot JSONB,  -- Store context at time of showing
  shown_at TIMESTAMPTZ DEFAULT NOW(),
  rating TEXT CHECK (rating IN ('up', 'down'))
);

-- Indexes for wisdom_shown
CREATE INDEX IF NOT EXISTS idx_wisdom_shown_user ON wisdom_shown(user_id, shown_at DESC);
CREATE INDEX IF NOT EXISTS idx_wisdom_shown_wisdom ON wisdom_shown(wisdom_id);

-- =====================================================
-- CURATED FALLBACK WISDOM (per context trigger)
-- =====================================================

-- streak_strong (5+ day streak)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('You''re not building a streak. You''re building a reputation.',
 'Consistency compounds. Every day you show up, you''re proving to yourself and your pipeline that you''re the kind of rep who doesn''t quit. That reputation follows you into every conversation.',
 'streak_strong', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Streaks don''t happen by accident. Neither do closed deals.',
 'The discipline that built this streak is the same discipline that builds pipeline. You''ve proven you can show up. Now watch the results compound.',
 'streak_strong', 'curated');

-- streak_broken (no calls in 3+ days)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('The phone gets heavier every day you don''t pick it up.',
 'Avoidance creates anxiety. The longer you wait, the harder it feels. But here''s the truth: the first call breaks the spell. Just one dial and you''re back in the game.',
 'streak_broken', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Every day you don''t call, someone else is calling your leads.',
 'Your pipeline doesn''t pause because you did. Those leads are getting calls from competitors right now. The question is whether you''re going to let them.',
 'streak_broken', 'curated');

-- zero_calls (0 calls after 12pm)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Half the day gone, zero dials. One call changes the energy.',
 'Starting is the hardest part. Your brain is making it bigger than it is. Pick up the phone, make one call, and watch how quickly the momentum builds.',
 'zero_calls', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Zero calls isn''t a slow day. It''s a choice you''re making right now.',
 'Every hour that passes, you''re actively choosing not to call. That''s fine if it''s intentional. But if you want deals, you need dials. Simple as that.',
 'zero_calls', 'curated');

-- behind_target (< 50% of target after 2pm)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Clock''s ticking. But you only need one good conversation.',
 'Targets aren''t about quantity. They''re about getting enough at-bats. One connected call can change your whole day. Focus on the next dial, not the number.',
 'behind_target', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Behind target with hours left. This is where reps are made.',
 'Anyone can hit numbers when it''s easy. The reps who make it are the ones who dig in when they''re behind. This afternoon matters.',
 'behind_target', 'curated');

-- overdue_callbacks (2+ overdue)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('They already said yes once. Don''t make them regret it.',
 'A callback isn''t a cold call. They''re expecting you. They want to hear from you. Every hour you delay, they''re wondering if you''re the right person to work with.',
 'overdue_callbacks', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('People are literally waiting for your call. How many reps can say that?',
 'Most salespeople would kill for warm leads who asked to be called back. You have them. Don''t waste the privilege.',
 'overdue_callbacks', 'curated');

-- sms_waiting (2+ SMS waiting)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Someone replied. Fastest path to a deal is someone who''s already engaged.',
 'An inbound reply is the warmest lead you''ll get all day. Don''t let it go cold. Strike while they''re thinking about you.',
 'sms_waiting', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('They texted back. That''s interest. Move on it.',
 'In a world of ghosting and ignored messages, someone took time to reply. That''s not nothing. That''s an open door.',
 'sms_waiting', 'curated');

-- hot_leads_stale (hot leads not contacted 48hrs)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Hot leads go cold. Yours are cooling right now.',
 'A hot lead today is a warm lead tomorrow and a cold lead next week. Speed to contact is the single biggest factor in conversion. Every hour matters.',
 'hot_leads_stale', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('48 hours is a lifetime in sales. Your hot leads are getting colder.',
 'Interest fades fast. The enthusiasm they had two days ago? It''s diminishing with every hour. Call them before they forget why they were interested.',
 'hot_leads_stale', 'curated');

-- friday_push (Friday after 3pm)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Weekend''s calling. But so are your leads. 30 more minutes.',
 'Friday afternoon separates the professionals from the clock-watchers. While everyone else is mentally checked out, you''re stacking next week''s pipeline.',
 'friday_push', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Most reps check out Friday arvo. That''s exactly why you shouldn''t.',
 'The best time to reach people is when no one else is trying. Friday afternoon, your competition has mentally clocked off. Use that.',
 'friday_push', 'curated');

-- monday_start (Monday before 11am)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Fresh week. The tone you set in the first hour carries all week.',
 'Monday morning is a statement. Start slow and you''ll chase all week. Start strong and you''ll cruise. Your choice.',
 'monday_start', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Monday morning energy. This is where winning weeks begin.',
 'How you start sets the pattern. The first few calls of the week establish your rhythm. Make them count.',
 'monday_start', 'curated');

-- post_win (conversion in last 4 hours)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Blood in the water. Confidence peaks after a win. Use it now.',
 'Your voice sounds different after a win. Prospects can hear certainty. This is your window - the next 2-3 calls have the highest conversion probability of your day.',
 'post_win', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('You just closed. Your next calls will be your best calls today.',
 'Post-win energy is real. You''re sharper, more confident, more relaxed. Don''t waste this state scrolling - get back on the phone while you''re in the zone.',
 'post_win', 'curated');

-- winning_streak (2+ wins this week)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Multiple wins this week. You''re in the zone. Don''t stop.',
 'Winning creates winning. Your confidence is high, your pitch is tight, your timing is on. This is the week to go hard.',
 'winning_streak', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('When you''re hot, you''re hot. Keep the pressure on.',
 'Momentum in sales is real. Whatever you''re doing this week is working. Double down, don''t coast.',
 'winning_streak', 'curated');

-- afternoon_slump (2pm-4pm)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('2pm energy dip. This is when average reps coast.',
 'Your competitors are checking emails and planning tomorrow. You''re on the phone. That''s the edge.',
 'afternoon_slump', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('The afternoon slump is real. Push through it and you''re already ahead.',
 'Everyone feels it. The difference is what you do about it. One call. Then another. The energy comes back.',
 'afternoon_slump', 'curated');

-- crushing_it (beat target + win today)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Target smashed. Now show them what extra looks like.',
 'You hit your number. Most would coast. But you''re not most. The difference between good and elite is what you do after you''ve already won.',
 'crushing_it', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Already won the day. Now make it memorable.',
 'Target is the floor, not the ceiling. Today you have permission to see what''s possible when you don''t stop at ''enough''.',
 'crushing_it', 'curated');

-- end_of_day (after 5pm)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Day''s done. Tomorrow starts with momentum or excuses.',
 'How you end today determines how you start tomorrow. Leave something in the chamber - a hot lead to call first thing, a callback scheduled. Give tomorrow-you a head start.',
 'end_of_day', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Clock out with a clear conscience. Did you leave it all out there?',
 'The best feeling is ending the day knowing you did the work. No regrets, no ''should haves''. Just results and effort.',
 'end_of_day', 'curated');

-- general (default fallback)
INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('The money''s in the follow-up. Always has been.',
 'Most deals don''t close on the first call. They close on the fifth, the eighth, the twelfth. The rep who follows up wins.',
 'general', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('Every call is practice. Every conversation is data.',
 'Even the calls that don''t convert are teaching you something. Pay attention. The patterns reveal themselves to those who show up consistently.',
 'general', 'curated');

INSERT INTO wisdom (message, expanded, context_trigger, source) VALUES
('You miss 100% of the follow-ups you don''t make.',
 'The lead that feels ''dead'' might just need one more touch. You''ll never know if you don''t try. Fortune favours the persistent.',
 'general', 'curated');

-- =====================================================
-- DATABASE FUNCTIONS
-- =====================================================

-- Function to increment times_shown
CREATE OR REPLACE FUNCTION increment_wisdom_shown(wisdom_id UUID)
RETURNS void AS $$
BEGIN
  UPDATE wisdom SET times_shown = times_shown + 1 WHERE id = wisdom_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to increment rating
CREATE OR REPLACE FUNCTION increment_wisdom_rating(wisdom_id UUID, rating_type TEXT)
RETURNS void AS $$
BEGIN
  IF rating_type = 'thumbs_up' THEN
    UPDATE wisdom SET thumbs_up = thumbs_up + 1 WHERE id = wisdom_id;
  ELSIF rating_type = 'thumbs_down' THEN
    UPDATE wisdom SET thumbs_down = thumbs_down + 1 WHERE id = wisdom_id;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- =====================================================
-- RLS POLICIES
-- =====================================================

-- Enable RLS
ALTER TABLE wisdom ENABLE ROW LEVEL SECURITY;
ALTER TABLE wisdom_shown ENABLE ROW LEVEL SECURITY;

-- Wisdom table - readable by all authenticated users
CREATE POLICY "Wisdom readable by authenticated users" ON wisdom
  FOR SELECT TO authenticated USING (true);

-- Wisdom_shown - users can only see their own records
CREATE POLICY "Users can view own wisdom_shown" ON wisdom_shown
  FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own wisdom_shown" ON wisdom_shown
  FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own wisdom_shown" ON wisdom_shown
  FOR UPDATE TO authenticated USING (auth.uid() = user_id);

-- Service role can do everything (for backend)
CREATE POLICY "Service role full access wisdom" ON wisdom
  FOR ALL TO service_role USING (true) WITH CHECK (true);

CREATE POLICY "Service role full access wisdom_shown" ON wisdom_shown
  FOR ALL TO service_role USING (true) WITH CHECK (true);
