-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.1.29-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win32
-- HeidiSQL Version:             12.4.0.6659
-- --------------------------------------------------------

/*!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 structure for table saulitis_qr_project.saulits_mervieniba
CREATE TABLE IF NOT EXISTS `saulits_mervieniba` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mervieniba` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- Dumping data for table saulitis_qr_project.saulits_mervieniba: ~2 rows (approximately)
INSERT INTO `saulits_mervieniba` (`id`, `mervieniba`) VALUES
	(11, 'metri'),
	(12, 'gab');

-- Dumping structure for table saulitis_qr_project.saulits_noliktava
CREATE TABLE IF NOT EXISTS `saulits_noliktava` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serijas_nr` varchar(50) DEFAULT NULL,
  `prece_id` int(11) NOT NULL,
  `daudzums` varchar(50) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  `piegadatajs_id` int(11) DEFAULT NULL,
  `mervieniba_id` int(11) NOT NULL,
  `cena` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_noliktava_prece` (`prece_id`),
  KEY `FK_noliktava_piegadatajs` (`piegadatajs_id`),
  KEY `FK_noliktava_mervieniba` (`mervieniba_id`),
  CONSTRAINT `FK_noliktava_mervieniba` FOREIGN KEY (`mervieniba_id`) REFERENCES `saulits_mervieniba` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_noliktava_piegadatajs` FOREIGN KEY (`piegadatajs_id`) REFERENCES `saulits_piegadatajs` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_noliktava_prece` FOREIGN KEY (`prece_id`) REFERENCES `saulits_prece` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

-- Dumping data for table saulitis_qr_project.saulits_noliktava: ~4 rows (approximately)
INSERT INTO `saulits_noliktava` (`id`, `serijas_nr`, `prece_id`, `daudzums`, `date_time`, `piegadatajs_id`, `mervieniba_id`, `cena`) VALUES
	(21, '345', 22, '4', '2023-10-10 12:38:13', 19, 11, '4,23'),
	(22, '345', 22, '5', '2023-10-10 12:38:48', 19, 11, '4,23'),
	(23, 'galds123', 23, '1', '2023-10-11 10:40:30', 19, 12, '300'),
	(24, 'galds123', 23, '-1', '2023-10-11 10:41:18', 20, 12, '300');

-- Dumping structure for view saulitis_qr_project.saulits_noliktava_view
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `saulits_noliktava_view` (
	`id` INT(11) NOT NULL,
	`serijas_nr` VARCHAR(50) NULL COLLATE 'utf8_general_ci',
	`prece` VARCHAR(50) NULL COLLATE 'utf8_general_ci',
	`daudzums` DOUBLE NULL,
	`date_time` DATETIME NULL,
	`piegadatajs` VARCHAR(50) NULL COLLATE 'utf8_general_ci',
	`mervieniba` VARCHAR(10) NULL COLLATE 'utf8_general_ci',
	`cena` VARCHAR(50) NULL COLLATE 'utf8_general_ci'
) ENGINE=MyISAM;

-- Dumping structure for table saulitis_qr_project.saulits_piegadatajs
CREATE TABLE IF NOT EXISTS `saulits_piegadatajs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `piegadatajs` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

-- Dumping data for table saulitis_qr_project.saulits_piegadatajs: ~2 rows (approximately)
INSERT INTO `saulits_piegadatajs` (`id`, `piegadatajs`) VALUES
	(19, 'Roberts'),
	(20, 'Bruh');

-- Dumping structure for table saulitis_qr_project.saulits_prece
CREATE TABLE IF NOT EXISTS `saulits_prece` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prece` varchar(50) NOT NULL DEFAULT '0',
  `preces_kods` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `preces_kods` (`preces_kods`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

-- Dumping data for table saulitis_qr_project.saulits_prece: ~2 rows (approximately)
INSERT INTO `saulits_prece` (`id`, `prece`, `preces_kods`) VALUES
	(22, 'Kabelis', '111222'),
	(23, 'Galds', '555566');

-- Dumping structure for view saulitis_qr_project.saulits_noliktava_view
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `saulits_noliktava_view`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `saulits_noliktava_view` AS SELECT n.id, n.serijas_nr, u.prece, SUM(n.daudzums) AS daudzums, n.date_time, p.piegadatajs, m.mervieniba, n.cena
FROM saulits_noliktava AS n
LEFT JOIN saulits_piegadatajs AS p
ON p.id=n.piegadatajs_id
LEFT JOIN saulits_mervieniba AS m
ON m.id=n.mervieniba_id
LEFT JOIN saulits_prece AS u
ON u.id=n.prece_id
GROUP BY n.prece_id 
HAVING SUM(n.daudzums) > 0 ;

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