-- 
-- Baza danych: `isrp`
-- 
-- 
-- Struktura tabeli dla  `answer`
-- 

CREATE DATABASE isrp;
USE isrp;


DROP TABLE IF EXISTS `answer`;
CREATE TABLE IF NOT EXISTS `answer` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `questionId` int(10) unsigned NOT NULL,
  `answer` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`),
  KEY `answers_FKIndex1` (`questionId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=37 ;

-- 
-- Zrzut danych tabeli `answer`
-- 

INSERT INTO `answer` VALUES 
(19,2,'Very Important'),
(20,2,'Merits Attention'),
(21,2,'Needs further Work'),
(22,2,'Questionable'),
(23,2,'Poor'),
(24,3,'Thorough Review'),
(25,3,'Adequate'),
(26,3,'Inadequate'),
(27,4,'Well Written'),
(28,4,'Needs Editing'),
(29,4,'Needs Major Revision'),
(30,4,'Difficult to understand'),
(31,4,'Tables are Confusing'),
(32,4,'Figures are Confusing'),
(33,4,'Abstract Needs Revision'),
(34,6,'The paper is in my area of expertise'),
(35,6,'The paper is in an area peripheral to my expertise'),
(36,6,'The paper is in an area not related to my expertise');


-- --------------------------------------------------------

-- 
-- Struktura tabeli dla  `answersquestions`
-- 

DROP TABLE IF EXISTS `answersquestions`;
CREATE TABLE IF NOT EXISTS `answersquestions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `reviewId` int(10) unsigned NOT NULL,
  `questionId` int(10) unsigned NOT NULL,
  `answerId` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `answers_questions_FKIndex1` (`questionId`),
  KEY `answers_questions_FKIndex2` (`answerId`),
  KEY `answers_questions_FKIndex3` (`reviewId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=11 ;

-- 
-- Struktura tabeli dla  `country`
-- 

DROP TABLE IF EXISTS `country`;
CREATE TABLE IF NOT EXISTS `country` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `country` varchar(50) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=238 ;

-- 
-- Zrzut danych tabeli `country`
-- 

