--
-- Host: localhost    Database: user110
-- ------------------------------------------------------


--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `account_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` char(30) DEFAULT NULL,
  PRIMARY KEY (`account_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `accounts`
--

INSERT INTO `accounts` VALUES (1,'admin','123456'),(2,'user1','password'),(3,'user2','harderpassword');

--
-- Table structure for table `letters`
--

CREATE TABLE `letters` (
  `letter_id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) DEFAULT NULL,
  `content` text DEFAULT NULL,
  `send_date` date DEFAULT NULL,
  PRIMARY KEY (`letter_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `letters`
--

INSERT INTO `letters` VALUES (1,'Специальное предложение','Получите скидку 20% на наши товары!','2024-10-01'),(2,'Новый продукт','Представляем вам наш новый продукт! Узнайте больше...','2024-10-05'),(3,'Поздравления','Поздравляем наших подписчиков с наступающим праздником!','2024-10-10');

--
-- Table structure for table `mailing`
--

CREATE TABLE `mailing` (
  `mailing_id` int(11) NOT NULL AUTO_INCREMENT,
  `letter_id` int(11) DEFAULT NULL,
  `subscriber_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`mailing_id`),
  KEY `letter_id` (`letter_id`),
  KEY `subscriber_id` (`subscriber_id`),
  CONSTRAINT `mailing_ibfk_1` FOREIGN KEY (`letter_id`) REFERENCES `letters` (`letter_id`),
  CONSTRAINT `mailing_ibfk_2` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`subscriber_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `mailing`
--

INSERT INTO `mailing` VALUES (1,1,2),(2,2,2),(3,2,3),(4,3,1),(5,3,2),(6,3,3);

--
-- Table structure for table `subscribers`
--

CREATE TABLE `subscribers` (
  `subscriber_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `email` varchar(80) NOT NULL,
  `account_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`subscriber_id`),
  UNIQUE KEY `email` (`email`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `subscribers_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Dumping data for table `subscribers`
--

INSERT INTO `subscribers` VALUES (1,'Иван','Иванов','ivanov@example.com',1),(2,'Мария','Петрова','petrova@example.com',2),(3,'Сергей','Кузнецов','kuznetsov@example.com',3);


