--
-- Host: 127.0.0.1    Database: lyubi_time_ms
-- ------------------------------------------------------


--
-- Current Database: `lyubi_time_ms`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `lyubi_time_ms` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `lyubi_time_ms`;

--
-- Table structure for table `daily_record`
--

CREATE TABLE `daily_record` (
  `id` int NOT NULL AUTO_INCREMENT,
  `record_date` date NOT NULL COMMENT '日期',
  `weight_loss_time` int DEFAULT '0' COMMENT '减肥',
  `english_time` int DEFAULT '0' COMMENT '英语',
  `html_css_time` int DEFAULT '0' COMMENT 'HTML/CSS',
  `js_time` int DEFAULT '0' COMMENT 'JavaScript',
  `php_time` int DEFAULT '0' COMMENT 'PHP',
  `database_time` int DEFAULT '0' COMMENT '数据库',
  `network_time` int DEFAULT '0' COMMENT '网络',
  `project_management_time` int DEFAULT '0' COMMENT '项目管理（含开发、测试、运维等全过程）',
  `full_stack_time` int DEFAULT '0' COMMENT '全栈研究',
  `huagai_time` int DEFAULT '0' COMMENT '华盖研究',
  `other_time` int DEFAULT '0' COMMENT '其他时间',
  `total_study_time` int GENERATED ALWAYS AS ((((((((((`english_time` + `html_css_time`) + `js_time`) + `php_time`) + `database_time`) + `network_time`) + `project_management_time`) + `full_stack_time`) + `huagai_time`) + `other_time`)) VIRTUAL COMMENT '每日学习时间合计',
  `huagai_income` decimal(10,2) DEFAULT '0.00' COMMENT '华盖收入',
  `satisfaction_id` tinyint DEFAULT NULL COMMENT '今日满意度',
  PRIMARY KEY (`id`),
  UNIQUE KEY `record_date` (`record_date`),
  KEY `fk_satisfaction_dictionary` (`satisfaction_id`),
  CONSTRAINT `fk_satisfaction_dictionary` FOREIGN KEY (`satisfaction_id`) REFERENCES `satisfaction_dictionary` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=144 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='每日有效时间记录表';

--
-- Dumping data for table `daily_record`
--

INSERT INTO `daily_record` (`id`, `record_date`, `weight_loss_time`, `english_time`, `html_css_time`, `js_time`, `php_time`, `database_time`, `network_time`, `project_management_time`, `full_stack_time`, `huagai_time`, `other_time`, `huagai_income`, `satisfaction_id`) VALUES (1,'2024-12-27',0,40,0,0,0,0,0,0,0,0,0,0.00,NULL),(2,'2024-12-28',10,280,0,0,0,0,0,0,0,0,0,0.00,NULL),(3,'2024-12-29',0,95,0,0,0,0,0,0,0,0,0,0.00,NULL),(4,'2024-12-30',35,320,60,0,0,0,0,0,0,0,0,0.00,NULL),(5,'2024-12-31',65,130,165,0,0,0,0,0,0,0,0,0.00,NULL),(6,'2025-01-01',30,120,30,0,0,0,0,0,0,0,0,0.00,NULL),(7,'2025-01-02',65,125,110,0,0,0,0,0,0,0,0,0.00,NULL),(8,'2025-01-03',45,130,105,0,0,0,0,0,0,0,0,0.00,NULL),(9,'2025-01-04',50,0,65,0,0,0,0,0,0,0,0,0.00,NULL),(10,'2025-01-05',30,0,30,0,0,0,0,0,0,0,0,0.00,NULL),(11,'2025-01-06',105,170,145,0,0,0,0,0,0,0,0,0.00,NULL),(12,'2025-01-07',55,110,130,0,0,0,0,0,0,0,180,0.00,NULL),(13,'2025-01-08',0,90,0,0,0,0,0,0,0,0,0,0.00,NULL),(14,'2025-01-09',70,75,175,0,0,0,0,0,0,0,0,0.00,NULL),(15,'2025-01-10',0,140,0,0,0,0,0,0,0,0,0,0.00,NULL),(16,'2025-01-11',0,35,0,0,0,0,0,0,0,0,0,0.00,NULL),(17,'2025-01-12',30,70,280,0,0,0,0,0,0,0,0,0.00,NULL),(18,'2025-01-13',60,75,40,0,0,0,0,0,0,0,115,0.00,NULL),(19,'2025-01-14',75,45,245,0,0,0,0,0,0,0,20,0.00,NULL),(20,'2025-01-15',0,0,95,0,0,0,0,0,0,0,0,0.00,NULL),(21,'2025-01-16',45,115,105,0,0,0,0,0,0,0,0,0.00,NULL),(22,'2025-01-17',0,0,410,60,0,0,0,0,0,0,0,0.00,NULL),(23,'2025-01-18',0,15,0,85,0,0,0,0,0,0,0,0.00,NULL),(24,'2025-01-19',0,105,95,45,0,0,0,0,0,0,0,0.00,NULL),(25,'2025-01-20',70,30,60,205,0,0,0,0,0,0,0,0.00,NULL),(26,'2025-01-21',0,0,90,60,0,0,0,0,0,0,0,0.00,NULL),(27,'2025-01-22',20,60,75,30,0,0,0,0,0,0,0,0.00,NULL),(28,'2025-01-23',55,45,85,0,0,0,0,0,0,0,0,0.00,NULL),(29,'2025-01-24',0,0,95,0,0,0,0,0,0,0,0,0.00,NULL),(30,'2025-01-25',55,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(31,'2025-01-26',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(32,'2025-01-27',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(33,'2025-01-28',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(34,'2025-01-29',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(35,'2025-01-30',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(36,'2025-01-31',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(37,'2025-02-01',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(38,'2025-02-02',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(39,'2025-02-03',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(40,'2025-02-04',0,0,0,0,0,0,0,0,0,0,115,0.00,NULL),(41,'2025-02-05',40,40,100,15,0,0,0,0,0,0,0,0.00,NULL),(42,'2025-02-06',30,45,155,0,0,0,0,0,0,0,0,0.00,NULL),(43,'2025-02-07',0,10,0,60,0,0,0,0,0,0,0,0.00,NULL),(44,'2025-02-08',0,0,0,0,0,0,0,0,0,0,80,0.00,NULL),(45,'2025-02-09',10,0,95,0,0,0,0,0,0,0,0,0.00,NULL),(46,'2025-02-10',40,30,25,0,0,0,0,0,0,0,0,0.00,NULL),(47,'2025-02-11',0,0,185,50,0,0,0,0,0,0,0,0.00,NULL),(48,'2025-02-12',30,0,145,0,0,0,0,0,0,0,0,0.00,NULL),(49,'2025-02-13',40,15,335,65,0,0,0,0,0,0,0,0.00,NULL),(50,'2025-02-14',45,0,25,40,0,0,0,0,0,0,0,0.00,NULL),(51,'2025-02-15',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(52,'2025-02-16',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(53,'2025-02-17',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(54,'2025-02-18',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(55,'2025-02-19',45,0,0,0,0,0,0,0,0,0,85,0.00,NULL),(56,'2025-02-20',30,0,0,40,0,0,0,0,0,0,85,0.00,NULL),(57,'2025-02-21',25,0,0,130,0,0,0,0,0,0,0,0.00,NULL),(58,'2025-02-22',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(59,'2025-02-23',75,0,0,175,0,0,0,0,0,0,35,0.00,NULL),(60,'2025-02-24',40,0,0,135,0,0,60,0,0,0,0,0.00,NULL),(61,'2025-02-25',20,0,0,125,0,0,90,0,0,0,0,0.00,NULL),(62,'2025-02-26',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(63,'2025-02-27',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(64,'2025-02-28',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(65,'2025-03-01',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(66,'2025-03-02',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(67,'2025-03-03',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(68,'2025-03-04',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(69,'2025-03-05',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(70,'2025-03-06',20,0,0,0,0,0,35,0,0,0,0,0.00,NULL),(71,'2025-03-07',0,0,0,0,0,0,70,0,0,0,0,0.00,NULL),(72,'2025-03-08',45,0,45,0,0,0,0,0,0,0,0,0.00,NULL),(73,'2025-03-09',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(74,'2025-03-10',15,0,0,0,0,0,35,0,0,0,0,0.00,NULL),(75,'2025-03-11',40,17,101,0,0,0,123,0,0,0,0,0.00,NULL),(76,'2025-03-12',0,0,0,18,0,0,23,0,0,0,0,0.00,NULL),(77,'2025-03-13',32,0,14,55,0,0,0,0,0,0,0,0.00,NULL),(78,'2025-03-14',0,0,0,0,0,219,0,0,0,0,0,0.00,NULL),(79,'2025-03-15',0,0,0,135,0,0,0,0,0,0,0,0.00,NULL),(80,'2025-03-16',63,0,0,48,0,0,0,0,0,0,0,0.00,NULL),(81,'2025-03-17',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(82,'2025-03-18',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(83,'2025-03-19',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(84,'2025-03-20',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(85,'2025-03-21',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(86,'2025-03-22',70,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(87,'2025-03-23',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(88,'2025-03-24',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(89,'2025-03-25',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(90,'2025-03-26',50,0,0,130,0,10,0,0,0,0,0,0.00,NULL),(91,'2025-03-27',20,0,80,0,0,295,20,0,0,0,0,0.00,NULL),(92,'2025-03-28',40,0,0,220,0,55,0,0,0,0,25,0.00,NULL),(93,'2025-03-29',0,0,0,5,0,0,0,0,0,0,0,0.00,NULL),(94,'2025-03-30',0,0,0,0,0,365,0,0,0,0,0,0.00,NULL),(95,'2025-03-31',30,0,0,0,0,285,0,0,0,0,0,0.00,NULL),(96,'2025-04-01',50,0,0,0,0,240,0,0,0,0,0,0.00,NULL),(97,'2025-04-02',80,0,0,270,0,0,0,0,0,0,0,0.00,NULL),(98,'2025-04-03',25,0,0,305,0,0,0,0,0,0,0,0.00,NULL),(99,'2025-04-04',113,0,0,298,0,0,0,0,0,0,0,0.00,NULL),(100,'2025-04-05',0,0,0,0,0,65,0,0,0,0,0,0.00,NULL),(101,'2025-04-06',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(102,'2025-04-07',0,0,0,0,0,160,0,0,0,0,0,0.00,NULL),(103,'2025-04-08',60,0,0,0,0,350,0,0,0,0,0,0.00,NULL),(104,'2025-04-09',25,0,0,0,0,520,0,0,0,0,0,0.00,NULL),(105,'2025-04-10',0,50,0,0,0,380,0,0,0,0,0,0.00,NULL),(106,'2025-04-11',0,45,0,0,0,115,0,0,0,0,0,0.00,NULL),(107,'2025-04-12',55,0,0,0,0,400,0,0,0,0,0,0.00,NULL),(108,'2025-04-13',0,0,0,0,0,100,0,0,0,0,0,0.00,NULL),(109,'2025-04-14',20,170,0,0,0,220,0,0,0,0,0,0.00,NULL),(110,'2025-04-15',50,145,0,0,0,230,0,0,0,0,0,0.00,NULL),(111,'2025-04-16',65,50,0,0,0,355,0,0,0,0,0,0.00,NULL),(112,'2025-04-17',0,50,0,0,0,275,0,0,0,0,0,0.00,NULL),(113,'2025-04-18',35,30,0,0,0,250,0,0,0,0,0,0.00,NULL),(114,'2025-04-19',35,30,0,0,0,170,0,0,0,0,0,0.00,NULL),(115,'2025-04-20',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(116,'2025-04-21',55,40,0,0,0,115,0,0,0,0,0,0.00,NULL),(117,'2025-04-22',30,85,0,0,0,195,0,0,0,0,0,0.00,NULL),(118,'2025-04-23',35,60,0,0,0,260,0,0,0,0,0,0.00,NULL),(119,'2025-04-24',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL),(120,'2025-04-25',35,95,0,0,0,90,0,0,0,0,0,0.00,NULL),(121,'2025-04-26',50,0,0,0,0,280,0,0,0,0,0,0.00,NULL),(122,'2025-04-27',50,0,0,0,0,345,0,0,0,0,0,0.00,NULL),(123,'2025-04-28',100,40,0,0,0,150,0,0,0,0,0,0.00,NULL),(124,'2025-04-29',95,45,0,0,0,280,0,0,0,50,0,0.00,NULL),(125,'2025-04-30',125,35,255,0,0,0,0,0,0,50,0,0.00,NULL),(126,'2025-05-01',0,55,160,0,0,0,0,0,0,0,0,0.00,NULL),(127,'2025-05-02',25,65,215,0,0,0,0,0,0,100,0,0.00,NULL),(128,'2025-05-03',0,45,135,0,0,0,0,0,0,20,0,0.00,NULL),(129,'2025-05-04',40,20,260,0,0,0,0,0,0,30,0,0.00,NULL),(130,'2025-05-05',25,35,110,0,0,0,0,0,0,0,0,0.00,NULL),(131,'2025-05-06',110,0,230,0,0,0,35,0,0,0,0,0.00,NULL),(132,'2025-05-07',100,50,125,0,0,0,40,0,0,0,0,0.00,NULL),(133,'2025-05-08',0,0,10,0,0,0,0,0,445,0,0,0.00,NULL),(134,'2025-05-09',0,0,0,0,375,30,0,0,0,0,0,0.00,NULL),(135,'2025-05-10',0,0,0,0,165,0,0,0,0,0,0,0.00,NULL),(136,'2025-05-11',0,0,0,0,90,0,0,0,0,0,0,0.00,NULL),(137,'2025-05-12',0,35,0,0,385,0,0,0,0,0,0,0.00,NULL),(138,'2025-05-13',10,0,0,0,175,0,0,0,0,0,0,0.00,NULL),(139,'2025-05-14',40,0,0,0,335,0,0,0,0,0,0,0.00,NULL),(140,'2025-05-15',40,30,0,0,205,0,0,0,0,0,0,0.00,NULL),(141,'2025-05-16',40,0,0,0,240,60,0,0,0,0,0,0.00,NULL),(142,'2025-05-17',0,0,0,0,0,100,0,180,0,0,0,0.00,NULL),(143,'2025-05-18',0,0,0,0,0,0,0,0,0,0,0,0.00,NULL);

--
-- Table structure for table `satisfaction_dictionary`
--

CREATE TABLE `satisfaction_dictionary` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  `level` varchar(20) NOT NULL COMMENT '满意度等级',
  PRIMARY KEY (`id`),
  UNIQUE KEY `level` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='满意度字典表，用于存储满意度等级，标准化满意度评价';

--
-- Dumping data for table `satisfaction_dictionary`
--

INSERT INTO `satisfaction_dictionary` (`id`, `level`) VALUES (4,'一般'),(5,'不满意'),(6,'很不满意'),(1,'很满意'),(2,'满意'),(3,'还算满意');

--
-- Temporary view structure for view `view_avg_daily_study_time`
--

/*!50001 DROP VIEW IF EXISTS `view_avg_daily_study_time`*/;
/*!50001 CREATE VIEW `view_avg_daily_study_time` AS SELECT 
 1 AS `record_date`,
 1 AS `avg_daily_study_time`*/;

--
-- Temporary view structure for view `view_week`
--

/*!50001 DROP VIEW IF EXISTS `view_week`*/;
/*!50001 CREATE VIEW `view_week` AS SELECT 
 1 AS `week_period`*/;

--
-- Dumping routines for database 'lyubi_time_ms'
--

--
-- Current Database: `lyubi_time_ms`
--

USE `lyubi_time_ms`;

--
-- Final view structure for view `view_avg_daily_study_time`
--

/*!50001 DROP VIEW IF EXISTS `view_avg_daily_study_time`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_avg_daily_study_time` AS select `dr`.`record_date` AS `record_date`,round((select ifnull(avg(`dr2`.`total_study_time`),0) from `daily_record` `dr2` where (`dr2`.`record_date` <= `dr`.`record_date`)),0) AS `avg_daily_study_time` from `daily_record` `dr` */;

--
-- Final view structure for view `view_week`
--

/*!50001 DROP VIEW IF EXISTS `view_week`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `view_week` AS select concat('第',week(`dr`.`record_date`,1),'周') AS `week_period` from `daily_record` `dr` */;