INSERT INTO `country` (`id`, `country`) VALUES 
(1, 'Afghanistan'),
(2, 'Albania'),
(3, 'Algeria'),
(4, 'American Samoa'),
(5, 'Andorra'),
(6, 'Anla'),
(7, 'Anguilla'),
(8, 'Antarctica'),
(9, 'Antigua &amp; Barbuda'),
(10, 'Argentina'),
(11, 'Armenia'),
(12, 'Aruba'),
(13, 'Australia'),
(14, 'Austria'),
(15, 'Azerbaijan'),
(16, 'Baham'),
(17, 'Bahrain'),
(18, 'Bangladesh'),
(19, 'Barbados'),
(20, 'Belarus'),
(21, 'Belgium'),
(22, 'Belize'),
(23, 'Benin'),
(24, 'Bermuda'),
(25, 'Bhutan'),
(26, 'Bolivia'),
(27, 'Bosnia Herzevina'),
(28, 'Botswana'),
(29, 'Bouvet Island'),
(30, 'Brazil'),
(31, 'British Indian Ocean Territory'),
(32, 'Brunei'),
(33, 'Bulgaria'),
(34, 'Burkina Fo'),
(35, 'Burundi'),
(36, 'Cambodia'),
(37, 'Cameroon'),
(38, 'Canada'),
(39, 'Cape Verde'),
(40, 'Cayman Islands'),
(41, 'Central African Republic'),
(42, 'Chad'),
(43, 'Chile'),
(44, 'China'),
(45, 'Christm Island'),
(46, 'Cocos (Keeling) Islands'),
(47, 'Colombia'),
(48, 'Comoros'),
(49, 'Con'),
(50, 'Cook Islands'),
(51, 'Costa Rica'),
(52, 'Croatia'),
(53, 'Cuba'),
(54, 'Cyprus'),
(55, 'Czech Republic'),
(56, 'Denmark'),
(57, 'Djibouti'),
(58, 'Dominica'),
(59, 'Dominican Republic'),
(60, 'Et Timor'),
(61, 'Ecuador'),
(62, 'Egypt'),
(63, 'El Salvador'),
(64, 'Equatorial Guinea'),
(65, 'Eritrea'),
(66, 'Estonia'),
(67, 'Ethiopia'),
(68, 'Falkland Islands'),
(69, 'Faroe Islands'),
(70, 'Fiji'),
(71, 'Finland'),
(72, 'France'),
(73, 'French Guiana'),
(74, 'French Polynesia'),
(75, 'Gabon'),
(76, 'Gambia'),
(77, 'Georgia'),
(78, 'Germany'),
(79, 'Ghana'),
(80, 'Gibraltar'),
(81, 'Greece'),
(82, 'Greenland'),
(83, 'Grenada'),
(84, 'Guadeloupe'),
(85, 'Guam'),
(86, 'Guatemala'),
(87, 'Guinea'),
(88, 'Guinea-Bissau'),
(89, 'Guyana'),
(90, 'Haiti'),
(91, 'Heard and McDonald Islands'),
(92, 'Hondur'),
(93, 'Hong Kong'),
(94, 'Hungary'),
(95, 'Iceland'),
(96, 'India'),
(97, 'Indonesia'),
(98, 'Iran'),
(99, 'Iraq'),
(100, 'Ireland'),
(101, 'Israel'),
(102, 'Italy'),
(103, 'Ivory Cot'),
(104, 'Jamaica'),
(105, 'Japan'),
(106, 'Jordan'),
(107, 'Kazakhstan'),
(108, 'Kenya'),
(109, 'Kiribati'),
(110, 'Kuwait'),
(111, 'Kyrgyzstan'),
(112, 'Laos'),
(113, 'Latvia'),
(114, 'Lebanon'),
(115, 'Lesotho'),
(116, 'Liberia'),
(117, 'Libya'),
(118, 'Liechtenstein'),
(119, 'Lithuania'),
(120, 'Luxembourg'),
(121, 'Macau'),
(122, 'Macedonia'),
(123, 'Madagcar'),
(124, 'Malawi'),
(125, 'Malaysia'),
(126, 'Maldives'),
(127, 'Mali'),
(128, 'Malta'),
(129, 'Marshall Islands'),
(130, 'Martinique'),
(131, 'Mauritania'),
(132, 'Mauritius'),
(133, 'Mayotte'),
(134, 'Mexico'),
(135, 'Micronesia'),
(136, 'Moldova'),
(137, 'Monaco'),
(138, 'Monlia'),
(139, 'Montserrat'),
(140, 'Morocco'),
(141, 'Mozambique'),
(142, 'Myanmar'),
(143, 'Namibia'),
(144, 'Nauru'),
(145, 'Nepal'),
(146, 'Netherlands'),
(147, 'Netherlands Antilles'),
(148, 'New Caledonia'),
(149, 'New Zealand'),
(150, 'Nicaragua'),
(151, 'Niger'),
(152, 'Nigeria'),
(153, 'Niue'),
(154, 'Norfolk Island'),
(155, 'North Korea'),
(156, 'Northern Mariana Islands'),
(157, 'Norway'),
(158, 'Oman'),
(159, 'Pakistan'),
(160, 'Palau'),
(161, 'Panama'),
(162, 'Papua New Guinea'),
(163, 'Paraguay'),
(164, 'Peru'),
(165, 'Philippines'),
(166, 'Pitcairn'),
(167, 'Poland'),
(168, 'Portugal'),
(169, 'Puerto Rico'),
(170, 'Qatar'),
(171, 'Reunion'),
(172, 'Romania'),
(173, 'Russia'),
(174, 'Rwanda'),
(175, 'Saint Helena'),
(176, 'Saint Kitts &amp; Nevis'),
(177, 'Saint Lucia'),
(178, 'Saint Pierre &amp; Miguelon'),
(179, 'Saint Tome &amp; Principe'),
(180, 'Saint Vincent &amp; The Grenadines'),
(181, 'Samoa'),
(182, 'San Marino'),
(183, 'Saudi Arabia'),
(184, 'Senegal'),
(185, 'Seychelles'),
(186, 'Sierra Leone'),
(187, 'Singapore'),
(188, 'Slovakia'),
(189, 'Slovenia'),
(190, 'Solomon Islands'),
(191, 'Somalia'),
(192, 'South Africa'),
(193, 'South Georgia and South Sandwich Islands'),
(194, 'South Korea'),
(195, 'Spain'),
(196, 'Sri Lanka'),
(197, 'Sudan'),
(198, 'Suriname'),
(199, 'Svalbard and Jon Mayor Islands'),
(200, 'Swaziland'),
(201, 'Sweden'),
(202, 'Switzerland'),
(203, 'Syrian Arab Republic'),
(204, 'Taiwan'),
(205, 'Tajikistan'),
(206, 'Tanzania'),
(207, 'Thailand'),
(208, 'To'),
(209, 'Tokelau'),
(210, 'Tonga'),
(211, 'Trinidad &amp; Toba'),
(212, 'Tunisia'),
(213, 'Turkey'),
(214, 'Turkmenistan'),
(215, 'Turks &amp; Caicos Islands (British West Indies)'),
(216, 'Tuvalu'),
(217, 'Uganda'),
(218, 'Ukraine'),
(219, 'United Arab Emirates'),
(220, 'United Kingdom'),
(221, 'Unknown'),
(222, 'Uruguay'),
(223, 'USA'),
(224, 'Uzbekistan'),
(225, 'Vanuatu'),
(226, 'Vatican City'),
(227, 'Venezuela'),
(228, 'Viet Nam'),
(229, 'Virgin Islands (British)'),
(230, 'Virgin Islands (U.S.)'),
(231, 'Wallis &amp; Futuna Islands'),
(232, 'Western Sahara'),
(233, 'Yemen Republic'),
(234, 'Yuslavia'),
(235, 'Zambia'),
(236, 'Ziare'),
(237, 'Zimbabwe');

