Setup Tweetalytics

git clone https://github.com/sushiselite/auto-social.git
cd auto-social
npm install
cp env.example .env.local

-- Users table
CREATE TABLE users (
  id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
  username TEXT,
  preferences JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, now()) NOT NULL
);

-- Ideas table
CREATE TABLE ideas (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  content TEXT,
  type TEXT CHECK (type IN ('text', 'voice')),
  audio_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, now()) NOT NULL
);

-- Tweets table
CREATE TABLE tweets (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  idea_id UUID REFERENCES ideas(id) ON DELETE CASCADE,
  content TEXT,
  status TEXT CHECK (status IN ('generated', 'in_review', 'approved', 'published')) DEFAULT 'generated',
  scheduled_time TIMESTAMP WITH TIME ZONE,
  performance JSONB,
  viral_score INTEGER,
  authenticity_score INTEGER,
  engagement_score INTEGER,
  quality_score INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, now()) NOT NULL
);

-- Training examples table
CREATE TABLE training_examples (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  tweet_text TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, now()) NOT NULL
);

-- Tweet feedback table
CREATE TABLE tweet_feedback (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tweet_id UUID REFERENCES tweets(id) ON DELETE CASCADE,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  comment TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, now()) NOT NULL
);

-- Enable Row Level Security
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE ideas ENABLE ROW LEVEL SECURITY;
ALTER TABLE tweets ENABLE ROW LEVEL SECURITY;
ALTER TABLE training_examples ENABLE ROW LEVEL SECURITY;
ALTER TABLE tweet_feedback ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view own data" ON users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own data" ON users FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "Users can insert own data" ON users FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can manage own ideas" ON ideas FOR ALL USING (auth.uid() = user_id);
CREATE POLICY "Users can manage own tweets" ON tweets FOR ALL USING (auth.uid() = user_id);
CREATE POLICY "Users can manage own training examples" ON training_examples FOR ALL USING (auth.uid() = user_id);
CREATE POLICY "Users can manage own feedback" ON tweet_feedback FOR ALL USING (auth.uid() = user_id);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_tweets_viral_score ON tweets(viral_score);
CREATE INDEX IF NOT EXISTS idx_tweets_authenticity_score ON tweets(authenticity_score);
CREATE INDEX IF NOT EXISTS idx_tweets_engagement_score ON tweets(engagement_score);
CREATE INDEX IF NOT EXISTS idx_tweets_quality_score ON tweets(quality_score);

-- Add constraints to ensure scores are within 0-100 range
ALTER TABLE tweets ADD CONSTRAINT viral_score_range CHECK (viral_score >= 0 AND viral_score <= 100);
ALTER TABLE tweets ADD CONSTRAINT authenticity_score_range CHECK (authenticity_score >= 0 AND authenticity_score <= 100);
ALTER TABLE tweets ADD CONSTRAINT engagement_score_range CHECK (engagement_score >= 0 AND engagement_score <= 100);
ALTER TABLE tweets ADD CONSTRAINT quality_score_range CHECK (quality_score >= 0 AND quality_score <= 100);

-- Function to handle user creation
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (id, username, preferences, created_at)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'name', NEW.email),
    '{
      "defaultTone": "professional",
      "timezone": "UTC",
      "notifications": true,
      "autoSchedule": false
    }'::jsonb,
    NOW()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to call the function when a new user signs up
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();