-- This mode allows the use of 0 as a valid ID value in an AUTO_INCREMENT column
SET sql_mode='NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE IF NOT EXISTS `language` (
  `id` int unsigned NOT NULL,
  `shortcode` varchar(10) NOT NULL,
  `display_name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO language VALUES(0,'None','None');
INSERT INTO language VALUES(1,'af','Afrikaans');
INSERT INTO language VALUES(2,'af-ZA','Afrikaans - South Africa');
INSERT INTO language VALUES(3,'sq','Albanian');
INSERT INTO language VALUES(4,'sq-AL','Albanian - Albania');
INSERT INTO language VALUES(5,'ar','Arabic');
INSERT INTO language VALUES(6,'ar-DZ','Arabic - Algeria');
INSERT INTO language VALUES(7,'ar-BH','Arabic - Bahrain');
INSERT INTO language VALUES(8,'ar-EG','Arabic - Egypt');
INSERT INTO language VALUES(9,'ar-IQ','Arabic - Iraq');
INSERT INTO language VALUES(10,'ar-JO','Arabic - Jordan');
INSERT INTO language VALUES(11,'ar-KW','Arabic - Kuwait');
INSERT INTO language VALUES(12,'ar-LB','Arabic - Lebanon');
INSERT INTO language VALUES(13,'ar-LY','Arabic - Libya');
INSERT INTO language VALUES(14,'ar-MA','Arabic - Morocco');
INSERT INTO language VALUES(15,'ar-OM','Arabic - Oman');
INSERT INTO language VALUES(16,'ar-QA','Arabic - Qatar');
INSERT INTO language VALUES(17,'ar-SA','Arabic - Saudi Arabia');
INSERT INTO language VALUES(18,'ar-SY','Arabic - Syria');
INSERT INTO language VALUES(19,'ar-TN','Arabic - Tunisia');
INSERT INTO language VALUES(20,'ar-AE','Arabic - United Arab Emirates');
INSERT INTO language VALUES(21,'ar-YE','Arabic - Yemen');
INSERT INTO language VALUES(22,'hy','Armenian');
INSERT INTO language VALUES(23,'hy-AM','Armenian - Armenia');
INSERT INTO language VALUES(24,'az','Azeri');
INSERT INTO language VALUES(25,'az-AZ-Cyrl','Azeri (Cyrillic) - Azerbaijan');
INSERT INTO language VALUES(26,'az-AZ-Latn','Azeri (Latin) - Azerbaijan');
INSERT INTO language VALUES(27,'eu','Basque');
INSERT INTO language VALUES(28,'eu-ES','Basque - Basque');
INSERT INTO language VALUES(29,'be','Belarusian');
INSERT INTO language VALUES(30,'be-BY','Belarusian - Belarus');
INSERT INTO language VALUES(31,'bg','Bulgarian');
INSERT INTO language VALUES(32,'bg-BG','Bulgarian - Bulgaria');
INSERT INTO language VALUES(33,'ca','Catalan');
INSERT INTO language VALUES(34,'ca-ES','Catalan - Catalan');
INSERT INTO language VALUES(35,'zh-CHS','Chinese (Simplified)');
INSERT INTO language VALUES(36,'zh-CHT','Chinese (Traditional)');
INSERT INTO language VALUES(37,'zh-CN','Chinese - China');
INSERT INTO language VALUES(38,'zh-HK','Chinese - Hong Kong SAR');
INSERT INTO language VALUES(39,'zh-MO','Chinese - Macao SAR');
INSERT INTO language VALUES(40,'zh-SG','Chinese - Singapore');
INSERT INTO language VALUES(41,'zh-TW','Chinese - Taiwan');
INSERT INTO language VALUES(42,'hr','Croatian');
INSERT INTO language VALUES(43,'hr-HR','Croatian - Croatia');
INSERT INTO language VALUES(44,'cs','Czech');
INSERT INTO language VALUES(45,'cs-CZ','Czech - Czech Republic');
INSERT INTO language VALUES(46,'da','Danish');
INSERT INTO language VALUES(47,'da-DK','Danish - Denmark');
INSERT INTO language VALUES(48,'div','Dhivehi');
INSERT INTO language VALUES(49,'div-MV','Dhivehi - Maldives');
INSERT INTO language VALUES(50,'nl','Dutch');
INSERT INTO language VALUES(51,'nl-BE','Dutch - Belgium');
INSERT INTO language VALUES(52,'nl-NL','Dutch - The Netherlands');
INSERT INTO language VALUES(53,'en','English');
INSERT INTO language VALUES(54,'en-AU','English - Australia');
INSERT INTO language VALUES(55,'en-BZ','English - Belize');
INSERT INTO language VALUES(56,'en-CA','English - Canada');
INSERT INTO language VALUES(57,'en-CB','English - Caribbean');
INSERT INTO language VALUES(58,'en-IE','English - Ireland');
INSERT INTO language VALUES(59,'en-JM','English - Jamaica');
INSERT INTO language VALUES(60,'en-NZ','English - New Zealand');
INSERT INTO language VALUES(61,'en-PH','English - Philippines');
INSERT INTO language VALUES(62,'en-ZA','English - South Africa');
INSERT INTO language VALUES(63,'en-TT','English - Trinidad and Tobago');
INSERT INTO language VALUES(64,'en-GB','English - United Kingdom');
INSERT INTO language VALUES(65,'en-US','English - United States');
INSERT INTO language VALUES(66,'en-ZW','English - Zimbabwe');
INSERT INTO language VALUES(67,'eo','Esperanto');
INSERT INTO language VALUES(68,'et','Estonian');
INSERT INTO language VALUES(69,'et-EE','Estonian - Estonia');
INSERT INTO language VALUES(70,'fo','Faroese');
INSERT INTO language VALUES(71,'fo-FO','Faroese - Faroe Islands');
INSERT INTO language VALUES(72,'fa','Farsi');
INSERT INTO language VALUES(73,'fa-IR','Farsi - Iran');
INSERT INTO language VALUES(74,'fi','Finnish');
INSERT INTO language VALUES(75,'fi-FI','Finnish - Finland');
INSERT INTO language VALUES(76,'fr','French');
INSERT INTO language VALUES(77,'fr-BE','French - Belgium');
INSERT INTO language VALUES(78,'fr-CA','French - Canada');
INSERT INTO language VALUES(79,'fr-FR','French - France');
INSERT INTO language VALUES(80,'fr-LU','French - Luxembourg');
INSERT INTO language VALUES(81,'fr-MC','French - Monaco');
INSERT INTO language VALUES(82,'fr-CH','French - Switzerland');
INSERT INTO language VALUES(83,'gl','Galician');
INSERT INTO language VALUES(84,'gl-ES','Galician - Galician');
INSERT INTO language VALUES(85,'ka','Georgian');
INSERT INTO language VALUES(86,'ka-GE','Georgian - Georgia');
INSERT INTO language VALUES(87,'de','German');
INSERT INTO language VALUES(88,'de-AT','German - Austria');
INSERT INTO language VALUES(89,'de-DE','German - Germany');
INSERT INTO language VALUES(90,'de-LI','German - Liechtenstein');
INSERT INTO language VALUES(91,'de-LU','German - Luxembourg');
INSERT INTO language VALUES(92,'de-CH','German - Switzerland');
INSERT INTO language VALUES(93,'el','Greek');
INSERT INTO language VALUES(94,'el-GR','Greek - Greece');
INSERT INTO language VALUES(95,'gu','Gujarati');
INSERT INTO language VALUES(96,'gu-IN','Gujarati - India');
INSERT INTO language VALUES(97,'he','Hebrew');
INSERT INTO language VALUES(98,'he-IL','Hebrew - Israel');
INSERT INTO language VALUES(99,'hi','Hindi');
INSERT INTO language VALUES(100,'hi-IN','Hindi - India');
INSERT INTO language VALUES(101,'hu','Hungarian');
INSERT INTO language VALUES(102,'hu-HU','Hungarian - Hungary');
INSERT INTO language VALUES(103,'is','Icelandic');
INSERT INTO language VALUES(104,'is-IS','Icelandic - Iceland');
INSERT INTO language VALUES(105,'id','Indonesian');
INSERT INTO language VALUES(106,'id-ID','Indonesian - Indonesia');
INSERT INTO language VALUES(107,'it','Italian');
INSERT INTO language VALUES(108,'it-IT','Italian - Italy');
INSERT INTO language VALUES(109,'it-CH','Italian - Switzerland');
INSERT INTO language VALUES(110,'ja','Japanese');
INSERT INTO language VALUES(111,'ja-JP','Japanese - Japan');
INSERT INTO language VALUES(112,'kn','Kannada');
INSERT INTO language VALUES(113,'kn-IN','Kannada - India');
INSERT INTO language VALUES(114,'kk','Kazakh');
INSERT INTO language VALUES(115,'kk-KZ','Kazakh - Kazakhstan');
INSERT INTO language VALUES(116,'kok','Konkani');
INSERT INTO language VALUES(117,'kok-IN','Konkani - India');
INSERT INTO language VALUES(118,'ko','Korean');
INSERT INTO language VALUES(119,'ko-KR','Korean - Korea');
INSERT INTO language VALUES(120,'ky','Kyrgyz');
INSERT INTO language VALUES(121,'ky-KG','Kyrgyz - Kyrgyzstan');
INSERT INTO language VALUES(122,'lv','Latvian');
INSERT INTO language VALUES(123,'lv-LV','Latvian - Latvia');
INSERT INTO language VALUES(124,'lt','Lithuanian');
INSERT INTO language VALUES(125,'lt-LT','Lithuanian - Lithuania');
INSERT INTO language VALUES(126,'lb','Luxembourgish');
INSERT INTO language VALUES(127,'mk','Macedonian');
INSERT INTO language VALUES(128,'mk-MK','Macedonian - Former Yugoslav Republic of Macedonia');
INSERT INTO language VALUES(129,'ms','Malay');
INSERT INTO language VALUES(130,'ms-BN','Malay - Brunei');
INSERT INTO language VALUES(131,'ms-MY','Malay - Malaysia');
INSERT INTO language VALUES(132,'mr','Marathi');
INSERT INTO language VALUES(133,'mr-IN','Marathi - India');
INSERT INTO language VALUES(134,'mn','Mongolian');
INSERT INTO language VALUES(135,'mn-MN','Mongolian - Mongolia');
INSERT INTO language VALUES(136,'no','Norwegian');
INSERT INTO language VALUES(137,'nb-NO','Norwegian (Bokmål) - Norway');
INSERT INTO language VALUES(138,'nn-NO','Norwegian (Nynorsk) - Norway');
INSERT INTO language VALUES(139,'pl','Polish');
INSERT INTO language VALUES(140,'pl-PL','Polish - Poland');
INSERT INTO language VALUES(141,'pt','Portuguese');
INSERT INTO language VALUES(142,'pt-BR','Portuguese - Brazil');
INSERT INTO language VALUES(143,'pt-PT','Portuguese - Portugal');
INSERT INTO language VALUES(144,'pa','Punjabi');
INSERT INTO language VALUES(145,'pa-IN','Punjabi - India');
INSERT INTO language VALUES(146,'ro','Romanian');
INSERT INTO language VALUES(147,'ro-RO','Romanian - Romania');
INSERT INTO language VALUES(148,'ru','Russian');
INSERT INTO language VALUES(149,'ru-RU','Russian - Russia');
INSERT INTO language VALUES(150,'sa','Sanskrit');
INSERT INTO language VALUES(151,'sa-IN','Sanskrit - India');
INSERT INTO language VALUES(152,'sr-SP-Cyrl','Serbian (Cyrillic) - Serbia');
INSERT INTO language VALUES(153,'sr-SP-Latn','Serbian (Latin) - Serbia');
INSERT INTO language VALUES(154,'sk','Slovak');
INSERT INTO language VALUES(155,'sk-SK','Slovak - Slovakia');
INSERT INTO language VALUES(156,'sl','Slovenian');
INSERT INTO language VALUES(157,'sl-SI','Slovenian - Slovenia');
INSERT INTO language VALUES(158,'es','Spanish');
INSERT INTO language VALUES(159,'es-AR','Spanish - Argentina');
INSERT INTO language VALUES(160,'es-BO','Spanish - Bolivia');
INSERT INTO language VALUES(161,'es-CL','Spanish - Chile');
INSERT INTO language VALUES(162,'es-CO','Spanish - Colombia');
INSERT INTO language VALUES(163,'es-CR','Spanish - Costa Rica');
INSERT INTO language VALUES(164,'es-DO','Spanish - Dominican Republic');
INSERT INTO language VALUES(165,'es-EC','Spanish - Ecuador');
INSERT INTO language VALUES(166,'es-SV','Spanish - El Salvador');
INSERT INTO language VALUES(167,'es-GT','Spanish - Guatemala');
INSERT INTO language VALUES(168,'es-HN','Spanish - Honduras');
INSERT INTO language VALUES(169,'es-MX','Spanish - Mexico');
INSERT INTO language VALUES(170,'es-NI','Spanish - Nicaragua');
INSERT INTO language VALUES(171,'es-PA','Spanish - Panama');
INSERT INTO language VALUES(172,'es-PY','Spanish - Paraguay');
INSERT INTO language VALUES(173,'es-PE','Spanish - Peru');
INSERT INTO language VALUES(174,'es-PR','Spanish - Puerto Rico');
INSERT INTO language VALUES(175,'es-ES','Spanish - Spain');
INSERT INTO language VALUES(176,'es-UY','Spanish - Uruguay');
INSERT INTO language VALUES(177,'es-VE','Spanish - Venezuela');
INSERT INTO language VALUES(178,'sw','Swahili');
INSERT INTO language VALUES(179,'sw-KE','Swahili - Kenya');
INSERT INTO language VALUES(180,'sv','Swedish');
INSERT INTO language VALUES(181,'sv-FI','Swedish - Finland');
INSERT INTO language VALUES(182,'sv-SE','Swedish - Sweden');
INSERT INTO language VALUES(183,'syr','Syriac');
INSERT INTO language VALUES(184,'syr-SY','Syriac - Syria');
INSERT INTO language VALUES(185,'ta','Tamil');
INSERT INTO language VALUES(186,'ta-IN','Tamil - India');
INSERT INTO language VALUES(187,'tt','Tatar');
INSERT INTO language VALUES(188,'tt-RU','Tatar - Russia');
INSERT INTO language VALUES(189,'te','Telugu');
INSERT INTO language VALUES(190,'te-IN','Telugu - India');
INSERT INTO language VALUES(191,'th','Thai');
INSERT INTO language VALUES(192,'th-TH','Thai - Thailand');
INSERT INTO language VALUES(193,'tr','Turkish');
INSERT INTO language VALUES(194,'tr-TR','Turkish - Turkey');
INSERT INTO language VALUES(195,'uk','Ukrainian');
INSERT INTO language VALUES(196,'uk-UA','Ukrainian - Ukraine');
INSERT INTO language VALUES(197,'ur','Urdu');
INSERT INTO language VALUES(198,'ur-PK','Urdu - Pakistan');
INSERT INTO language VALUES(199,'uz','Uzbek');
INSERT INTO language VALUES(200,'uz-UZ-Cyrl','Uzbek (Cyrillic) - Uzbekistan');
INSERT INTO language VALUES(201,'uz-UZ-Latn','Uzbek (Latin) - Uzbekistan');
INSERT INTO language VALUES(202,'vi','Vietnamese');

CREATE TABLE IF NOT EXISTS `itunes_category` (
  `id` int unsigned NOT NULL,
  `language` varchar(10) NOT NULL,
  `category` varchar(64) NOT NULL,
  `subcategory` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `itunes_category` VALUES (0,'en', 'None','None'),(1,'en','Arts','Arts'),(2,'en','Arts','Books'),(3,'en','Arts','Design'),(4,'en','Arts','Fashion & Beauty'),(5,'en','Arts','Food'),(6,'en','Arts','Performing Arts'),(7,'en','Arts','Visual Arts'),(8,'en','Business','Business'),(9,'en','Business','Careers'),(10,'en','Business','Entrepreneurship'),(11,'en','Business','Investing'),(12,'en','Business','Management'),(13,'en','Business','Marketing'),(14,'en','Business','Non-Profit'),(15,'en','Comedy','Comedy'),(16,'en','Comedy','Comedy Interviews'),(17,'en','Comedy','Improv'),(18,'en','Comedy','Stand-Up'),(19,'en','Education','Education'),(20,'en','Education','Courses'),(21,'en','Education','How To'),(22,'en','Education','Language Learning'),(23,'en','Education','Self-Improvement'),(24,'en','Fiction','Fiction'),(25,'en','Fiction','Comedy Fiction'),(26,'en','Fiction','Drama'),(27,'en','Fiction','Science Fiction'),(28,'en','Government','Government'),(29,'en','Health & Fitness','Health & Fitness'),(30,'en','Health & Fitness','Alternative Health'),(31,'en','Health & Fitness','Fitness'),(32,'en','Health & Fitness','Medicine'),(33,'en','Health & Fitness','Mental Health'),(34,'en','Health & Fitness','Nutrition'),(35,'en','Health & Fitness','Sexuality'),(36,'en','History','History'),(37,'en','Kids & Family','Kids & Family'),(38,'en','Kids & Family','Education for Kids'),(39,'en','Kids & Family','Parenting'),(40,'en','Kids & Family','Pets & Animals'),(41,'en','Kids & Family','Stories for Kids'),(42,'en','Leisure','Leisure'),(43,'en','Leisure','Animation & Manga'),(44,'en','Leisure','Automotive'),(45,'en','Leisure','Aviation'),(46,'en','Leisure','Crafts'),(47,'en','Leisure','Games'),(48,'en','Leisure','Hobbies'),(49,'en','Leisure','Home & Garden'),(50,'en','Leisure','Video Games'),(51,'en','Music','Music'),(52,'en','Music','Music Commentary'),(53,'en','Music','Music History'),(54,'en','Music','Music Interviews'),(55,'en','News','News'),(56,'en','News','Business News'),(57,'en','News','Daily News'),(58,'en','News','Entertainment News'),(59,'en','News','News Commentary'),(60,'en','News','Politics'),(61,'en','News','Sports News'),(62,'en','News','Tech News'),(63,'en','Religion & Spirituality','Religion & Sprituality'),(64,'en','Religion & Spirituality','Buddhism'),(65,'en','Religion & Spiriuality','Christianity'),(66,'en','Religion & Spiriuality','Hinduism'),(67,'en','Religion & Spiriuality','Islam'),(68,'en','Religion & Spiriuality','Judaism'),(69,'en','Religion & Spiriuality','Religion'),(70,'en','Religion & Spiriuality','Spirituality'),(71,'en','Science','Science'),(72,'en','Science','Astronomy'),(73,'en','Science','Chemistry'),(74,'en','Science','Earth Sciences'),(75,'en','Science','Life Sciences'),(76,'en','Science','Mathematics'),(77,'en','Science','Natural Sciences'),(78,'en','Science','Nature'),(79,'en','Science','Physics'),(80,'en','Science','Social Sciences'),(81,'en','Society & Culture','Society & Culture'),(82,'en','Society & Culture','Documentary'),(83,'en','Society & Culture','Personal Journals'),(84,'en','Society & Culture','Philosophy'),(85,'en','Society & Culture','Places & Travel'),(86,'en','Society & Culture','Relationships'),(87,'en','Sports','Sports'),(88,'en','Sports','Baseball'),(89,'en','Sports','Basketball'),(90,'en','Sports','Cricket'),(91,'en','Sports','Fantasy Sports'),(92,'en','Sports','Football'),(93,'en','Sports','Golf'),(94,'en','Sports','Hockey'),(95,'en','Sports','Rugby'),(96,'en','Sports','Running'),(97,'en','Sports','Soccer'),(98,'en','Sports','Swimming'),(99,'en','Sports','Tennis'),(100,'en','Sports','Volleyball'),(101,'en','Sports','Wilderness'),(102,'en','Sports','Wrestling'),(103,'en','Technology','Technology'),(104,'en','True Crime','True Crime'),(105,'en','TV & Film','TV & Film'),(106,'en','TV & Film','After Shows'),(107,'en','TV & Film','Film History'),(108,'en','TV & Film','Film Interviews'),(109,'en','TV & Film','Film Reviews'),(110,'en','TV & Film','TV Reviews');

    
CREATE TABLE IF NOT EXISTS `useragents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `http_header` text NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `device` varchar(64) DEFAULT NULL,
  `bot` BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- INSERT INTO useragent VALUES(1, 'unknown', NULL, NULL, NULL);

