--
-- Host: localhost    Database: user109
-- ------------------------------------------------------


--
-- Table structure for table `city`
--

CREATE TABLE `city` (
  `city_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `city`
--

INSERT INTO `city` VALUES (1,'Moskva'),(2,'Sankt-Peterburg'),(3,'Kazan'),(4,'Ekaterinburg'),(5,'Novosibirsk');

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
  `customer_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` VALUES (1,'Ivanov_Ivan'),(2,'Petrova_Anna'),(3,'Sidorov_Mihail'),(4,'Kuznetsova_Olga');

--
-- Table structure for table `groups`
--

CREATE TABLE `groups` (
  `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `can_add` tinyint(1) DEFAULT 0,
  `can_edit` tinyint(1) DEFAULT 0,
  `can_delete` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `groups`
--

INSERT INTO `groups` VALUES (1,'Пользователь',1,1,0),(2,'Администратор',1,1,1);

--
-- Table structure for table `manufacturer`
--

CREATE TABLE `manufacturer` (
  `manuf_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `city_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`manuf_id`),
  KEY `city_id` (`city_id`),
  CONSTRAINT `manufacturer_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `manufacturer`
--

INSERT INTO `manufacturer` VALUES (1,'OrtoPro',1),(2,'FlexMed',2),(3,'SilaZdorovya',3),(4,'BioKomfort',4),(5,'MedPromServis',5);

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `product_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `manuf_id` bigint(20) unsigned DEFAULT NULL,
  `price` decimal(10,2) NOT NULL CHECK (`price` >= 0),
  `release_date` date NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `manuf_id` (`manuf_id`),
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`supplier_id`) ON UPDATE CASCADE,
  CONSTRAINT `product_ibfk_2` FOREIGN KEY (`manuf_id`) REFERENCES `manufacturer` (`manuf_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `product`
--

INSERT INTO `product` VALUES (1,'Ortopedicheskaya_podushka',1,1,5900.00,'2025-09-15'),(2,'Stelki_ortopedicheskie',1,2,1200.00,'2024-12-20'),(3,'Nakalennik',2,3,3200.00,'2025-01-10'),(4,'Poyas_ortopedicheskiy',2,1,4500.00,'2024-06-12'),(5,'Vorochnik_shei',3,4,2300.00,'2025-03-05'),(6,'Nalokotnik',3,4,1100.00,'2025-11-20'),(7,'Bandazh_napreche',4,3,1500.00,'2025-07-01'),(8,'Kompresionnye_chulki',4,2,900.00,'2024-04-10'),(9,'Massazhnyy_myach',1,1,500.00,'2025-10-10'),(10,'Ortopedicheskiy_kovrik',2,2,2000.00,'2023-05-05');

--
-- Table structure for table `sale`
--

CREATE TABLE `sale` (
  `sale_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `customer_id` bigint(20) unsigned DEFAULT NULL,
  `sale_date` date NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` > 0),
  `price` decimal(10,2) NOT NULL CHECK (`price` >= 0),
  PRIMARY KEY (`sale_id`),
  KEY `product_id` (`product_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `sale_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON UPDATE CASCADE,
  CONSTRAINT `sale_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `sale`
--

INSERT INTO `sale` VALUES (1,1,1,'2025-12-05',1,5900.00),(2,1,2,'2025-11-18',2,5900.00),(3,2,1,'2025-10-22',1,1200.00),(4,3,3,'2025-08-10',1,3200.00),(5,4,2,'2025-06-15',1,4500.00),(6,5,3,'2025-03-12',3,2300.00),(7,6,1,'2025-12-10',3,1100.00),(8,7,2,'2025-09-20',1,1500.00),(9,8,3,'2024-12-28',2,900.00),(10,9,1,'2025-10-28',1,500.00);

--
-- Table structure for table `supplier`
--

CREATE TABLE `supplier` (
  `supplier_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `city_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`supplier_id`),
  KEY `city_id` (`city_id`),
  CONSTRAINT `supplier_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `supplier`
--

INSERT INTO `supplier` VALUES (1,'MedSnab',1),(2,'OrtoMir',2),(3,'ZdorovyePlus',3),(4,'MedTech',4),(5,'OrtoLogistika',5);


