CREATE SCHEMA `main`;

CREATE TABLE `main`.`address`(`id` BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));

CREATE TABLE `main`.`contract`(`id` BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));

CREATE TABLE `main`.`customer`(`id` BIGINT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));

ALTER TABLE `main`.`address` ADD `city` VARCHAR(255);
ALTER TABLE `main`.`address` ADD `country` VARCHAR(255);
ALTER TABLE `main`.`address` ADD `house_number` VARCHAR(255);
ALTER TABLE `main`.`address` ADD `street` VARCHAR(255);
ALTER TABLE `main`.`address` ADD `zip_code` VARCHAR(255);

ALTER TABLE `main`.`contract` ADD `id_customer` BIGINT;
ALTER TABLE `main`.`contract` ADD `duration_months` INT;
ALTER TABLE `main`.`contract` ADD `amount` FLOAT;
ALTER TABLE `main`.`contract` ADD `contract_number` VARCHAR(255);
ALTER TABLE `main`.`contract` ADD `contract_date` DATETIME;

ALTER TABLE `main`.`customer` ADD `id_address` BIGINT;
ALTER TABLE `main`.`customer` ADD `email` VARCHAR(255);
ALTER TABLE `main`.`customer` ADD `phone` VARCHAR(255);
ALTER TABLE `main`.`customer` ADD `first_name` VARCHAR(255);
ALTER TABLE `main`.`customer` ADD `last_name` VARCHAR(255);

ALTER TABLE `main`.`address` MODIFY `city` VARCHAR(255);
ALTER TABLE `main`.`address` MODIFY `city` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`address` MODIFY `country` VARCHAR(255);
ALTER TABLE `main`.`address` MODIFY `country` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`address` MODIFY `house_number` VARCHAR(255);
ALTER TABLE `main`.`address` MODIFY `house_number` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`address` MODIFY `street` VARCHAR(255);
ALTER TABLE `main`.`address` MODIFY `street` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`address` MODIFY `zip_code` VARCHAR(255);
ALTER TABLE `main`.`address` MODIFY `zip_code` VARCHAR(255) NOT NULL;

ALTER TABLE `main`.`contract` MODIFY `id_customer` BIGINT;
ALTER TABLE `main`.`contract` MODIFY `id_customer` BIGINT NOT NULL;
ALTER TABLE `main`.`contract` MODIFY `duration_months` INT;
ALTER TABLE `main`.`contract` MODIFY `duration_months` INT NOT NULL;
ALTER TABLE `main`.`contract` MODIFY `amount` FLOAT;
ALTER TABLE `main`.`contract` MODIFY `amount` FLOAT NOT NULL;
ALTER TABLE `main`.`contract` MODIFY `contract_number` VARCHAR(255);
ALTER TABLE `main`.`contract` MODIFY `contract_number` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`contract` MODIFY `contract_date` DATETIME;
ALTER TABLE `main`.`contract` MODIFY `contract_date` DATETIME NOT NULL;

ALTER TABLE `main`.`customer` MODIFY `id_address` BIGINT;
ALTER TABLE `main`.`customer` MODIFY `id_address` BIGINT NOT NULL;
ALTER TABLE `main`.`customer` MODIFY `email` VARCHAR(255);
ALTER TABLE `main`.`customer` MODIFY `email` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`customer` MODIFY `phone` VARCHAR(255);
ALTER TABLE `main`.`customer` MODIFY `phone` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`customer` MODIFY `first_name` VARCHAR(255);
ALTER TABLE `main`.`customer` MODIFY `first_name` VARCHAR(255) NOT NULL;
ALTER TABLE `main`.`customer` MODIFY `last_name` VARCHAR(255);
ALTER TABLE `main`.`customer` MODIFY `last_name` VARCHAR(255) NOT NULL;

ALTER TABLE `main`.`contract` ADD CONSTRAINT fk__contract__id_customer FOREIGN KEY (`id_customer`) REFERENCES `main`.`customer`(`ID`);

ALTER TABLE `main`.`customer` ADD CONSTRAINT fk__customer__id_address FOREIGN KEY (`id_address`) REFERENCES `main`.`address`(`ID`);

CREATE INDEX fk__contract__id_customer ON `main`.`contract`(`id_customer`);

CREATE INDEX fk__customer__id_address ON `main`.`customer`(`id_address`);