DROP TABLE IF EXISTS lectures;
DROP TABLE IF EXISTS series;

CREATE TABLE series (
    series_id INTEGER PRIMARY KEY AUTOINCREMENT,
    series_title TEXT NOT NULL,
    external_playlist_id TEXT UNIQUE,
    difficulty TEXT
);

CREATE TABLE lectures (
    lecture_id INTEGER PRIMARY KEY AUTOINCREMENT,
    series_id INTEGER,
    title TEXT NOT NULL,
    video_id TEXT UNIQUE,
    transcription_status INTEGER DEFAULT 0,
    FOREIGN KEY (series_id) REFERENCES series (series_id)
);

INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('From the Islam of Hadith to the Islam of the Qur''an', 'PL2lEKHPpJo6mXlNACb-JVhf_lPiHP925n', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL2lEKHPpJo6mXlNACb-JVhf_lPiHP925n'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Holy Qur''an in the Hadith of the Messenger of God', 'PL2lEKHPpJo6mzzG_94n5P-vF5IGYsZazc', 'Unknown');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL2lEKHPpJo6mzzG_94n5P-vF5IGYsZazc'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Stations of the Chosen One (PBUH)', 'PL2lEKHPpJo6ka7k9Smt7g6Wj0MKilL5sc', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL2lEKHPpJo6ka7k9Smt7g6Wj0MKilL5sc'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Imamate in the Holy Qur''an', 'PL0FCC3F8153160D36', 'Advanced');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL0FCC3F8153160D36'), 'Lecture 1', 'c3-UaycuDIg');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL0FCC3F8153160D36'), 'Lecture 2', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Day of Resurrection', 'PLC96DE46686B44FE9', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLC96DE46686B44FE9'), 'Lecture 1', 'enpHvfGKmm8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLC96DE46686B44FE9'), 'Lecture 2', 'oH6NmXziDZQ');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Greatest Messenger in Islamic Heritage', 'PL5A965EC4789E1AFD', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL5A965EC4789E1AFD'), 'Lecture 1', 'M8ZCYRISRGI');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL5A965EC4789E1AFD'), 'Lecture 2', 'tmba3tWWWac');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Religious Authority and the Caliphate After the Messenger of God', 'PLD41CD7B6C08ACD22', 'Advanced');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLD41CD7B6C08ACD22'), 'Lecture 1', 'OpcOlt-mJxo');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Features of Umayyad Islam', 'PL6D6AD5D75DCE1871', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL6D6AD5D75DCE1871'), 'Lecture 1', 'tmba3tWWWac');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL6D6AD5D75DCE1871'), 'Lecture 2', 'oH6NmXziDZQ');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Short Miscellany', 'PL74EAAFBAD91C2996', 'Supplementary');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL74EAAFBAD91C2996'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Advanced Lessons in Fiqh', 'PL0A7C96862A304D32', 'Advanced');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL0A7C96862A304D32'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Advanced Lessons in Usul al-Fiqh', 'PLCF57CD0D603F43E7', 'Advanced');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLCF57CD0D603F43E7'), 'Lecture 1', 'enpHvfGKmm8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLCF57CD0D603F43E7'), 'Lecture 2', '4aIJ3MIbvJE');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLCF57CD0D603F43E7'), 'Lecture 3', 'Rf75uztL23s');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Lessons in Justice', 'PLBF68B223E039AF88', 'Supplementary');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLBF68B223E039AF88'), 'Lecture 1', 'enpHvfGKmm8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLBF68B223E039AF88'), 'Lecture 2', '4aIJ3MIbvJE');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Lessons', 'PLE333691A1940D77E', 'Supplementary');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLE333691A1940D77E'), 'Lecture 1', 'enpHvfGKmm8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLE333691A1940D77E'), 'Lecture 2', 'oH6NmXziDZQ');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Lectures', 'PLA5C60046D003427F', 'Supplementary');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLA5C60046D003427F'), 'lectures - Placeholder', 'placeholder_PLA5C60046D003427F');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Lessons in Qur''anic Exegesis', 'PLD7BFB458853CC1BA', 'Foundation');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLD7BFB458853CC1BA'), 'Lecture 1', 'enpHvfGKmm8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLD7BFB458853CC1BA'), 'Lecture 2', 'oH6NmXziDZQ');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLD7BFB458853CC1BA'), 'Lecture 3', '4aIJ3MIbvJE');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Theory of the Uprightness of the Companions', 'PL95D3B1B16480981C', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL95D3B1B16480981C'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Monotheism / Divine Unity', 'PL70F77A611C2C23BE', 'Foundation');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL70F77A611C2C23BE'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Knowing God', 'PL13FA82BEFE502452', 'Foundation');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL13FA82BEFE502452'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Afterlife in the Holy Qur''an', 'PLFA97FB44CEEE526A', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLFA97FB44CEEE526A'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Miscellaneous Researches', 'PL098F9F4772A72008', 'Supplementary');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL098F9F4772A72008'), 'Lecture 1', 'enpHvfGKmm8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL098F9F4772A72008'), 'Lecture 2', 'gmmVEI6AuM8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Face to Face', 'PL78CD0B1BFBCF96D4', 'Supplementary');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL78CD0B1BFBCF96D4'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL78CD0B1BFBCF96D4'), 'Lecture 2', 'k7t4KsCTif8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Qur''anic Reflections', 'PLDF0060D2A56A5B94', 'Foundation');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLDF0060D2A56A5B94'), 'qur’anic reflections - Placeholder', 'placeholder_PLDF0060D2A56A5B94');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Qur''anic Terminology', 'PL35259F7DC0C01B39', 'Foundation');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL35259F7DC0C01B39'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL35259F7DC0C01B39'), 'Lecture 2', 'hKivTGS2wpc');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Monotheism in Nahj al-Balagha', 'PL1E01FC8C67E8ADBE', 'Foundation');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL1E01FC8C67E8ADBE'), 'Lecture 1', 'c3-UaycuDIg');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PL1E01FC8C67E8ADBE'), 'Lecture 2', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Mahdist Thesis', 'PLCC324BA5D8B7D146', 'Advanced');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLCC324BA5D8B7D146'), 'Lecture 1', 'dJGV2c8aNZs');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Reflections on Modernity and Postmodernity', 'PLHASXv5O7U5-fYZKq4o65gEz3RsRbmdbO', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLHASXv5O7U5-fYZKq4o65gEz3RsRbmdbO'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Rule of Grace: Presentation and Critique', 'PLHASXv5O7U5-0Wai2BSTf59oQPxQv0kzv', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLHASXv5O7U5-0Wai2BSTf59oQPxQv0kzv'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('The Logic of Understanding the Husayni Uprising', 'PLHASXv5O7U58yW0OPYf--lYs7kKfstxDT', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLHASXv5O7U58yW0OPYf--lYs7kKfstxDT'), 'Lecture 1', 'ZUFiwgW12d8');
INSERT INTO series (series_title, external_playlist_id, difficulty) VALUES ('Debates on the Renewal of Religious Thought', 'PLHASXv5O7U58SpDlmnVKieq_NQYhzwNJK', 'Intermediate');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLHASXv5O7U58SpDlmnVKieq_NQYhzwNJK'), 'Lecture 1', 'V-QMwJRLPj0');
INSERT OR IGNORE INTO lectures (series_id, title, video_id) VALUES ((SELECT series_id FROM series WHERE external_playlist_id = 'PLHASXv5O7U58SpDlmnVKieq_NQYhzwNJK'), 'Lecture 2', 'ZUFiwgW12d8');
