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

CREATE TABLE `cableCompany`.`customers` (
    `customerID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `firstName` VARCHAR( 55 ) NOT NULL ,
    `middleName` VARCHAR( 55 ) NOT NULL ,
    `lastName` VARCHAR( 55 ) NOT NULL ,
    `email` VARCHAR( 55 ) NOT NULL ,
    `phone` VARCHAR( 20 ) NOT NULL ,
    `address` VARCHAR( 255 ) NOT NULL ,
    PRIMARY KEY ( `customerID` )
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

insert into customers (firstName, middleName, lastName, email, phone, address) VALUES ('Darko', 'Andreev', 'Mikan', 'andreev.darko@gmail.com', '0899543', 'Vidlic 36');
insert into customers (firstName, middleName, lastName, email, phone, address) VALUES ('Denis', 'Djamalov', 'Kalfov', 'd.kalfov@gmail.com', '089954123', 'Mladost 1');
insert into customers (firstName, middleName, lastName, email, phone, address) VALUES ('Niki', 'Budinov', 'Zlatkov', 'd.budinow@gmail.com', '089776453', 'Nadejda 1');



CREATE TABLE `cableCompany`.`accounts` (
    `accountID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
    `amount` DOUBLE NOT NULL ,
    `customer_id` INT UNSIGNED NOT NULL ,
    CONSTRAINT FOREIGN KEY ( `customer_id` )
        REFERENCES `cableCompany`.`customers` ( `customerID` )
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

insert into accounts (amount, customer_id) VALUES (5467.87, 1);
insert into accounts (amount, customer_id) VALUES (4567.23, 2);
insert into accounts (amount, customer_id) VALUES (1998.32, 3);


CREATE TABLE `cableCompany`.`plans` (
    `planID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR( 25 ) NOT NULL,
    `monthly_fee` DOUBLE NOT NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

insert into plans (name, monthly_fee) VALUES ('plan1', 500);
insert into plans (name, monthly_fee) VALUES ('plan2', 178);
insert into plans (name, monthly_fee) VALUES ('plan3', 800);

CREATE TABLE `cableCompany`.`contracts` (
    `contractID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
    `contractDate` DATE NOT NULL ,
    `customer_id` INT UNSIGNED NOT NULL ,
    `plan_id` INT UNSIGNED NOT NULL ,
    CONSTRAINT FOREIGN KEY ( `customer_id` )
        REFERENCES `cableCompany`.`customers`( `customerID` ) ,
    CONSTRAINT FOREIGN KEY ( `plan_id` )
        REFERENCES `cableCompany`.`plans` ( `planID` ) ,
    UNIQUE KEY( `customer_id`, `plan_id` )
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

insert into contracts (contractDate, customer_id, plan_id ) VALUES ('2017-05-25', 1, 2);
insert into contracts (contractDate, customer_id, plan_id ) VALUES ('2013-03-14', 2, 1);
insert into contracts (contractDate, customer_id, plan_id ) VALUES ('2016-11-10', 1, 3);




CREATE TABLE `cableCompany`.`payments`(
    `paymentID` INT AUTO_INCREMENT PRIMARY KEY ,
    `contract_id` INT UNSIGNED NOT NULL ,
    `paymentAmount` DOUBLE NOT NULL ,
    `month` TINYINT NOT NULL ,
    `year` YEAR NOT NULL ,
    `dateOfPayment` DATETIME NOT NULL ,
    CONSTRAINT FOREIGN KEY ( `contract_id` )
        REFERENCES `cableCompany`.`contracts`( `contractID` ) ,
    UNIQUE KEY( `contract_id`, `month`, `year` )
)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

insert into payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES (2, 1768.34, 05, 2013, '2015-04-11');
insert into payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES (1, 2367.22, 11, 2015, '2016-03-16');
insert into payments (contract_id, paymentAmount, month, year, dateOfPayment) VALUES (3, 1567.11, 06, 2012, '2005-05-12');


CREATE TABLE `cableCompany`.`debtors`(
    `customer_id` INT UNSIGNED NOT NULL ,
    `plan_id` INT UNSIGNED NOT NULL ,
    `debt_amount` DOUBLE NOT NULL ,
    FOREIGN KEY ( `customer_id` )
        REFERENCES `cableCompany`.`customers`( `customerID` ) ,
    FOREIGN KEY ( `plan_id` )
        REFERENCES `cableCompany`.`plans`( `planID` ) ,
    PRIMARY KEY ( `customer_id`, `plan_id` )
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

insert into debtors (customer_id, plan_id, debt_amount) VALUES (3, 1, 865.13);