USE demonstration;
DROP TABLE IF EXISTS `demonstration`.`transactions`;
DROP TABLE IF EXISTS `demonstration`.`credit_cards`;
DROP TABLE IF EXISTS `demonstration`.`accounts`;
DROP TABLE IF EXISTS `demonstration`.`customers`;
DROP TABLE IF EXISTS `demonstration`.`geographic_areas`;
DROP TABLE IF EXISTS `demonstration`.`branches`;

# CREATION OF DB
CREATE TABLE geographic_areas (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    area_name VARCHAR (50) NOT NULL,
    population INT NOT NULL,
    avg_income DOUBLE NOT NULL,
	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE branches (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	branch_code VARCHAR (50) NOT NULL,
    branch_name VARCHAR (50) NOT NULL,
    service_type INT NOT NULL,
	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	customer_code VARCHAR (50) NOT NULL,
    area_id INT UNSIGNED NOT NULL,
	firstname VARCHAR(50) NOT NULL,
	lastname VARCHAR(30) NOT NULL,
    vat_number VARCHAR (30) NOT NULL UNIQUE,
    phone_number VARCHAR (15) NOT NULL,
	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (area_id) REFERENCES geographic_areas(id)
);

CREATE TABLE accounts (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
	iban VARCHAR (30) NOT NULL UNIQUE,
    balance DOUBLE NOT NULL,
    interest_rate DOUBLE,
    withdrawal_amount DOUBLE,
	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT account_type CHECK ( (interest_rate IS NULL AND withdrawal_amount IS NOT NULL) OR (withdrawal_amount IS NULL AND interest_rate IS NOT NULL))

);

CREATE TABLE credit_cards (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    card_number VARCHAR (50) NOT NULL,
    issue_date DATE NOT NULL,
    expiration_date DATE NOT NULL,
    credit_limit DOUBLE NOT NULL,
    borrowing_rate DOUBLE NOT NULL,
    balance DOUBLE NOT NULL,
    card_level VARCHAR (30) NOT NULL,
	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

CREATE TABLE transactions (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    card_id INT UNSIGNED NOT NULL,
    branch_id INT UNSIGNED,
    charged_amount DOUBLE NOT NULL,
	updated_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (card_id) REFERENCES credit_cards(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
);

# POPULATION OF DB
INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('natus', 35477, '38655');
INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('rerum', 96787, '87543');
INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('iure', 54379, '46436');
INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('tuce', 56490, '63596');
INSERT INTO `geographic_areas` (`area_name`, `population`, `avg_income`) VALUES ('huri', 47921, '32557');

INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (1, 'Katlyn', 'Borer', '911141250', '519-221-0332', '3457284876');
INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (2, 'Bob', 'Borer', '987441523', '519-074-0762', '4345684876');
INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (3, 'Lamak', 'Borer', '914321964', '519-537-0542', '2137284876');
INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (4, 'Phillip', 'Borer', '845148750', '519-846-0907', '9957284876');
INSERT INTO `customers` (`area_id`, `firstname`, `lastname`, `vat_number`, `phone_number`, `customer_code`) VALUES (1, 'Cathrine', 'Borer', '365107050', '519-452-0738', '9007284876');

INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (1, '800945673678545788', 4563.85, 1000.0, NULL);
INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (2, '946748593678545788', 20663.85, 1000.0, NULL);
INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (3, '604945673678545788', 10863.85, 1000.0, NULL);
INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (4, '253945673678545788', 1463.85, 1000.0, NULL);
INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (5, '132945673678545788', 17463.85, 1000.0, NULL);
INSERT INTO `accounts` (`customer_id`, `iban`, `balance`, `withdrawal_amount`, `interest_rate`) VALUES (5, '032945673678545788', 17463.85, 1000.0, NULL);

INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (2, 1, '2002-06-16', '2020-04-24', '6000', 0.12, 3248, 2, '5487214963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (3, 1, '2004-04-11', '2020-03-30', '12000', 0.11, 9228, 3, '2345114963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (1, 2, '2006-02-11', '2016-04-01', '3000', 0.1, 6323, 1, '2547614963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 2, '9678314963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 3, '3334214963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 2, '2228314963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 3, '1114214963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 2, '9448314963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 1, '2364214963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (5, 4, '2008-01-09', '2018-07-01', '9000', 0.9, 2134, 1, '0978314963664243');
INSERT INTO `credit_cards` (`customer_id`, `account_id`, `issue_date`, `expiration_date`, `credit_limit`, `borrowing_rate`, `balance`, `card_level`, `card_number`) VALUES (4, 3, '2012-11-01', '2022-03-01', '15000', 0.5, 4545, 1, '3435614963664243');

INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('earum', 7, '245093597');
INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('leirm', 18, '097097587');
INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('gurtsa', 18, '364093597');
INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('iopto', 18, '175093597');
INSERT INTO `branches` (`branch_name`, `service_type`, `branch_code`) VALUES ('quore', 17, '998093597');


INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-01-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-02-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-03-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-04-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-05-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-06-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-07-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-08-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-09-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-10-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-11-01 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2017-12-01 18:06:19');

INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 53, '2018-05-13 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (2, 2, 104, '2018-05-16 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (3, 3, 203, '2018-06-03 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (4, 4, 24, '2018-06-23 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (5, 5, 13, '2018-05-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 123, '2018-06-23 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (2, 4, 133, '2018-06-13 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (3, 3, 1378, '2018-06-10 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (4, 2, 2413, '2018-10-06 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (5, 2, 313, '2018-06-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 1, 913, '2018-12-09 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');
INSERT INTO `transactions` (`card_id`, `branch_id`, `charged_amount`, `created_datetime`) VALUES (1, 5, 51, '2018-06-30 18:06:19');