-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.28-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for skoleni
CREATE DATABASE IF NOT EXISTS `skoleni` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `skoleni`;

-- Dumping structure for table skoleni.skoleni_atzimes
CREATE TABLE IF NOT EXISTS `skoleni_atzimes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade` int(2) NOT NULL COMMENT 'Atzīme',
  `subject_id` int(11) NOT NULL COMMENT 'Kurā priekšmetā ir šī atzīme',
  `student_id` int(11) NOT NULL COMMENT 'Kuram skolēnam ir šī atzīme',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Šeit tiek saglabātas skolēnu atzīmes';

-- Dumping data for table skoleni.skoleni_atzimes: ~0 rows (approximately)
DELETE FROM `skoleni_atzimes`;

-- Dumping structure for function skoleni.skoleni_check_person
DELIMITER //
CREATE FUNCTION `skoleni_check_person`(`dname` VARCHAR(50),
	`dfname` VARCHAR(50)
) RETURNS int(11)
    COMMENT 'Funkcija, kas pārbauda vai šāds cilvēks eksistē, pie reģistrācijas, lai netiek reģistrēti divi identiski cilvēki'
BEGIN
	DECLARE person_id INT(11);
	
	SELECT id INTO person_id
	FROM skoleni_personas
	WHERE NAME = dname and fname = dfname
	LIMIT 1;
	RETURN person_id;
END//
DELIMITER ;

-- Dumping structure for function skoleni.skoleni_check_user
DELIMITER //
CREATE FUNCTION `skoleni_check_user`(`demail` VARCHAR(50),
	`passw` VARCHAR(50)
) RETURNS int(11)
    COMMENT 'Funkcija, kas pārbauda vai ievadītais lietotājs eksistē'
BEGIN
	DECLARE user_id INT(11);
	
	SELECT id INTO user_id
	FROM skoleni_konti
	WHERE email = demail and PASSWORD = passw;
	RETURN user_id;
END//
DELIMITER ;

-- Dumping structure for function skoleni.skoleni_create_person
DELIMITER //
CREATE FUNCTION `skoleni_create_person`(`name` VARCHAR(50),
	`fname` VARCHAR(50),
	`position` TINYINT,
	`class` INT
) RETURNS int(11)
    COMMENT 'Funkcija, kas izveido cilvēkus'
BEGIN
	DECLARE dname VARCHAR(50);
	DECLARE dfname VARCHAR(50);
	DECLARE dposition TINYINT(1);
	DECLARE dclass INT(3);
	
	SELECT name, fname, position, class INTO dname, dfname, dposition, dclass;
	
	INSERT INTO skoleni_personas(name, fname, position, class)
	VALUES (dname, dfname, dposition, dclass);
	
	RETURN LAST_INSERT_ID();
END//
DELIMITER ;

-- Dumping structure for function skoleni.skoleni_create_user
DELIMITER //
CREATE FUNCTION `skoleni_create_user`(`email` VARCHAR(50),
	`passw` VARCHAR(50)
) RETURNS int(11)
    COMMENT 'Funkcija, kas izveido lietotāju'
BEGIN
    DECLARE dperson_id INT(11);
    
    SELECT max(id) INTO dperson_id
    FROM skoleni_personas;
    
    INSERT INTO skoleni_konti (email, PASSWORD, person_id)
    VALUES (email, passw, dperson_id);
    
    RETURN LAST_INSERT_ID();
END//
DELIMITER ;

-- Dumping structure for table skoleni.skoleni_konti
CREATE TABLE IF NOT EXISTS `skoleni_konti` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(20) NOT NULL,
  `person_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Šeit tiek saglabāti epasti un paroles';

-- Dumping data for table skoleni.skoleni_konti: ~1 rows (approximately)
DELETE FROM `skoleni_konti`;
INSERT INTO `skoleni_konti` (`id`, `email`, `password`, `person_id`) VALUES
	(1, 'dunavskisedijs@gmail.com', 'ergegrg', 2);

-- Dumping structure for table skoleni.skoleni_login
CREATE TABLE IF NOT EXISTS `skoleni_login` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table skoleni.skoleni_login: ~0 rows (approximately)
DELETE FROM `skoleni_login`;

-- Dumping structure for table skoleni.skoleni_personas
CREATE TABLE IF NOT EXISTS `skoleni_personas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `fname` varchar(30) NOT NULL,
  `position` tinyint(1) DEFAULT 1 COMMENT 'Kurā pakāpē ir šis cilvēks(skolnieks/skolotājs...)',
  `class` int(11) DEFAULT NULL COMMENT 'kurā klasē mācās, vai kuru kl;asi māca',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table skoleni.skoleni_personas: ~2 rows (approximately)
DELETE FROM `skoleni_personas`;
INSERT INTO `skoleni_personas` (`id`, `name`, `fname`, `position`, `class`) VALUES
	(1, 'Edijs', 'Dunavskis', 1, 310),
	(2, 'Edijs', 'Dunavskis', 1, 310);

-- Dumping structure for table skoleni.skoleni_prieksmeti
CREATE TABLE IF NOT EXISTS `skoleni_prieksmeti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(20) NOT NULL,
  `special` tinyint(1) DEFAULT NULL COMMENT 'Vai šie priekšmeti ir tikai kādā noteiktā klasē, vai sākas no noteiktas klases',
  `class_start` tinyint(2) DEFAULT NULL COMMENT 'No kuras klases sākas',
  `class_end` tinyint(2) DEFAULT NULL COMMENT 'kurā klasē beidzas',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Šeit ir saraksts ar visiem mācību priekšmetiem';

-- Dumping data for table skoleni.skoleni_prieksmeti: ~0 rows (approximately)
DELETE FROM `skoleni_prieksmeti`;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;