SET GLOBAL event_scheduler = ON; 

SET sql_mode = '';


GRANT SELECT, INSERT ON dbbridge.* TO 'dbbridge'@'%';
FLUSH PRIVILEGES;

CREATE DATABASE IF NOT EXISTS dbbridge;
USE dbbridge;

delimiter |

CREATE PROCEDURE UpdateDaySum ()
BEGIN
  IF (SELECT COUNT(day) FROM daysum WHERE day = DATE(CURRENT_TIMESTAMP)) > 0 THEN
    UPDATE daysum AS dest, (
      SELECT
        SUM(dc_input_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'dc_input_power',
        SUM(dc_output_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'dc_output_power',
        SUM(ac_input_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'ac_input_power',
        SUM(ac_output_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'ac_output_power'
      FROM (SELECT b1.*, b2.InsertTimeStamp AS b2InsertTimeStamp
        FROM bluetti b1, bluetti b2
        WHERE DATE(b1.InsertTimeStamp) >= DATE(CURRENT_TIMESTAMP)
          AND b1.ID = b2.ID + 1
          AND UNIX_TIMESTAMP(b1.InsertTimeStamp) - UNIX_TIMESTAMP(b2.InsertTimeStamp) < 600) b) AS src
      SET
	   	  dest.dc_input_power  = src.dc_input_power,
  		  dest.dc_output_power = src.dc_output_power,
	      dest.ac_input_power  = src.ac_input_power,
  	    dest.ac_output_power = src.ac_output_power
      WHERE dest.day = DATE(CURRENT_TIMESTAMP);
  ELSE
    INSERT INTO daysum
      SELECT
        DATE(InsertTimeStamp) AS day,
        SUM(dc_input_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'dc_input_power',
        SUM(dc_output_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'dc_output_power',
        SUM(ac_input_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'ac_input_power',
        SUM(ac_output_power*(UNIX_TIMESTAMP(InsertTimeStamp) - UNIX_TIMESTAMP(b2InsertTimeStamp))*0.00027777)/1000 AS 'ac_output_power'
      FROM (SELECT b1.*, b2.InsertTimeStamp AS b2InsertTimeStamp
        FROM bluetti b1, bluetti b2
        WHERE DATE(b1.InsertTimeStamp) >= DATE(CURRENT_TIMESTAMP)
          AND b1.ID = b2.ID + 1
          AND UNIX_TIMESTAMP(b1.InsertTimeStamp) - UNIX_TIMESTAMP(b2.InsertTimeStamp) < 600) b;
  END IF;
END|

delimiter ;

CREATE EVENT calc_daysum ON SCHEDULE EVERY 1 HOUR DO CALL UpdateDaySum();

CREATE TABLE `bluetti` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `InsertTimeStamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `device_type` INTEGER NOT NULL DEFAULT '0',
  `serial_number` INTEGER NOT NULL DEFAULT '0',
  `arm_version` INTEGER NOT NULL DEFAULT '0',
  `dsp_version` INTEGER NOT NULL DEFAULT '0',
  `dc_input_power` INTEGER NOT NULL DEFAULT '0',
  `ac_input_power` INTEGER NOT NULL DEFAULT '0',
  `ac_output_power` INTEGER NOT NULL DEFAULT '0',
  `dc_output_power` INTEGER NOT NULL DEFAULT '0',
  `power_generation` INTEGER NOT NULL DEFAULT '0',
  `total_battery_percent` INTEGER NOT NULL DEFAULT '0',
  `ac_output_on` INTEGER NOT NULL DEFAULT '0',
  `dc_output_on` INTEGER NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `Index_2` (`InsertTimeStamp`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE `config` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `kwh_price` FLOAT NOT NULL DEFAULT '0',
  `kwh_max` FLOAT NOT NULL DEFAULT '0',
  `start_date` DATETIME NOT NULL DEFAULT '2022-01-01 00:00:00',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO `config` (`ID`,`kwh_price`,`kwh_max`,`start_date`) VALUES 
 (1,0.56,6,'2022-09-30 00:00:00');
 
CREATE TABLE `daysum` (
  `day` DATE DEFAULT NULL,
  `dc_input_power` FLOAT DEFAULT NULL,
  `dc_output_power` FLOAT DEFAULT NULL,
  `ac_input_power` FLOAT DEFAULT NULL,
  `ac_output_power` FLOAT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `yeardays` (
  `day` INTEGER DEFAULT NULL,
  KEY `day` (`day`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `yeardays` (`day`) VALUES 
 (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45), (46), (47), (48), (49), (50), (51), (52), (53), (54), (55), (56), (57), (58), (59), (60), (61), (62), (63), (64), (65), (66), (67), (68), (69), (70), (71), (72), (73), (74), (75), (76), (77), (78), (79), (80), (81), (82), (83), (84), (85), (86), (87), (88), (89), (90), (91), (92), (93), (94), (95), (96), (97), (98), (99), (100), (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112), (113), (114), (115), (116), (117), (118), (119), (120), (121), (122), (123), (124), (125), (126), (127), (128), (129), (130), (131), (132), (133), (134), (135), (136), (137), (138), (139), (140), (141), (142), (143), (144), (145), (146), (147), (148), (149), (150), (151), (152), (153), (154), (155), (156), (157), (158), (159), (160), (161), (162), (163), (164), (165), (166), (167), (168), (169), (170), (171), (172), (173), (174), (175), (176), (177), (178), (179), (180), (181), (182), (183), (184), (185), (186), (187), (188), (189), (190), (191), (192), (193), (194), (195), (196), (197), (198), (199), (200), (201), (202), (203), (204), (205), (206), (207), (208), (209), (210), (211), (212), (213), (214), (215), (216), (217), (218), (219), (220), (221), (222), (223), (224), (225), (226), (227), (228), (229), (230), (231), (232), (233), (234), (235), (236), (237), (238), (239), (240), (241), (242), (243), (244), (245), (246), (247), (248), (249), (250), (251), (252), (253), (254), (255), (256), (257), (258), (259), (260), (261), (262), (263), (264), (265), (266), (265), (268), (269), (270), (271), (272), (273), (274), (275), (276), (277), (278), (279), (280), (281), (282), (283), (284), (285), (286), (287), (288), (289), (290), (291), (292), (293), (294), (295), (296), (297), (298), (299), (300), (301), (302), (303), (304), (305), (306), (307), (308), (309), (310), (311), (312), (313), (314), (315), (316), (317), (318), (319), (320), (321), (322), (323), (324), (325), (326), (327), (328), (329), (330), (331), (332), (333), (334), (335), (336), (337), (338), (339), (340), (341), (342), (343), (344), (345), (346), (347), (348), (349), (350), (351), (352), (353), (354), (355), (356), (357), (358), (359), (360), (361), (362), (363), (364), (365);
