-- Konstantin Mihaylov N12 & Peter Mihaylov N16 XIIb
DROP DATABASE demo_db;

CREATE DATABASE demo_db;
USE demo_db;

-- TABLES
CREATE TABLE Person (
    id INTEGER AUTO_INCREMENT NOT NULL,
    p_name VARCHAR(50) NOT NULL,
    age INTEGER,
    avg_weight FLOAT DEFAULT 0,
    gender ENUM('M', 'F'),

    PRIMARY KEY (id)
);

CREATE TABLE Weight_data (
    id INTEGER NOT NULL,
    created_at DATETIME,
    weight FLOAT,
    p_id INTEGER,

    PRIMARY KEY (id),
    FOREIGN KEY (p_id) REFERENCES Person(id)
);

INSERT INTO Person (id, p_name, age, gender) VALUES (1, 'Ivan', 18, 'M');
INSERT INTO Person (id, p_name, age, gender) VALUES (2, 'Beatris', 18, 'F');
INSERT INTO Person (id, p_name, age, gender) VALUES (3, 'Fea', 15, 'F');


-- INSERT

DROP TRIGGER IF EXISTS person_weight_insert;

CREATE TRIGGER person_weight_insert AFTER INSERT ON Weight_data
FOR EACH ROW
    UPDATE Person SET avg_weight =
        (SELECT AVG(weight) FROM Weight_data WHERE p_id = NEW.p_id)
    WHERE NEW.p_id = Person.id;

INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (1, NOW(), 70, 1);
INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (2, DATE_FORMAT(NOW() ,'%Y-01-01'), 80, 1);
INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (6, DATE_FORMAT(NOW() ,'%Y-12-31'), 75, 1);
INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (7, DATE_FORMAT(NOW() ,'%Y-01-01'), 35, 2);
INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (8, DATE_FORMAT(NOW() ,'%Y-12-31'), 65, 2);
INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (3, DATE_SUB(NOW(), INTERVAL 2 MONTH), 60, 1);

INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (4, NOW(), 60, 2);
INSERT INTO Weight_data (id, created_at, weight, p_id) VALUES (5, NOW(), 40, 3);

SELECT * FROM Person;


-- UPDATE

DROP TRIGGER IF EXISTS person_weight_update;
CREATE TRIGGER person_weight_update AFTER UPDATE ON Weight_data
FOR EACH ROW
    UPDATE Person SET avg_weight =
        (SELECT AVG(weight) FROM Weight_data WHERE p_id = NEW.p_id)
    WHERE NEW.p_id = Person.id;

UPDATE Weight_data
 SET   weight = 90
 WHERE p_id = 3;

SELECT * FROM Person;


-- AVG WEIGHT

SELECT id, AVG(weight)
FROM Weight_data
WHERE p_id = 1 AND created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY id;


-- WEIGHT FROM BEGINNIG AND END OF YEAR
-- This example shows the results of using the ABS function on three different numbers.
SELECT start_of_year.p_id, ABS(start_of_year.weight - end_of_year.weight)
FROM Weight_data as start_of_year
LEFT JOIN Weight_data as end_of_year ON
end_of_year.created_at = DATE_FORMAT(NOW() ,'%Y-12-31')
AND start_of_year.p_id = end_of_year.p_id
WHERE start_of_year.created_at = DATE_FORMAT(NOW() ,'%Y-01-01')
GROUP BY start_of_year.p_id, ABS(start_of_year.weight - end_of_year.weight);


-- AVG MEN

SELECT AVG(avg_weight)
FROM Person
WHERE gender = 'M';


-- AVG FEMALE
SELECT AVG(avg_weight)
FROM Person
WHERE gender = 'F';