-- =====================================================
-- CannaDeals Marketing Command Center - Database Setup
-- Run this in Supabase SQL Editor
-- =====================================================

-- Keyword tracking
CREATE TABLE IF NOT EXISTS tracked_keywords (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  keyword TEXT NOT NULL UNIQUE,
  category TEXT,
  search_volume INTEGER,
  current_position INTEGER,
  previous_position INTEGER,
  best_position INTEGER,
  first_tracked TIMESTAMPTZ DEFAULT NOW(),
  last_updated TIMESTAMPTZ DEFAULT NOW()
);

-- Keyword history
CREATE TABLE IF NOT EXISTS keyword_rankings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  keyword_id UUID REFERENCES tracked_keywords(id) ON DELETE CASCADE,
  position INTEGER NOT NULL,
  url TEXT,
  impressions INTEGER DEFAULT 0,
  clicks INTEGER DEFAULT 0,
  ctr FLOAT DEFAULT 0,
  recorded_date DATE NOT NULL,
  UNIQUE(keyword_id, recorded_date)
);

-- Social metrics
CREATE TABLE IF NOT EXISTS social_metrics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  platform TEXT NOT NULL,
  metric_type TEXT NOT NULL,
  value INTEGER NOT NULL,
  recorded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Alerts
CREATE TABLE IF NOT EXISTS marketing_alerts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  alert_type TEXT NOT NULL,
  severity TEXT NOT NULL DEFAULT 'info',
  title TEXT NOT NULL,
  description TEXT,
  data JSONB,
  is_read BOOLEAN DEFAULT FALSE,
  is_resolved BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Data collection log
CREATE TABLE IF NOT EXISTS collection_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source TEXT NOT NULL,
  status TEXT NOT NULL,
  records_collected INTEGER DEFAULT 0,
  error_message TEXT,
  started_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_keyword_rankings_date ON keyword_rankings(recorded_date);
CREATE INDEX IF NOT EXISTS idx_keyword_rankings_keyword ON keyword_rankings(keyword_id);
CREATE INDEX IF NOT EXISTS idx_social_metrics_platform ON social_metrics(platform, recorded_at);
CREATE INDEX IF NOT EXISTS idx_marketing_alerts_created ON marketing_alerts(created_at DESC);

-- Insert initial keywords to track
INSERT INTO tracked_keywords (keyword, category, search_volume) VALUES
('florida dispensary deals', 'transactional', 2400),
('dispensary deals near me', 'transactional', 1900),
('florida medical marijuana card', 'informational', 3200),
('mmj card cost florida', 'informational', 880),
('cannabis for ptsd florida', 'condition', 720),
('cannabis for chronic pain florida', 'condition', 880),
('miami dispensary deals', 'local', 1200),
('tampa dispensary deals', 'local', 900),
('orlando dispensary deals', 'local', 800),
('jacksonville dispensary deals', 'local', 500),
('best dispensary florida', 'informational', 1600),
('jungle boys florida', 'brand', 2200),
('the flowery deals', 'brand', 590),
('trulieve deals florida', 'brand', 1800),
('sunburn cannabis deals', 'brand', 390),
('cookies dispensary florida', 'brand', 1100),
('goldflower florida', 'brand', 320),
('florida cannabis news', 'informational', 590),
('how to get medical card florida', 'informational', 1400),
('qualifying conditions florida mmj', 'informational', 720)
ON CONFLICT (keyword) DO NOTHING;

-- Enable RLS
ALTER TABLE tracked_keywords ENABLE ROW LEVEL SECURITY;
ALTER TABLE keyword_rankings ENABLE ROW LEVEL SECURITY;
ALTER TABLE social_metrics ENABLE ROW LEVEL SECURITY;
ALTER TABLE marketing_alerts ENABLE ROW LEVEL SECURITY;
ALTER TABLE collection_log ENABLE ROW LEVEL SECURITY;

-- Create policies (service role has full access)
CREATE POLICY "Service role full access" ON tracked_keywords FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON keyword_rankings FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON social_metrics FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON marketing_alerts FOR ALL TO service_role USING (true) WITH CHECK (true);
CREATE POLICY "Service role full access" ON collection_log FOR ALL TO service_role USING (true) WITH CHECK (true);
