complete-schema.sql

-- Complete Supabase Schema for YouTube Oracle
-- Run this entire file in your Supabase SQL Editor at:
-- Run this in your Supabase project's SQL Editor

-- ============================================================================
-- MAIN SCHEMA (videos, minting, etc)
-- ============================================================================

-- 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()
);

-- ============================================================================
-- PLAYLIST TRACKING SCHEMA
-- ============================================================================

-- Create playlists table to track playlist stats
CREATE TABLE IF NOT EXISTS playlists (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  channel_id TEXT NOT NULL,
  channel_title TEXT,
  video_count INTEGER DEFAULT 0,
  total_views BIGINT DEFAULT 0,
  average_views BIGINT DEFAULT 0,
  thumbnail_url TEXT,
  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 playlist_images table to store generated playlist images
CREATE TABLE IF NOT EXISTS playlist_images (
  id SERIAL PRIMARY KEY,
  playlist_id TEXT NOT NULL REFERENCES playlists(id),
  image_url TEXT NOT NULL,
  style TEXT, -- 'grid', 'collage', etc.
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================================
-- NFT MINTING SCHEMA
-- ============================================================================

-- Create nft_mints table to track all NFT mints
CREATE TABLE IF NOT EXISTS nft_mints (
  id SERIAL PRIMARY KEY,
  video_id TEXT NOT NULL,
  video_title TEXT NOT NULL,
  token_id TEXT NOT NULL UNIQUE,
  tx_hash TEXT NOT NULL,
  recipient_address TEXT NOT NULL,
  metadata_uri TEXT,
  thumbnail_url TEXT,
  views_at_mint BIGINT,
  likes_at_mint BIGINT,
  minted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create holders table to track NFT holders
CREATE TABLE IF NOT EXISTS holders (
  id SERIAL PRIMARY KEY,
  wallet_address TEXT NOT NULL UNIQUE,
  nft_count INTEGER DEFAULT 0,
  total_value BIGINT DEFAULT 0,
  first_mint_at TIMESTAMP WITH TIME ZONE,
  last_mint_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================================
-- HOLDER AIRDROP SCHEMA
-- ============================================================================

-- Create airdrops table to track all airdrops
CREATE TABLE IF NOT EXISTS airdrops (
  id SERIAL PRIMARY KEY,
  campaign_name TEXT NOT NULL,
  description TEXT,
  token_id TEXT,
  amount_per_holder BIGINT NOT NULL,
  total_holders INTEGER NOT NULL,
  total_amount BIGINT NOT NULL,
  tx_hash TEXT NOT NULL,
  status TEXT DEFAULT 'pending', -- 'pending', 'processing', 'completed', 'failed'
  error_message TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE
);

-- ============================================================================
-- 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);
CREATE INDEX IF NOT EXISTS idx_playlists_last_checked ON playlists(last_checked_at);
CREATE INDEX IF NOT EXISTS idx_playlists_channel_id ON playlists(channel_id);
CREATE INDEX IF NOT EXISTS idx_playlist_images_playlist_id ON playlist_images(playlist_id);
CREATE INDEX IF NOT EXISTS idx_nft_mints_video_id ON nft_mints(video_id);
CREATE INDEX IF NOT EXISTS idx_nft_mints_recipient ON nft_mints(recipient_address);
CREATE INDEX IF NOT EXISTS idx_nft_mints_token_id ON nft_mints(token_id);
CREATE INDEX IF NOT EXISTS idx_holders_wallet ON holders(wallet_address);
CREATE INDEX IF NOT EXISTS idx_airdrops_status ON airdrops(status);
CREATE INDEX IF NOT EXISTS idx_airdrops_created_at ON airdrops(created_at);

-- ============================================================================
-- INITIAL DATA
-- ============================================================================

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

-- ============================================================================
-- TRIGGERS AND FUNCTIONS
-- ============================================================================

-- 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
DROP TRIGGER IF EXISTS update_videos_updated_at ON videos;
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
DROP TRIGGER IF EXISTS update_oracle_state_updated_at ON oracle_state;
CREATE TRIGGER update_oracle_state_updated_at BEFORE UPDATE ON oracle_state
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create trigger for playlists table
DROP TRIGGER IF EXISTS update_playlists_updated_at ON playlists;
CREATE TRIGGER update_playlists_updated_at BEFORE UPDATE ON playlists
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create trigger for holders table
DROP TRIGGER IF EXISTS update_holders_updated_at ON holders;
CREATE TRIGGER update_holders_updated_at BEFORE UPDATE ON holders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================================
-- ENABLE ROW LEVEL SECURITY (Optional - uncomment if needed)
-- ============================================================================

-- ALTER TABLE videos ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE channel_stats ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE minting_events ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE thumbnails ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE oracle_state ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE playlists ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE playlist_images ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE nft_mints ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE holders ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE airdrops ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- DONE! All tables created successfully
-- ============================================================================

SELECT 'Schema setup complete! All tables created.' AS status;