DROP DATABASE IF EXISTS WeightDB;
CREATE DATABASE WeightDB;

USE WeightDB;

CREATE TABLE person(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    p_name VARCHAR(50) NOT NULL,
    average_weight INTEGER DEFAULT 0,
    gender ENUM('M', 'F') NOT NULL
);

CREATE TABLE weight_data(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    person_id INTEGER,
    weight INTEGER DEFAULT 0,
    FOREIGN KEY (person_id) REFERENCES person(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


DELIMITER $$
CREATE TRIGGER calculate_avg
    AFTER INSERT ON weight_data
    FOR EACH ROW
BEGIN
    UPDATE person
    SET average_weight = (SELECT AVG(weight) FROM weight_data
                          WHERE person.id = weight_data.person_id)
    WHERE id = New.person_id;
END$$
DELIMITER ;

INSERT INTO person(p_name, gender) VALUES('Ivancho', 'M');
INSERT INTO person(p_name, gender) VALUES('Mariika', 'F');

INSERT INTO weight_data(person_id, weight) VALUES(1, 70);
INSERT INTO weight_data(person_id, weight) VALUES(1, 80);
INSERT INTO weight_data(person_id, weight) VALUES(1, 80);

INSERT INTO weight_data(person_id, weight) VALUES(2, 45);
INSERT INTO weight_data(person_id, weight) VALUES(2, 50);

SELECT * FROM weight_data;
SELECT * FROM person;

SELECT AVG(wd.weight) as 'Average Weight(1 month)' FROM person p
JOIN weight_data wd
WHERE wd.created_at >= DATE_SUB(NOW(), INTERVAL 30 day) AND p.id = 1;

SELECT ABS(
    (SELECT weight FROM weight_data WHERE person_id = 1 ORDER BY weight ASC LIMIT 1) -
    (SELECT weight FROM weight_data WHERE person_id = 1 ORDER BY weight DESC LIMIT 1)
);

SELECT AVG(p.average_weight) as 'Avg female' FROM person p
WHERE p.gender = 'F';

SELECT AVG(p.average_weight) as 'Avg male' FROM person p
WHERE p.gender = 'M';