-- Add paid content fields to reels table
ALTER TABLE reels
ADD COLUMN IF NOT EXISTS is_paid BOOLEAN DEFAULT false,
ADD COLUMN IF NOT EXISTS price DECIMAL(10,2) DEFAULT 0.00;

-- Create purchases table for tracking content purchases
CREATE TABLE IF NOT EXISTS content_purchases (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content_type TEXT NOT NULL CHECK (content_type IN ('post', 'reel')),
  content_id INTEGER NOT NULL,
  amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, content_type, content_id)
);

-- Create indexes
CREATE INDEX IF NOT EXISTS content_purchases_user_id_idx ON content_purchases(user_id);
CREATE INDEX IF NOT EXISTS content_purchases_content_idx ON content_purchases(content_type, content_id);
CREATE INDEX IF NOT EXISTS content_purchases_created_at_idx ON content_purchases(created_at);

-- Enable RLS
ALTER TABLE content_purchases ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view their own purchases"
  ON content_purchases FOR SELECT
  TO authenticated
  USING (user_id = auth.uid()::integer);

CREATE POLICY "Users can create purchases"
  ON content_purchases FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid()::integer);