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

/*!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 raimondsedvins_4110
CREATE DATABASE IF NOT EXISTS `raimondsedvins_4110` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `raimondsedvins_4110`;

-- Dumping structure for table raimondsedvins_4110.all_data
CREATE TABLE IF NOT EXISTS `all_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id for all table ids',
  `id_registration` int(10) unsigned DEFAULT NULL COMMENT 'person/registration data',
  `id_design` int(10) unsigned DEFAULT NULL COMMENT 'design/looks',
  `id_design_data` int(10) unsigned DEFAULT NULL COMMENT 'all data for design pages',
  `id_login` int(10) unsigned DEFAULT NULL COMMENT 'login',
  `id_ingame` int(10) unsigned DEFAULT NULL COMMENT 'ingame saved data',
  `id_csc` int(10) unsigned DEFAULT NULL COMMENT 'custom settings configuration',
  `id_games` int(10) unsigned DEFAULT NULL COMMENT 'table games, now to connect conf',
  `id_dsc` int(10) unsigned DEFAULT NULL COMMENT 'default setting configuration',
  `id_friend_data` int(10) unsigned DEFAULT NULL,
  `id_forgot` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_reg` (`id_registration`),
  KEY `id_design` (`id_design`),
  KEY `id_login` (`id_login`),
  KEY `id_ingame` (`id_ingame`),
  KEY `id_design_data` (`id_design_data`),
  KEY `id_csc` (`id_csc`),
  KEY `id_dsc` (`id_dsc`),
  KEY `id_friend_data` (`id_friend_data`),
  KEY `FK_all_data_forgot` (`id_forgot`),
  CONSTRAINT `FK_all_data_aplication_data` FOREIGN KEY (`id_ingame`) REFERENCES `aplication_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_custom_setting_configuration` FOREIGN KEY (`id_csc`) REFERENCES `custom_setting_configuration` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_default_setting_configuration` FOREIGN KEY (`id_dsc`) REFERENCES `default_setting_configuration` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_design` FOREIGN KEY (`id_design`) REFERENCES `design` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_design_data` FOREIGN KEY (`id_design_data`) REFERENCES `design_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_forgot` FOREIGN KEY (`id_forgot`) REFERENCES `forgot` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_friend_list_data` FOREIGN KEY (`id_friend_data`) REFERENCES `friend_list_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_login` FOREIGN KEY (`id_login`) REFERENCES `login` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_all_data_registration` FOREIGN KEY (`id_registration`) REFERENCES `registration` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.all_data: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.aplication_data
CREATE TABLE IF NOT EXISTS `aplication_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_login` int(10) unsigned NOT NULL,
  `id_games` int(10) unsigned NOT NULL,
  `id_friends_list` int(10) unsigned DEFAULT NULL,
  `progress` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_login` (`id_login`),
  KEY `id_games` (`id_games`),
  KEY `id_friends_list` (`id_friends_list`),
  KEY `progress` (`progress`),
  CONSTRAINT `FK_aplication_data_friend_list_data` FOREIGN KEY (`id_friends_list`) REFERENCES `friend_list_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_aplication_data_login` FOREIGN KEY (`id_login`) REFERENCES `login` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='f';

-- Dumping data for table raimondsedvins_4110.aplication_data: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.custom_setting_configuration
CREATE TABLE IF NOT EXISTS `custom_setting_configuration` (
  `id` int(10) unsigned NOT NULL,
  `id_games` int(10) unsigned NOT NULL,
  `configuration` varchar(50) DEFAULT NULL,
  `id_login` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_games` (`id_games`),
  KEY `id_login` (`id_login`),
  CONSTRAINT `FK_custom_setting_configuration_login` FOREIGN KEY (`id_login`) REFERENCES `login` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.custom_setting_configuration: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.default_setting_configuration
CREATE TABLE IF NOT EXISTS `default_setting_configuration` (
  `id` int(10) unsigned NOT NULL,
  `id_games` int(10) unsigned NOT NULL,
  `def_conf` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_games` (`id_games`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.default_setting_configuration: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.design
CREATE TABLE IF NOT EXISTS `design` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'dizaina id',
  `id_design` int(10) unsigned NOT NULL COMMENT 'solitaire desingn',
  PRIMARY KEY (`id`),
  KEY `id_design` (`id_design`),
  CONSTRAINT `FK_design_design_data` FOREIGN KEY (`id_design`) REFERENCES `design_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.design: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.design_data
CREATE TABLE IF NOT EXISTS `design_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `game_choice` varchar(50) NOT NULL,
  `settings` varchar(50) NOT NULL,
  `registraiton` varchar(50) NOT NULL,
  `login` varchar(50) NOT NULL,
  `friend_list` varchar(50) NOT NULL,
  `solitare` varchar(50) NOT NULL,
  `bouncing` varchar(50) NOT NULL,
  `snake` varchar(50) NOT NULL,
  `minesweeper` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.design_data: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.forgot
CREATE TABLE IF NOT EXISTS `forgot` (
  `id` int(10) unsigned NOT NULL DEFAULT 0,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.forgot: ~0 rows (approximately)

-- Dumping structure for table raimondsedvins_4110.friend_invites
CREATE TABLE IF NOT EXISTS `friend_invites` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `invites` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `invites` (`invites`),
  CONSTRAINT `FK_friend_invites_login` FOREIGN KEY (`invites`) REFERENCES `login` (`nickname`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.friend_invites: ~1 rows (approximately)
INSERT INTO `friend_invites` (`id`, `invites`) VALUES
	(1, 'robot');

-- Dumping structure for table raimondsedvins_4110.friend_list_data
CREATE TABLE IF NOT EXISTS `friend_list_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id list for friends ',
  `friends` varchar(25) DEFAULT NULL COMMENT 'friend list',
  PRIMARY KEY (`id`),
  KEY `friends` (`friends`),
  CONSTRAINT `FK_friend_list_data_login` FOREIGN KEY (`friends`) REFERENCES `login` (`nickname`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.friend_list_data: ~3 rows (approximately)
INSERT INTO `friend_list_data` (`id`, `friends`) VALUES
	(2, 'edvino'),
	(1, 'Me'),
	(3, 'toloswag_420');

-- Dumping structure for table raimondsedvins_4110.games
CREATE TABLE IF NOT EXISTS `games` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_friend_list_data` int(10) unsigned NOT NULL,
  `solitaire` varchar(50) NOT NULL,
  `bouncing` varchar(50) NOT NULL,
  `snake` varchar(50) NOT NULL,
  `minesweeper` time NOT NULL,
  `minesweeper_easy` time NOT NULL,
  `minesweeper_hard` time NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id fr` (`id_friend_list_data`) USING BTREE,
  CONSTRAINT `FK_games_friend_list_data` FOREIGN KEY (`id_friend_list_data`) REFERENCES `friend_list_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.games: ~3 rows (approximately)
INSERT INTO `games` (`id`, `id_friend_list_data`, `solitaire`, `bouncing`, `snake`, `minesweeper`, `minesweeper_easy`, `minesweeper_hard`) VALUES
	(1, 1, '', '0', '', '00:00:11', '00:00:21', '00:00:14'),
	(2, 2, '', '250', '', '02:15:00', '02:43:00', '10:00:00'),
	(3, 3, '', '1500', '', '00:56:00', '01:13:00', '04:20:00');

-- Dumping structure for table raimondsedvins_4110.login
CREATE TABLE IF NOT EXISTS `login` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'login',
  `id_registration` int(10) unsigned NOT NULL COMMENT 'reg id for login',
  `nickname` varchar(50) DEFAULT NULL COMMENT 'ingame name',
  `password` varchar(50) DEFAULT NULL COMMENT 'user password',
  PRIMARY KEY (`id`),
  KEY `id_registration` (`id_registration`),
  KEY `nickname` (`nickname`),
  CONSTRAINT `FK_login_registration` FOREIGN KEY (`id_registration`) REFERENCES `registration` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.login: ~5 rows (approximately)
INSERT INTO `login` (`id`, `id_registration`, `nickname`, `password`) VALUES
	(1, 1, 'Me', '123'),
	(2, 2, 'edvino', '321'),
	(3, 3, 'toloswag_420', 'yeye'),
	(4, 4, 'robot', '456'),
	(5, 5, 'rubert', 'ruru');

-- Dumping structure for function raimondsedvins_4110.register
DELIMITER //
CREATE FUNCTION `register`() RETURNS int(11)
BEGIN 
SET @id =0;
INSERT INTO register 
(`name`,
 email)
 VALUES(
 vards ,
email);
SELECT LAST_INSERT_ID()
INTO @id;
INSERT INTO login 
(register_id,
login,
 password)
 VALUES(
 @id,
 username,
password);
SELECT LAST_INSERT_ID()
INTO @id;
RETURN @id;
END//
DELIMITER ;

-- Dumping structure for table raimondsedvins_4110.registration
CREATE TABLE IF NOT EXISTS `registration` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Person/registration data',
  `email` varchar(50) NOT NULL COMMENT 'user e-mail',
  `name` varchar(50) NOT NULL COMMENT 'user real name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.registration: ~5 rows (approximately)
INSERT INTO `registration` (`id`, `email`, `name`) VALUES
	(1, 'raionds@gmail.com', 'me'),
	(2, 'edvins@gmail.com', 'Edvins'),
	(3, 'fake@hotmail.net', 'drizzy'),
	(4, 'robot@robot.net', 'robot'),
	(5, 'rubert@gmail.com', 'rubert');

-- Dumping structure for table raimondsedvins_4110.scoreboard
CREATE TABLE IF NOT EXISTS `scoreboard` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_games` int(10) unsigned DEFAULT NULL,
  `id_friend_list_data` int(10) unsigned DEFAULT NULL,
  `progress` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_friend_list_data` (`id_friend_list_data`),
  KEY `progress` (`progress`),
  KEY `id_games` (`id_games`),
  CONSTRAINT `FK_board_friend_list_data` FOREIGN KEY (`id_friend_list_data`) REFERENCES `friend_list_data` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_scoreboard_aplication_data` FOREIGN KEY (`progress`) REFERENCES `aplication_data` (`progress`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_scoreboard_games` FOREIGN KEY (`id_games`) REFERENCES `games` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table raimondsedvins_4110.scoreboard: ~0 rows (approximately)

/*!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) */;