CREATE TABLE IF NOT EXISTS `downloads` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
    -- podcast this HTTP request belongs to or 0 otherwise
   `podcast_id` int unsigned NOT NULL,
   `target` smallint unsigned NOT NULL DEFAULT 1,
   `reference_id` int unsigned NOT NULL,
   `user_agent_id` int unsigned NOT NULL,
   `request_id` varchar(32) NOT NULL,
   `access_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `access_date` date NOT NULL DEFAULT (CURRENT_DATE),
   PRIMARY KEY (`id`),
   KEY `user_agent_id` (`user_agent_id`),
   CONSTRAINT `downloads_ibfk_1` FOREIGN KEY (`user_agent_id`) REFERENCES `useragents` (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `jobs` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
   `guid` binary(16) NOT NULL,
   `podcast_id` int unsigned NOT NULL,
   `jobtype` smallint unsigned NOT NULL DEFAULT 1,
   `schedule` datetime NOT NULL,
   PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `pwdhash` varchar(256) NOT NULL,
  `role` smallint unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO users VALUES(0, 'admin', 'admin', 0);

--
-- Table structure for table `podcasts`
--

CREATE TABLE IF NOT EXISTS `podcasts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `link` varchar(256) NOT NULL,
  `title` varchar(256) NOT NULL,
  `subtitle` text DEFAULT NULL,
  `image_file_name` varchar(128) DEFAULT NULL,
  `copyright` varchar(256) DEFAULT NULL,
  `author` varchar(256) DEFAULT NULL,
  `owner_name` varchar(128) DEFAULT NULL,
  `owner_email` varchar(128) DEFAULT NULL,
  `description` text NOT NULL,
  `meta` text NOT NULL,
  `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `http_hostname` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `podcasts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `episodes`
