Spaces:
Running
Running
File size: 2,449 Bytes
892cb85 b4c99ff e901791 892cb85 b4c99ff 892cb85 956699f e901791 892cb85 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | 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
);
|