CREATE DATABASE IF NOT EXISTS `PythonProjectUsers` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `PythonProjectUsers`;

DROP TABLE IF EXISTS `PythonProjectUsers`.`accounts`;
CREATE TABLE IF NOT EXISTS `PythonProjectUsers`.`accounts` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
  	`username` varchar(50) NOT NULL,
  	`password` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `PythonProjectUsers`.`accounts` (`id`, `username`, `password`) VALUES (1, 'test', 'test');

CREATE DATABASE IF NOT EXISTS `PythonProject` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `PythonProject`;

DROP TABLE IF EXISTS `PythonProject`.`projects`;
CREATE TABLE `PythonProject`.`projects` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `vals` int NOT NULL,
  `temp` varchar(45) NOT NULL,
  `unit` int NOT NULL,
  `data` json NOT NULL,
  `userId` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `projects_FK` (`userId`),
  CONSTRAINT `projects_FK` FOREIGN KEY (`userId`) REFERENCES `PythonProjectUsers`.`accounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

INSERT INTO `PythonProject`.`projects` VALUES (1,'testproj',16,'F',15,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]',1),(2,'testproj22',6,'C',30,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]',1),(3,'testproj3',8,'F',15,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]',1),(4,'testproj4',17,'F',15,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]',1),(5,'testproj5',17,'C',20,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]',1),(6,'testporj6',16,'F',20,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]',1);


DROP TABLE IF EXISTS `PythonProject`.`tabledata`;

CREATE TABLE `PythonProject`.`tabledata` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;

INSERT INTO `PythonProject`.`tabledata` VALUES (1,'[{\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"A static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"B static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"C static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"D static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"E static\"}, {\"data1\": 21.0, \"data2\": 44.0, \"data3\": 66.0, \"data4\": 88.0, \"data5\": 100.0, \"data6\": 110.0, \"data7\": 120.0, \"data8\": 130.0, \"component\": \"F static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"G static\"}, {\"data1\": 2.0, \"data2\": 4.0, \"data3\": 6.0, \"data4\": 8.0, \"data5\": 10.0, \"data6\": 12.0, \"data7\": 14.0, \"data8\": 16.0, \"component\": \"H static\"}]');

Update `PythonProject`.tabledata SET data = '[
    {"id": 1, "component": "A static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 2, "component": "B static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 3, "component": "C static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 4, "component": "D static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 5, "component": "E static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 6, "component": "F static", "data1": 21.0, "data2": 44.0, "data3": 66.0, "data4": 88.0, "data5": 100.0, "data6": 110.0, "data7": 120.0, "data8": 130.0
    },
    {"id": 7, "component": "G static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 8, "component": "H static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    }
]' where id > 0;

Update `PythonProject`.projects SET data = '[
    {"id": 1, "component": "A static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 2, "component": "B static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 3, "component": "C static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 4, "component": "D static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 5, "component": "E static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 6, "component": "F static", "data1": 21.0, "data2": 44.0, "data3": 66.0, "data4": 88.0, "data5": 100.0, "data6": 110.0, "data7": 120.0, "data8": 130.0
    },
    {"id": 7, "component": "G static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    },
    {"id": 8, "component": "H static", "data1": 2.0, "data2": 4.0, "data3": 6.0, "data4": 8.0, "data5": 10.0, "data6": 12.0, "data7": 14.0, "data8": 16.0
    }
]' where id > 0;