supabase-schema.sql

-- Supabase Database Schema for YouTube Oracle
-- Run this in your Supabase SQL Editor

-- Create videos table to track view counts and thumbnails
CREATE TABLE IF NOT EXISTS videos (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  views BIGINT NOT NULL DEFAULT 0,
  likes BIGINT DEFAULT 0,
  comments BIGINT DEFAULT 0,
  thumbnail_url TEXT,
  thumbnail_downloaded BOOLEAN DEFAULT FALSE,
  last_checked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create channel_stats table to track subscriber counts
CREATE TABLE IF NOT EXISTS channel_stats (
  id SERIAL PRIMARY KEY,
  channel_id TEXT NOT NULL,
  subscriber_count BIGINT NOT NULL,
  view_count BIGINT DEFAULT 0,
  video_count INTEGER DEFAULT 0,
  recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create minting_events table to log all token mints
CREATE TABLE IF NOT EXISTS minting_events (
  id SERIAL PRIMARY KEY,
  event_type TEXT NOT NULL, -- 'views' or 'subscribers'
  video_id TEXT,
  delta INTEGER NOT NULL,
  tokens_minted BIGINT NOT NULL,
  tx_hash TEXT NOT NULL,
  wallet_address TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create thumbnails table to store thumbnail metadata
CREATE TABLE IF NOT EXISTS thumbnails (
  id SERIAL PRIMARY KEY,
  video_id TEXT NOT NULL REFERENCES videos(id),
  url TEXT NOT NULL,
  width INTEGER,
  height INTEGER,
  quality TEXT, -- 'default', 'medium', 'high', 'maxres'
  downloaded BOOLEAN DEFAULT FALSE,
  file_path TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create oracle_state table to track quota and state
CREATE TABLE IF NOT EXISTS oracle_state (
  id SERIAL PRIMARY KEY,
  key TEXT UNIQUE NOT NULL,
  value TEXT NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add indexes for performance
CREATE INDEX IF NOT EXISTS idx_videos_last_checked ON videos(last_checked_at);
CREATE INDEX IF NOT EXISTS idx_channel_stats_channel_id ON channel_stats(channel_id);
CREATE INDEX IF NOT EXISTS idx_channel_stats_recorded_at ON channel_stats(recorded_at);
CREATE INDEX IF NOT EXISTS idx_minting_events_video_id ON minting_events(video_id);
CREATE INDEX IF NOT EXISTS idx_minting_events_created_at ON minting_events(created_at);
CREATE INDEX IF NOT EXISTS idx_thumbnails_video_id ON thumbnails(video_id);

-- Insert initial oracle state
INSERT INTO oracle_state (key, value) 
VALUES ('quota_used', '0'), ('last_reset', NOW()::TEXT)
ON CONFLICT (key) DO NOTHING;

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger for videos table
CREATE TRIGGER update_videos_updated_at BEFORE UPDATE ON videos
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create trigger for oracle_state table
CREATE TRIGGER update_oracle_state_updated_at BEFORE UPDATE ON oracle_state
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();