-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `tijdverlengers`
--

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

--
-- Stand-in structure for view `current`
-- (See below for the actual view)
--
CREATE TABLE `current` (
`llnr` varchar(16)
,`text` varchar(64)
);

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

--
-- Table structure for table `files`
--

CREATE TABLE `files` (
  `file_id` int(11) NOT NULL,
  `file_md5` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `file_name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `file_tp` int(11) NOT NULL,
  `file_version` int(11) NOT NULL,
  `file_ok` int(11) NOT NULL,
  `file_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `file_restrict` enum('ADMIN','TEACHER','STUDENT') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'ADMIN'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `lijst`
--

CREATE TABLE `lijst` (
  `lijst_id` int(11) NOT NULL,
  `prev_lijst_id` int(11) DEFAULT NULL,
  `opm_id` int(11) DEFAULT NULL,
  `t_creat` timestamp NOT NULL DEFAULT current_timestamp(),
  `author` varchar(32) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `mondelingen`
--

CREATE TABLE `mondelingen` (
  `toets_id` int(11) NOT NULL,
  `file_id` int(11) NOT NULL,
  `mondeling_date` date NOT NULL,
  `mondeling_start` time NOT NULL,
  `mondeling_end` time NOT NULL,
  `mondeling_afd` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `mondeling_vak` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `mondeling_ll` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `mondeling_lok` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `mondeling_doc` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `mondeling_crap_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `opm`
--

CREATE TABLE `opm` (
  `opm_id` int(11) NOT NULL,
  `llnr` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `text` varchar(64) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `toetsen`
--

CREATE TABLE `toetsen` (
  `toets_id` int(11) NOT NULL,
  `file_id` int(11) NOT NULL,
  `toets_formula` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `toets_date` date NOT NULL,
  `toets_start` time NOT NULL,
  `toets_end` time NOT NULL,
  `toets_afd` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `toets_vak` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `toets_grp` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `toets_lok` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `toets_surv` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `toets_crap_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Table structure for table `vergoeding`
--

CREATE TABLE `vergoeding` (
  `vergoeding_id` int(11) NOT NULL,
  `docent` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `groep` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `tv1` int(11) NOT NULL,
  `tv2` int(11) NOT NULL,
  `ppl_id` int(11) NOT NULL,
  `year_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

--
-- Structure for view `current`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `current`  AS  select `opm`.`llnr` AS `llnr`,`opm`.`text` AS `text` from ((`lijst` join `opm` on(`lijst`.`opm_id` = `opm`.`opm_id`)) left join `lijst` `next_lijst` on(`next_lijst`.`prev_lijst_id` = `lijst`.`lijst_id`)) where `next_lijst`.`prev_lijst_id` is null ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `files`
--
ALTER TABLE `files`
  ADD PRIMARY KEY (`file_id`);

--
-- Indexes for table `lijst`
--
ALTER TABLE `lijst`
  ADD PRIMARY KEY (`lijst_id`),
  ADD UNIQUE KEY `prev_lijst_id` (`prev_lijst_id`),
  ADD KEY `opm_id` (`opm_id`);

--
-- Indexes for table `mondelingen`
--
ALTER TABLE `mondelingen`
  ADD PRIMARY KEY (`toets_id`);

--
-- Indexes for table `opm`
--
ALTER TABLE `opm`
  ADD PRIMARY KEY (`opm_id`),
  ADD KEY `llnr` (`llnr`,`text`) USING BTREE;

--
-- Indexes for table `toetsen`
--
ALTER TABLE `toetsen`
  ADD PRIMARY KEY (`toets_id`);

--
-- Indexes for table `vergoeding`
--
ALTER TABLE `vergoeding`
  ADD PRIMARY KEY (`vergoeding_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `files`
--
ALTER TABLE `files`
  MODIFY `file_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `lijst`
--
ALTER TABLE `lijst`
  MODIFY `lijst_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mondelingen`
--
ALTER TABLE `mondelingen`
  MODIFY `toets_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `opm`
--
ALTER TABLE `opm`
  MODIFY `opm_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `toetsen`
--
ALTER TABLE `toetsen`
  MODIFY `toets_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `vergoeding`
--
ALTER TABLE `vergoeding`
  MODIFY `vergoeding_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `lijst`
--
ALTER TABLE `lijst`
  ADD CONSTRAINT `opm_id should point to an existing opm` FOREIGN KEY (`opm_id`) REFERENCES `opm` (`opm_id`),
  ADD CONSTRAINT `prev_lijst_id should point to an existing lijst_id` FOREIGN KEY (`prev_lijst_id`) REFERENCES `lijst` (`lijst_id`);
COMMIT;

