asd

CREATE DATABASE 42_Iva;
USE 42_Iva;
CREATE TABLE students(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
egn VARCHAR (10) NOT NULL UNIQUE,
phone VARCHAR(10) NULL DEFAULT NULL,
class VARCHAR(5) NULL DEFAULT NULL
);
INSERT INTO students(name, egn, address, phone, class)
VALUES
('Ilyan Ivanov', '9401150045', 'Sofia-Mladost', '0895345123', '10'),
('Ivan Iliev', '9510104512', 'Sofia-Lulin', '0896786453', '11'),
('Elena Petrovs', '9505052154', 'Sofia-Mladost2', '0899123456', '11'),
('Ivan Iliev', '9510104542', 'Sofia-Mladost3', '0897123456', '11'),
('Maria Dimova', '9510104547', 'Sofia-Mladost4', '088898654', '11'),
('Antoaneta Ivanova', '9411104547', 'Sofia-Krasno selo', '0877123456', '10');

CREATE TABLE sports(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
INSERT INTO sports
VALUES
(NULL, 'football'),
(NULL, 'voleyball');

CREATE  TABLE coaches(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
egn VARCHAR (10) NOT NULL UNIQUE
);
INSERT INTO coaches(name, egn)
VALUES
('Ivan Petrov', '75090412'),
('Petyr Dimitrov', '2345678901'),
('Kaloyan Kolev', '3456789112');
drop table sportGroups;
CREATE TABLE sportGroups(
id INT AUTO_INCREMENT PRIMARY KEY,
location VARCHAR(50) NOT NULL,
dayOfWeek ENUM('Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun'),
hourOfTraining TIME NOT NULL,
sport_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (sport_id) REFERENCES sports(id),
coach_id INT ,
CONSTRAINT FOREIGN KEY(coach_id) REFERENCES coaches(id),
UNIQUE KEY (location, dayOfWeek,  hourOfTraining)
);
INSERT INTO sportGroups(location, dayOfWeek, hourOfTraining, sport_id, coach_id)
VALUES
('Sofia-Mladost1', 'Mon', '8:00:00', 1, 350),
('Sofia-Mladost1', 'Fri', '9:00:00', 1, 354),
('Sofia-Lulin', 'Fri', '8:00:00', 2, 354),
('Sofia-Lulin', 'Fri', '9:30:00', 2, 355),
('Plovdiv', 'Tue', '12:00:00', 1, 354);
INSERT INTO sportGroups(location, dayOfWeek, hourOfTraining, sport_id, coach_id)
VALUES
('Plovdiv', 'Fri', '12:00:00', 1, null);
drop table sportgr_student;

CREATE TABLE sportgr_student(
student_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(id),
sportgr_id INT NOT NULL,
CONSTRAINT FOREIGN KEY (sportgr_id) REFERENCES sportgroups(id),
PRIMARY KEY (student_id, sportgr_id)
);
INSERT INTO sportgr_student
VALUES
(1,31),
(2,31),
(3,32),
(4,32),
(5,31),
(6,32);

CREATE TABLE programmers(
id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    startWorkingDate DATE,/**YYYY-MM-DD**/
    teamLead_id INT NULL DEFAULT NULL,
    CONSTRAINT FOREIGN KEY (teamLead_id) REFERENCES programmers(id)
);

INSERT INTO programmers (name, address, startWorkingDate, teamLead_id)
VALUES
('Ivan Ivanov', 'Sofia', '1999-05-25', NULL),
('Georgi Petkov Todorov', 'Bulgaria - Sofia Nadezhda, bl. 35', '2002-12-01', 1),
('Todor Petkov', 'Sofia - Liylin 7', '2009-11-01', 1),
('Sofiq Dimitrova Petrova', 'Sofia - Mladost 4, bl. 7', '2010-01-01', 1),
('Teodor Ivanov Stoyanov', 'Sofia - Obelya, bl. 48', '2011-10-01', NULL),
('Iliya Stoynov Todorov', 'Sofia Nadezhda, bl. 28', '2000-02-01', 5),
('Mariela Dimitrova Yordanova', 'Sofia - Knyajevo, bl. 17', '2005-05-01', 5),
('Elena Miroslavova Georgieva', 'Sofia - Krasno Selo, bl. 27', '2008-04-01', 5),
('Teodor Milanov Milanov', 'Sofia - Lozenetz', '2012-04-01', 5);
drop table taxesPayment;

CREATE TABLE taxesPayment(
id int auto_increment primary key,
student_id int not null,
group_id int not null,
paymentAmount double not null,
month int,
year int,
dateOfPayment datetime not null,
constraint foreign key (student_id) references students(id),
constraint foreign key (group_id) references sportgroups(id)
);
INSERT INTO taxesPayment (student_id, group_id, paymentAmount, month, year, dateOfPayment)
VALUES
(1,11, 200, 3, 2022, now()),
(1,30, 200, 4, 2022, now()),
(1,33, 200, 5, 2022, now()),
(1,11, 200, 6, 2022, now()),
(1,11, 200, 7, 2022, now()),
(1,11, 200, 8, 2022, now()),
(1,30, 200, 9, 2022, now()),
(1,33, 200, 10, 2022, now()),
(1,33, 200, 11, 2022, now()),
(1,33, 200, 12, 2022, now()),
(4,11, 200, 1, 2022, now()),
(4,49, 200, 2, 2022, now()),
(4,49, 200, 3, 2022, now()),
(4,49, 200, 4, 2022, now()),
(4,49, 200, 5, 2022, now()),
(4,11, 200, 6, 2022, now());

select count(coaches_id) as CountOfGroupWithCoach from sportgroups;
select sum(paymentAmount) as Allpayment
from taxespayment
where student_id=4;
select max(paymentAmount) as Maxpayment
from taxespayment
where student_id=4;
select group_id as GroupId, AVG(paymentAmount) as AvgOfAllPaymentPerGroup
from taxespayment
group by group_id;
select students.id,students.name as StudentNam, sum(tp.paymentAmount) as SumOfAllPaymentPerGroup ,tp.month as Month
from taxespayment as tp
join students on tp.student_id= students.id
GROUP BY month, student_id
having SumOfAllPaymentPerGroup >100
LIMIT 3;
CREATE VIEW student_classes
 AS SELECT students.name, students.class, taxespayment.group_id
 FROM students
 LEFT JOIN taxespayment
 on students.id = taxespayment.student_id
 JOIN sportgr_student
 on students.id=sportgr_student.student_id
 JOIN sportgroups
 on sportgr_student.sportgr_id = sportgroups.sport_id
 join coaches
 on sportgroups.coach_id = coaches.id
 join sports
 on sportgroups.sport_id= sports.id
 where sportgroups.dayOfWeek='Mon' AND sportgroups.hourOfTraining='8:00:00'
 and  coaches.name='Ivan Petrov'
 and sports.name='Football';



-- EX1 - Da se izvedat vsichki sportove sus sportnite grupi, koito sa SAMO za Sofia izpolzvaiki psevdonimi na koloni
SELECT
    sp.name AS sport_name,
    sg.location AS group_location,
    sg.dayOfWeek AS group_day,
    sg.hourOfTraining AS training_hour,
    c.name AS coach_name
FROM
    sportGroups sg
JOIN
    sports sp ON sg.sport_id = sp.id
JOIN
    coaches c ON sg.coach_id = c.id
WHERE
    sg.location LIKE 'Sofia%';

-- EX2.1 - Da se sformirat dvoiki uchenici na baza na sportna grupa v koqto trenirat
SELECT
    s1.name AS student_1,
    s2.name AS student_2,
    sg.location AS sport_group_location,
    sg.dayOfWeek AS sport_group_day,
    sg.hourOfTraining AS sport_group_time
FROM sportgr_student sg1
JOIN sportgr_student sg2 ON sg1.sportgr_id = sg2.sportgr_id
JOIN students s1 ON sg1.student_id = s1.id
JOIN students s2 ON sg2.student_id = s2.id
JOIN sportGroups sg ON sg.id = sg1.sportgr_id
WHERE sg1.student_id < sg2.student_id;

-- EX2.2 - Da se sformirat dvoiki uchenici na baza na sportna grupa v koqto trenirat
SELECT DISTINCT
    s1.name AS student1,
    s2.name AS student2
FROM sportgr_student sg1
JOIN sportgr_student sg2 ON sg1.sportgr_id = sg2.sportgr_id AND sg1.student_id < sg2.student_id
JOIN students s1 ON sg1.student_id = s1.id
JOIN students s2 ON sg2.student_id = s2.id
JOIN sportgroups sg ON sg.id = sg1.sportgr_id;


-- Using union all
SELECT name, egn, address
FROM students
WHERE address LIKE '%mladost%'

UNION

SELECT name, egn, address
FROM students
WHERE address LIKE '%mladost%';

delete from coaches where id=1;

ALTER TABLE sportgroups
DROP FOREIGN KEY sportgroups_ibfk_2;

ALTER TABLE sportgroups
ADD CONSTRAINT FOREIGN KEY coach_id_key (coach_id) REFERENCES coaches(id)
ON DELETE SET NULL ON UPDATE CASCADE;

delete from coaches
where id=2;
update coaches
set id=350 where id=3;

DELETE FROM sportgroups
WHERE id=32;

DELIMITER $$
CREATE TRIGGER before_sportgr_delete
BEFORE DELETE ON sportgroups
FOR EACH ROW
BEGIN
    DECLARE new_sportgr_id INT;

    SELECT id INTO new_sportgr_id
    FROM sportgroups
    WHERE location = OLD.location AND id != OLD.id
    LIMIT 1;

    IF new_sportgr_id IS NOT NULL THEN
        UPDATE sportgr_student
        SET sportgr_id = new_sportgr_id
        WHERE sportgr_id = OLD.id;
    END IF;

END$$
DELIMITER ;


DELIMITER $$
CREATE procedure checkMonthTax(IN studId INT, IN groupId INT, IN paymentMonth INT, IN paymentYear INT)
BEGIN
DECLARE result char(1);
SET result = 0;
    IF( (SELECT paymentAmount
        FROM taxespayment
        WHERE student_id = studId
        AND group_id = groupId
        AND month = paymentMonth
        AND year = paymentYear) IS NOT NULL)
    THEN
        SET result = 1;
    ELSE
        SET result = 0;
    END IF;
SELECT result AS IsTaxPayed;
END$$;
DELIMITER ;
CALL checkMonthTax(1,1,1,2022);

SELECT * FROM taxespayment;

DELIMITER $$
CREATE procedure checkPaymentPeriod(IN studId INT, IN groupId INT, IN paymentYear INT)
BEGIN
DECLARE countOfMonths int;
DECLARE monthStr VARCHAR(10);
DECLARE yearStr VARCHAR(10);
SET monthStr = 'MONTH';
SET yearStr = 'YEAR';
    SELECT COUNT(*)
    INTO countOfMonths
    FROM taxespayment
    WHERE student_id = studId
    AND group_id = groupId
    AND year = paymentYear;

CASE countOfMonths
    WHEN 0 THEN SELECT 'This student has not paid for this group/year!' as PAYMENT_PERIOD;
    WHEN 1 THEN SELECT CONCAT('ONE_',monthStr) as PAYMENT_PERIOD;
    WHEN 3 THEN SELECT CONCAT('THREE_',monthStr,'S') as PAYMENT_PERIOD;
    WHEN 6 THEN SELECT CONCAT('SIX_',monthStr,'S') as PAYMENT_PERIOD;
    WHEN 12 THEN SELECT yearStr as PAYMENT_PERIOD;
    ELSE
            SELECT CONCAT(countOfMonths, monthStr, 'S') as PAYMENT_PERIOD;
        END CASE;
END$$;
DELIMITER ;


DROP PROCEDURE getAllPaymentsAmount;
DELIMITER $$
CREATE PROCEDURE getAllPaymentsAmount(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studld INT)
BEGIN
DECLARE iterator int;
DECLARE sum int;
DECLARE paymentAmount_result int;
SET sum = 0;
IF (firstMonth >= secMonth)
    THEN
        SELECT 'Please enter correct months!' as RESULT;
    ELSE IF( (SELECT COUNT(*)
            FROM taxesPayment
            WHERE student_id = studld) = 0) THEN SELECT 'Please enter correct student_id!' as RESULT;
        ELSE
            SET iterator = firstMonth;
            WHILE(iterator >= firstMonth AND iterator <= secMonth)
            DO
                SELECT paymentAmount
                INTO paymentAmount_result
                FROM taxespayment
                WHERE student_id = studld AND year = paymentYear AND month = iterator;
                SET sum = sum + paymentAmount_result;
                SET iterator = iterator + 1;
            END WHILE;
        END IF;
    END IF;
    SELECT sum AS result;
END;
DELIMITER ;




DROP PROCEDURE IF EXISTS getAllPaymentsAmount;
DELIMITER $$

CREATE PROCEDURE getAllPaymentsAmount(IN firstMonth INT, IN secMonth INT, IN paymentYear INT, IN studld INT)
BEGIN
    DECLARE iterator INT;
    DECLARE sum INT DEFAULT 0;
    DECLARE paymentAmount_result INT;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_result (
        result INT
    );

    TRUNCATE TABLE temp_result;

    IF (firstMonth >= secMonth) THEN
        INSERT INTO temp_result (result) VALUES ('Please enter correct months!');
    ELSEIF ( (SELECT COUNT(*) FROM taxesPayment WHERE student_id = studld) = 0) THEN
        INSERT INTO temp_result (result) VALUES ('Please enter correct student_id!');
    ELSE
        SET iterator = firstMonth;
        WHILE (iterator >= firstMonth AND iterator <= secMonth) DO
            SELECT paymentAmount
            INTO paymentAmount_result
            FROM taxesPayment
            WHERE student_id = studld AND year = paymentYear AND month = iterator;

            IF paymentAmount_result IS NOT NULL THEN
                SET sum = sum + paymentAmount_result;
            END IF;

            SET iterator = iterator + 1;
        END WHILE;

        INSERT INTO temp_result (result) VALUES (sum);
    END IF;

    SELECT * FROM temp_result;

    DROP TEMPORARY TABLE IF EXISTS temp_result;
END;
$$
DELIMITER ;