DROP DATABASE IF EXISTS cableCompany;
CREATE DATABASE cableCompany;
USE cableCompany;

CREATE TABLE customers
(
	customerID INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
	firstName VARCHAR(55) NOT NULL,
	middleName VARCHAR(55) NOT NULL,
	lastName VARCHAR(55) NOT NULL,
	email VARCHAR(55) NULL,
	phone VARCHAR(20) NOT NULL,
	address VARCHAR(255) NOT NULL
);

CREATE TABLE accounts
(
	accountID INT AUTO_INCREMENT PRIMARY KEY,
	amount DOUBLE NOT NULL,
	customer_id INT UNSIGNED NOT NULL,
	CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customers(customerID)
	ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE plans
(
	planID INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(32) NOT NULL,
	monthly_fee DOUBLE NOT NULL
);

CREATE TABLE payments
(
	paymentID INT AUTO_INCREMENT PRIMARY KEY,
	paymentAmount DOUBLE NOT NULL,
	month TINYINT NOT NULL,
	year YEAR NOT NULL,
	dateOfPayment DATETIME NOT NULL,
	customer_id INT UNSIGNED NOT NULL,
	plan_id INT UNSIGNED NOT NULL,
	CONSTRAINT FOREIGN KEY (customer_id) REFERENCES customers(customerID),
	CONSTRAINT FOREIGN KEY (plan_id) REFERENCES plans (planID),
	UNIQUE KEY (customer_id, plan_id, month, year)
);

CREATE TABLE debtors
(
	customer_id INT NOT NULL,
	plan_id INT  NOT NULL,
	debt_amount DOUBLE NOT NULL,
	FOREIGN KEY (customer_id) REFERENCES customers(customerID) ,
	FOREIGN KEY (plan_id) REFERENCES plans(planID) ,
	PRIMARY KEY (customer_id, plan_id)
);

INSERT INTO customers (firstName, middleName, lastName, email, phone, address)
VALUES
( 'Ivan', 'Todorov', 'Petrov', 'Ivan33@gmail.com', '0884123456', 'Sofia- Mladost'),
( 'Ivanka', 'Toshkova', 'Petkova', 'sunshine35@gmail.com', '0888257826', 'Plovdiv- Mladost'),
( 'Joro', 'Mitkov', 'Kuchkov', 'Joro12@abv.bg', '0882143552', 'Sliven- Drujba'),
( 'Filip', 'Ivanov', 'Georgiev', 'Filqka11@mail.bg', '0889134457', 'Varna- Moreto'),
( 'Hristo', 'Veselinov', 'Mitev', 'Icaka1134@gmail.com', '0882798410', 'England- Swindon'),
( 'Kalina', 'Dimitrova', 'Rasheva', 'bug21@abv.bg', '0881133357', 'Pleven- Mladost');

INSERT INTO accounts(amount, customer_id)
VALUES (3000, 1), (1200, 2), (200, 3), (700, 4), (10000, 5), (120, 6);

INSERT INTO plans(name, monthly_fee) VALUES ('Plan 1', 200),
											('Plan 2', 350),
                                            ('Plan 3', 1200),
                                            ('Plan 4', 120);

INSERT INTO payments (paymentAmount, month, year, dateOfPayment, customer_id, plan_id) VALUES
	(300, 1, 2016, '2016-03-03 16:35:00', 1, 3),
	(100, 1, 2016, '2016-03-04 17:35:00', 2, 2),
	(300, 1, 2016, '2016-03-05 11:43:00', 3, 1),
	(50, 1, 2016, '2016-03-06 15:11:00', 4, 4),
	(112, 1, 2016, '2016-03-07 09:51:00', 5, 3),
	(75, 1, 2016, '2016-03-08 10:15:00', 6, 2);

INSERT INTO debtors (customer_id, plan_id, debt_amount)
VALUES (2, 2, 300), (5, 3, 150), (1, 3, 65), (6, 2, 200);



DROP PROCEDURE IF EXISTS payMonthTax

delimiter |

CREATE PROCEDURE payMonthTax(IN customerId INT, IN tempSum DECIMAL, OUT success bit)

BEGIN

IF((SELECT amount
	FROM accounts
	WHERE amount >= tempSum
	AND customer_id = customerId
	) IS NULL)
THEN
	SELECT 'Invalid id or there is not enough money in the account!';
SET success = 0;
ELSE
	IF
	((
			SELECT paymentAmount
			FROM payments
			WHERE customer_id = customerId
	) = 0)
	THEN
		SELECT 'The fee is already paid';
		SET success = 1;
	ELSE
		SET success = 0;


START TRANSACTION;

UPDATE accounts SET amount = amount - tempSum
WHERE customer_id = customerId;

IF(ROW_COUNT() = 0)
THEN
	ROLLBACK;
ELSE
	UPDATE payments
	SET paymentAmount = paymentAmount - tempSum WHERE customer_id = customerId;

	IF(ROW_COUNT() = 0)
    THEN
		ROLLBACK;
	ELSE
SET success = 1;
SELECT 'Transaction complete'
COMMIT;

END IF;
END IF;
END IF;
END IF;
END;
|
delimiter ;



DROP PROCEDURE IF EXISTS paymentTime;

delimiter |

CREATE PROCEDURE paymentTime(IN monthOfPayment TINYINT, IN yearOfPayment INT)

BEGIN
DECLARE tempSum DECIMAL;
DECLARE tempCustomerId INT;
DECLARE tempPlanId INT;
DECLARE result INT;
DECLARE paymentCursor CURSOR FOR
SELECT customer_id, paymentAmount, plan_id
FROM payments
WHERE `month` = monthOfPayment
AND `year` = yearOfPayment;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET result = 1;

DROP TABLE IF EXISTS tempAccount;

CREATE TEMPORARY TABLE tempAccount(
tempId INT NOT NULL,
tempAmount INT NOT NULL,
CustomerID INT NOT NULL,
debt INT NOT NULL,
planId INT NOT NULL
)ENGINE = MEMORY;

START TRANSACTION;
OPEN paymentCursor;
SET result = 0;
payment_loop: WHILE (result = 0)
DO FETCH paymentCursor INTO tempCustomerId, tempSum, tempPlanId;

IF(result = 1) THEN LEAVE payment_loop;
ELSE
SET @RESULT = 0;
CALL payMonthTax(tempCustomerId, tempSum, @RESULT);

IF(@RESULT = 0)
THEN
SELECT tempCustomerId, tempSum;
INSERT INTO tempAccount (tempId, tempAmount, CustomerID, debt, planId)
SELECT a.accountID, a.amount, tempCustomerId, (tempSum - a.amount), tempPlanId
FROM accounts as a
WHERE a.customer_id = tempCustomerId;
END IF;
END IF;
END WHILE;
CLOSE paymentCursor;
SELECT * FROM tempAccount; #only for test

INSERT INTO debtors(customer_id, plan_id, debt_amount)
SELECT CustomerID, planId, debt
FROM tempAccount
ON DUPLICATE KEY UPDATE
debt_amount = debt_amount + debt;


UPDATE accounts
SET amount = 0 WHERE customer_id IN (SELECT CustomerID
									FROM tempAccount);

IF(ROW_COUNT() = 0)
THEN ROLLBACK;
ELSE

UPDATE payments
SET paymentAmount = 0 WHERE customer_id IN (SELECT CustomerID
											FROM tempAccount)
AND `month` = monthOfPayment
AND `year` = yearOfPayment;

IF(ROW_COUNT() = 0)
THEN ROLLBACK;
ELSE
COMMIT;
SELECT 'Transaction finished!';

END IF;
END IF;
DROP TABLE tempAccount;
END

|
delimiter ;

CALL paymentTime(1, 2016);



DROP EVENT IF EXISTS paymentEvent;

delimiter |

CREATE EVENT paymentEvent

ON SCHEDULE EVERY 1 MONTH
STARTS '2018-05-28 17:00:00'
DO
BEGIN
CALL paymentTime(MONTH(NOW()), YEAR(NOW()));
END;
|
DELIMITER ;


DROP VIEW IF EXISTS testView;

CREATE VIEW testView
AS SELECT customers.firstName, customers.middleName, customers.lastName, payments.year, payments.month,
plans.name, plans.monthly_fee
FROM customers JOIN payments JOIN plans
ON customers.customerId = payments.customer_id
AND plans.planId = payments.plan_id;

#SELECT * FROM testView;



DROP TRIGGER IF EXISTS planTrigger

DELIMITER |

CREATE TRIGGER  planTrigger BEFORE INSERT ON plans
FOR EACH ROW
BEGIN
IF(NEW.monthly_fee <= 10)
THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The fee must be over 10 BGN!';
END IF;
END
|
delimiter ;

#for test
#INSERT INTO plans(name, monthly_fee)
#VALUES ('Plan 1', 9);

DROP PROCEDURE IF EXISTS printInfo;

DELIMITER |


CREATE PROCEDURE printInfo(IN name1 VARCHAR(15), IN name2 VARCHAR(15), IN name3 VARCHAR(15))

BEGIN
SELECT customers.*, payments.paymentAmount
FROM customers JOIN payments
ON customers.customerId = payments.customer_id
WHERE firstName = name1 and middleName = name2 and lastName= name3;
END
|
DELIMITER ;

#CALL printInfo('Ivan', 'Todorov', 'Petrov');