Spaces:
Running
Running
| CREATE TABLE IF NOT EXISTS artists ( | |
| id TEXT PRIMARY KEY, | |
| name TEXT NOT NULL, | |
| spotify_id TEXT, | |
| youtube_channel_id TEXT, | |
| genre TEXT, | |
| region TEXT DEFAULT 'India', | |
| image_url TEXT, | |
| is_watched BOOLEAN DEFAULT 0, | |
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| CREATE TABLE IF NOT EXISTS songs ( | |
| id TEXT PRIMARY KEY, | |
| artist_id TEXT REFERENCES artists(id), | |
| title TEXT NOT NULL, | |
| platform TEXT NOT NULL, | |
| platform_id TEXT NOT NULL, | |
| album_name TEXT, | |
| release_date TEXT, | |
| thumbnail_url TEXT, | |
| sentiment_score REAL, | |
| sentiment_summary TEXT, | |
| themes TEXT, | |
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP, | |
| UNIQUE(platform, platform_id) | |
| ); | |
| CREATE TABLE IF NOT EXISTS play_snapshots ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| song_id TEXT REFERENCES songs(id), | |
| collected_at DATETIME DEFAULT CURRENT_TIMESTAMP, | |
| play_count INTEGER, | |
| like_count INTEGER DEFAULT 0, | |
| dislike_count INTEGER DEFAULT 0, | |
| comment_count INTEGER DEFAULT 0, | |
| ytmusic_play_count INTEGER, | |
| platform TEXT NOT NULL, | |
| cycle_id TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS viral_alerts ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| song_id TEXT REFERENCES songs(id), | |
| detected_at DATETIME DEFAULT CURRENT_TIMESTAMP, | |
| previous_count INTEGER, | |
| current_count INTEGER, | |
| growth_factor REAL, | |
| platform TEXT NOT NULL, | |
| status TEXT DEFAULT 'new' | |
| ); | |
| CREATE TABLE IF NOT EXISTS viral_predictions ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| song_id TEXT REFERENCES songs(id), | |
| prediction_date DATETIME DEFAULT CURRENT_TIMESTAMP, | |
| is_viral_candidate BOOLEAN, | |
| confidence_score REAL, | |
| reasoning TEXT | |
| ); | |
| CREATE TABLE IF NOT EXISTS suggested_artists ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| name TEXT NOT NULL, | |
| reason TEXT, | |
| source TEXT, | |
| status TEXT DEFAULT 'pending', | |
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP, | |
| UNIQUE(name) | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_songs_artist ON songs(artist_id); | |
| CREATE INDEX IF NOT EXISTS idx_songs_platform ON songs(platform); | |
| CREATE INDEX IF NOT EXISTS idx_snapshots_song ON play_snapshots(song_id, collected_at); | |
| CREATE INDEX IF NOT EXISTS idx_alerts_status ON viral_alerts(status); | |
| CREATE INDEX IF NOT EXISTS idx_songs_release ON songs(release_date); | |
| CREATE TABLE IF NOT EXISTS api_quota_log ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| api_name TEXT NOT NULL DEFAULT 'youtube_data_v3', | |
| operation TEXT NOT NULL, | |
| units_used INTEGER NOT NULL, | |
| details TEXT, | |
| created_at DATETIME DEFAULT CURRENT_TIMESTAMP | |
| ); | |