--

CREATE TABLE IF NOT EXISTS `episodes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `podcast_id` int unsigned NOT NULL,
  `guid` varchar(256) NOT NULL,
  `title` varchar(256) NOT NULL,
  `subtitle` varchar(256) DEFAULT NULL,
  `description` text,
  `content_encoded` text,
  `meta` text NOT NULL,
  `duration` varchar(12) DEFAULT NULL,
  `chapters` text,
  `state` tinyint unsigned NOT NULL DEFAULT '1',
    -- stored as UTC
  `publication_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- stored as UTC
  `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `podcast_id` (`podcast_id`),
  CONSTRAINT `episodes_ibfk_1` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`),
  CONSTRAINT `episodes_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT UC_EPISODE UNIQUE (podcast_id, guid)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `feeds` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `path_name` varchar(36) NOT NULL DEFAULT 'mp3',
  `title` varchar(32) NOT NULL DEFAULT 'MP3',
  `user_id` int unsigned NOT NULL,
  `podcast_id` int unsigned NOT NULL,
  `mime_type` varchar(128) NOT NULL,
  `footer` text,
  `config` text,
  `last_published` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_pending` tinyint(1) NOT NULL DEFAULT '1',
  `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `podcast_id` (`podcast_id`),
  CONSTRAINT `feeds_ibfk_1` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`),
  CONSTRAINT `feeds_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `transcripts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `episode_id` int unsigned NOT NULL,
  `podcast_id` int unsigned NOT NULL,
  `file_name` varchar(256) NOT NULL,
  `language` varchar(10) NOT NULL,
  `mime_type` varchar(128) NOT NULL,
  `captions` tinyint(1) NOT NULL DEFAULT '0',
-- default location is local
  `location` tinyint unsigned NOT NULL DEFAULT '2',
  `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `episode_id` (`episode_id`),
  KEY `podcast_id` (`podcast_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `transcripts_ibfk_1` FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`),
  CONSTRAINT `transcripts_ibfk_2` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`),
  CONSTRAINT `transcripts_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT UC_TRANSCRIPT UNIQUE (episode_id, mime_type)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `enclosures` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `episode_id` int unsigned NOT NULL,
  `podcast_id` int unsigned NOT NULL,
  `file_name` varchar(256) NOT NULL,
  `size` varchar(12) NOT NULL,
  `title` varchar(512) NOT NULL,
  `mime_type` varchar(128) NOT NULL,
-- default location is local
  `location` tinyint unsigned NOT NULL DEFAULT '2',
  `progress` tinyint unsigned DEFAULT NULL,
  `last_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `episode_id` (`episode_id`),
  KEY `podcast_id` (`podcast_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `enclosures_ibfk_1` FOREIGN KEY (`episode_id`) REFERENCES `episodes` (`id`),
  CONSTRAINT `enclosures_ibfk_2` FOREIGN KEY (`podcast_id`) REFERENCES `podcasts` (`id`),
  CONSTRAINT `enclosures_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