-- --------------------------------------------------------

-- 
-- Struktura tabeli dla  `paper`
-- 

DROP TABLE IF EXISTS `paper`;
CREATE TABLE IF NOT EXISTS `paper` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `reviewerId` int(10) unsigned default NULL,
  `authorId` int(10) unsigned NOT NULL,
  `authors` varchar(255) collate utf8_unicode_ci default NULL,
  `title` varchar(255) collate utf8_unicode_ci default NULL,
  `acronym` varchar(255) collate utf8_unicode_ci default NULL,
  `abstract` text collate utf8_unicode_ci,
  `submissionTime` datetime default NULL,
  `path` varchar(50) collate utf8_unicode_ci default NULL,
  `lastUpdate` timestamp NULL default NULL,
  `version` tinyint(3) unsigned NOT NULL default '1',
  `status` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `papers_FKIndex1` (`authorId`),
  KEY `paper_FKIndex2` (`reviewerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;


-- 
-- Struktura tabeli dla  `question`
-- 

DROP TABLE IF EXISTS `question`;
CREATE TABLE IF NOT EXISTS `question` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `question` varchar(255) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

-- 
-- Zrzut danych tabeli `question`
-- 


INSERT INTO `question` (`id`, `question`) VALUES 
(2, 'Wartość naukowa pracy?'),
(3, 'Znaczenie literaturowe'),
(4, 'Styl i organizacja'),
(6, 'Doświadczenie recenzenta');

-- --------------------------------------------------------

-- 
-- Struktura tabeli dla  `review`
-- 

DROP TABLE IF EXISTS `review`;
CREATE TABLE IF NOT EXISTS `review` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `paperId` int(10) unsigned NOT NULL,
  `reviewerId` int(10) unsigned NOT NULL,
  `status` tinyint(3) unsigned default '0',
  `commentsToAuthor` text collate utf8_unicode_ci,
  `commentsToOrganisator` text collate utf8_unicode_ci,
  `grade` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `reviews_FKIndex1` (`reviewerId`),
  KEY `reviews_FKIndex2` (`paperId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

-- 
-- Struktura tabeli dla  `user`
-- 

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `countryId` int(10) unsigned NOT NULL,
  `title` varchar(50) collate utf8_unicode_ci default NULL,
  `firstName` varchar(100) collate utf8_unicode_ci NOT NULL,
  `lastName` varchar(100) collate utf8_unicode_ci NOT NULL,
  `organization` varchar(100) collate utf8_unicode_ci default NULL,
  `department` varchar(100) collate utf8_unicode_ci default NULL,
  `street` varchar(100) collate utf8_unicode_ci default NULL,
  `zipCode` varchar(50) collate utf8_unicode_ci default NULL,
  `city` varchar(100) collate utf8_unicode_ci default NULL,
  `email` varchar(100) collate utf8_unicode_ci NOT NULL,
  `password` varchar(32) collate utf8_unicode_ci NOT NULL,
  `phone` varchar(25) collate utf8_unicode_ci NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `entryDate` date default NULL,
  `role` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `users_FKIndex1` (`countryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17 ;

-- 
-- Zrzut danych tabeli `user`. Na starcie istnieje tylko jeden użytkownik - Administrator
-- 

INSERT INTO `user` (`id`, `countryId`, `title`, `firstName`, `lastName`, `organization`, `department`, `street`, `zipCode`, `city`, `email`, `password`, `phone`, `gender`, `entryDate`, `role`) VALUES 
(1, 167, 'prof', 'Pierwszy', 'Administrator', 'ISSI', 'Organizacji Konferencji', 'Szybowcowa 4', '67-200', 'Warszawa', 'admin@op.pl', '5f4dcc3b5aa765d61d8327deb882cf99', '4468328261', 0, NULL, 1),
(2, 167, 'prof', 'Testowy', 'Recenzent', 'ISSI', 'Organizacji Konferencji', 'Szybowcowa 4', '67-200', 'Warszawa', 'reviewer@op.pl', '5f4dcc3b5aa765d61d8327deb882cf99', '4468328261', 0, NULL, 3);

-- 
-- Ograniczenia dla tabeli `answer`
-- 
ALTER TABLE `answer`
  ADD CONSTRAINT `answer_ibfk_1` FOREIGN KEY (`questionId`) REFERENCES `question` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- 
-- Ograniczenia dla tabeli `answersquestions`
-- 
ALTER TABLE `answersquestions`
  ADD CONSTRAINT `answersquestions_ibfk_1` FOREIGN KEY (`questionId`) REFERENCES `question` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `answersquestions_ibfk_2` FOREIGN KEY (`answerId`) REFERENCES `answer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `answersquestions_ibfk_3` FOREIGN KEY (`reviewId`) REFERENCES `review` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

-- 
-- Ograniczenia dla tabeli `paper`
-- 
ALTER TABLE `paper`
  ADD CONSTRAINT `paper_ibfk_1` FOREIGN KEY (`authorId`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `paper_ibfk_2` FOREIGN KEY (`reviewerId`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- 
-- Ograniczenia dla tabeli `review`
-- 
ALTER TABLE `review`
  ADD CONSTRAINT `review_ibfk_1` FOREIGN KEY (`reviewerId`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `review_ibfk_2` FOREIGN KEY (`paperId`) REFERENCES `paper` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- 
-- Ograniczenia dla tabeli `user`
-- 
ALTER TABLE `user`
  ADD CONSTRAINT `user_ibfk_1` FOREIGN KEY (`countryId`) REFERENCES `country` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
