--
-- Host: localhost    Database: zamar
-- ------------------------------------------------------



CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `submitter_id` int(11) DEFAULT NULL,
  `status` enum('DeleteRequest','Published','Submitted') NOT NULL DEFAULT 'Published',
  `created` datetime DEFAULT current_timestamp(),
  `type` enum('Text','Music','Translated','Copyright','Artist') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`type`),
  KEY `submitter_id` (`submitter_id`),
  CONSTRAINT `author_ibfk_1` FOREIGN KEY (`submitter_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1618 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `auth_assignment`
--

CREATE TABLE `auth_assignment` (
  `item_name` varchar(64) NOT NULL,
  `user_id` varchar(64) NOT NULL,
  `created_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_name`,`user_id`),
  CONSTRAINT `auth_assignment_ibfk_1` FOREIGN KEY (`item_name`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `auth_assignment`
--

INSERT INTO `auth_assignment` VALUES
('Administrator','121',1696782928),
('Administrator','44',NULL),
('Editor','51',1697397589),
('Member','112',1692104741),
('Member','140',1696675406),
('Member','141',1696698816),
('Member','142',1696707242),
('Member','143',1696718766),
('Member','144',1696786801),
('Member','156',1697027175),
('Member','157',1697719411),
('Member','59',1682112717),
('Member','83',1696782688),
('Member','84',1686507007),
('Member','85',1696782675),
('theCreator','40',NULL),
('theCreator','43',NULL);

--
-- Table structure for table `auth_item`
--

CREATE TABLE `auth_item` (
  `name` varchar(64) NOT NULL,
  `type` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  `rule_name` varchar(64) DEFAULT NULL,
  `data` text DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `rule_name` (`rule_name`),
  KEY `type` (`type`),
  CONSTRAINT `auth_item_ibfk_1` FOREIGN KEY (`rule_name`) REFERENCES `auth_rule` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `auth_item`
--

INSERT INTO `auth_item` VALUES
('Administrator',1,NULL,NULL,NULL,1678527976,1678527976),
('Editor',1,NULL,NULL,NULL,1678527976,1678527976),
('Member',1,NULL,NULL,NULL,1678527775,1678527775),
('theCreator',1,NULL,NULL,NULL,1678527976,1678527976);

--
-- Table structure for table `auth_item_child`
--

CREATE TABLE `auth_item_child` (
  `parent` varchar(64) NOT NULL,
  `child` varchar(64) NOT NULL,
  PRIMARY KEY (`parent`,`child`),
  KEY `child` (`child`),
  CONSTRAINT `auth_item_child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `auth_item_child_ibfk_2` FOREIGN KEY (`child`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `auth_item_child`
--

INSERT INTO `auth_item_child` VALUES
('Administrator','Editor'),
('Editor','Member'),
('theCreator','Administrator');

--
-- Table structure for table `auth_rule`
--

CREATE TABLE `auth_rule` (
  `name` varchar(64) NOT NULL,
  `data` text DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `auth_rule`
--


--
-- Table structure for table `author`
--

CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci NOT NULL,
  `submitter_id` int(11) DEFAULT NULL,
  `status` enum('DeleteRequest','Published','Submitted') NOT NULL DEFAULT 'Published',
  `created` datetime DEFAULT current_timestamp(),
  `type` enum('Text','Music','Translated','Copyright','Artist') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`type`),
  KEY `submitter_id` (`submitter_id`),
  CONSTRAINT `author_ibfk_1` FOREIGN KEY (`submitter_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=629 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Table structure for table `chord`
--

CREATE TABLE `chord` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `row_id` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `chord` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `row_id` (`row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=116590 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Table structure for table `faq`
--

CREATE TABLE `faq` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `faq_topic_id` int(11) NOT NULL,
  `question` varchar(1028) NOT NULL,
  `answer` varchar(2056) NOT NULL,
  `status` enum('APPROVED','SUBMITTED') NOT NULL DEFAULT 'APPROVED',
  `created` datetime DEFAULT current_timestamp(),
  `score` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `language_id` (`language_id`),
  KEY `faq_topic_id` (`faq_topic_id`),
  CONSTRAINT `faq_ibfk_1` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`),
  CONSTRAINT `faq_ibfk_2` FOREIGN KEY (`faq_topic_id`) REFERENCES `faq_topic` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;


--
-- Table structure for table `faq_topic`
--

CREATE TABLE `faq_topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `topic` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `faq_topic_ibfk_1` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `language`
--

CREATE TABLE `language` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `iso_name` varchar(7) DEFAULT NULL,
  `display_name_english` varchar(100) NOT NULL,
  `display_name_native` varchar(100) NOT NULL,
  `type` enum('ltr','rtl') NOT NULL DEFAULT 'ltr',
  `printfontfamily_id` int(11) NOT NULL,
  `printfontweight` int(11) NOT NULL,
  `printfontsize` float NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `display_name_english` (`display_name_english`),
  UNIQUE KEY `iso_name` (`iso_name`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT;

--
-- Dumping data for table `language`
--

INSERT INTO `language` VALUES
(3,'en-US','English','English','ltr',7,500,1.3),
(4,'sv','Swedish','Svenska','ltr',8,500,1),
(12,'ar','Arabic','العربية','rtl',6,900,1.3);

--
-- Table structure for table `language_font_family`
--

CREATE TABLE `language_font_family` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL,
  `fontfamilyname` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `language_font_family_ibfk_1` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `language_font_family`
--

INSERT INTO `language_font_family` VALUES
(1,12,'AzarMehrMono'),
(2,12,'DejaVuSansMono'),
(3,12,'HasubiMono'),
(4,12,'KawkabMono'),
(5,12,'Thabit'),
(6,12,'AzarMehrMonoBold'),
(7,3,'system-ui, -apple-system, \'Segoe UI\', Roboto, \'Helvetica Neue\', \'Noto Sans\', \'Liberation Sans\', Arial, sans-serif'),
(8,4,'system-ui, -apple-system, \'Segoe UI\', Roboto, \'Helvetica Neue\', \'Noto Sans\', \'Liberation Sans\', Arial, sans-serif');

--
-- Table structure for table `list_songs`
--

CREATE TABLE `list_songs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_lists_id` int(11) NOT NULL,
  `song_id` int(11) DEFAULT NULL,
  `sequence` int(11) NOT NULL,
  `keynote` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `orderof` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_lists_id` (`user_lists_id`),
  KEY `song_id` (`song_id`),
  CONSTRAINT `list_songs_ibfk_1` FOREIGN KEY (`user_lists_id`) REFERENCES `user_lists` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1086 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Table structure for table `migration`
--

CREATE TABLE `migration` (
  `version` varchar(180) NOT NULL,
  `apply_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `migration`
--


--
-- Table structure for table `notification`
--

CREATE TABLE `notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `status` enum('Draft','Published') NOT NULL DEFAULT 'Draft',
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `notification_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


--
-- Dumping data for table `notification_language`
--


--
-- Table structure for table `paragraph`
--

CREATE TABLE `paragraph` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('Verse','Chorus','Bridge') NOT NULL,
  `sequence` int(11) NOT NULL,
  `song_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `song_id` (`song_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8896 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Table structure for table `row`
--

CREATE TABLE `row` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci NOT NULL,
  `sequence` int(11) NOT NULL,
  `paragraph_id` int(11) NOT NULL,
  `created` datetime DEFAULT current_timestamp(),
  `updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `paragraph_id` (`paragraph_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46564 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


--
-- Table structure for table `song`
--

CREATE TABLE `song` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `title2` varchar(255) DEFAULT NULL,
  `submitter_id` int(11) DEFAULT NULL,
  `publisher_id` int(11) DEFAULT NULL,
  `status` enum('ChangeRequest','Submitted','Published','Rejected','Medley') NOT NULL,
  `rejected_reason` varchar(512) DEFAULT NULL,
  `orderof` varchar(255) NOT NULL,
  `keynote` varchar(10) NOT NULL,
  `created` datetime DEFAULT current_timestamp(),
  `updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `song_change_id` int(11) DEFAULT NULL,
  `language_id` int(11) NOT NULL,
  `bibleverse` varchar(128) DEFAULT NULL,
  `text` text DEFAULT NULL,
  `haschords` tinyint(1) NOT NULL DEFAULT 0,
  `title12` varchar(255) DEFAULT NULL,
  `title22` varchar(255) DEFAULT NULL,
  `inspected` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `submitter_id` (`submitter_id`),
  KEY `publisher_id` (`publisher_id`),
  KEY `song_ibfk_1` (`song_change_id`),
  KEY `song_ibfk_6` (`language_id`),
  CONSTRAINT `song_ibfk_4` FOREIGN KEY (`submitter_id`) REFERENCES `user` (`id`),
  CONSTRAINT `song_ibfk_5` FOREIGN KEY (`publisher_id`) REFERENCES `user` (`id`),
  CONSTRAINT `song_ibfk_6` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1944 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Table structure for table `song_authors`
--

CREATE TABLE `song_authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `song_id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `song_id` (`song_id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `song_authors_ibfk_1` FOREIGN KEY (`song_id`) REFERENCES `song` (`id`),
  CONSTRAINT `song_authors_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6441 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
--
-- Table structure for table `song_delete_request`
--

CREATE TABLE `song_delete_request` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `song_id` int(11) NOT NULL,
  `delete_reason` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci DEFAULT NULL,
  `reject_reason` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci DEFAULT NULL,
  `created` datetime DEFAULT current_timestamp(),
  `updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `song_id` (`song_id`),
  CONSTRAINT `song_delete_request_ibfk_1` FOREIGN KEY (`song_id`) REFERENCES `song` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


--
-- Table structure for table `song_tags`
--

CREATE TABLE `song_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `song_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  `submitter_id` int(11) NOT NULL,
  `publisher_id` int(11) DEFAULT NULL,
  `created` datetime DEFAULT current_timestamp(),
  `status` enum('DeleteRequest','Submitted','Published','Rejected') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `song_id` (`song_id`,`tag_id`),
  KEY `tag_id` (`tag_id`),
  KEY `song_tags_ibfk_3` (`submitter_id`),
  KEY `song_tags_ibfk_4` (`publisher_id`),
  CONSTRAINT `song_tags_ibfk_1` FOREIGN KEY (`song_id`) REFERENCES `song` (`id`),
  CONSTRAINT `song_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`),
  CONSTRAINT `song_tags_ibfk_3` FOREIGN KEY (`submitter_id`) REFERENCES `user` (`id`),
  CONSTRAINT `song_tags_ibfk_4` FOREIGN KEY (`publisher_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8434 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Table structure for table `tag`
--

CREATE TABLE `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci NOT NULL,
  `submitter_id` int(11) DEFAULT NULL,
  `status` enum('DeleteRequest','Published','Submitted') NOT NULL DEFAULT 'Published',
  `created` datetime DEFAULT current_timestamp(),
  `language_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `submitter_id` (`submitter_id`),
  CONSTRAINT `tag_ibfk_1` FOREIGN KEY (`submitter_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=144 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Table structure for table `testimonial`
--

CREATE TABLE `testimonial` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `testimony` varchar(1024) NOT NULL,
  `name` varchar(64) NOT NULL,
  `status` enum('SUBMITTED','APPROVED') NOT NULL,
  `submitter_id` int(11) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `language_id` int(11) NOT NULL,
  `place` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `submitter_id` (`submitter_id`),
  KEY `language_id` (`language_id`),
  CONSTRAINT `testimonial_ibfk_1` FOREIGN KEY (`submitter_id`) REFERENCES `user` (`id`),
  CONSTRAINT `testimonial_ibfk_2` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `user_lists`
--

CREATE TABLE `user_lists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime DEFAULT current_timestamp(),
  `updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `custom_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_lists_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=412 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;





