CREATE DATABASE DBAUTH;

CREATE TABLE `DBAUTH`.`CREDIT_CARD` (
        `CARD_NUMBER` INT(16) UNSIGNED NULL DEFAULT NULL ,
        `PIN` INT(4) UNSIGNED NULL DEFAULT NULL ,
        `OWNER` VARCHAR(64) NULL DEFAULT NULL ,
        `XREWSTIKO_YPOLOIPO` DOUBLE NULL DEFAULT NULL ,
        `CCV` INT(4) NULL DEFAULT NULL ,
        `EXPIRATION_DATE` DATE NULL DEFAULT NULL ,

        PRIMARY KEY (`CARD_NUMBER`)

        ) ENGINE = InnoDB;

CREATE TABLE `DBAUTH`.`LOGARIASMOS_EBANKING` (
        `USERNAME` VARCHAR(64) NULL DEFAULT NULL ,
        `PASSWORD` VARCHAR(64) NULL DEFAULT NULL ,
        `UID` INT NULL DEFAULT NULL ,
        `NAME` VARCHAR(64) NULL DEFAULT NULL ,
        `SURNAME` VARCHAR(64) NULL DEFAULT NULL ,
        `AFM` VARCHAR(9) NULL DEFAULT NULL ,
        `BIRTHDAY` DATE NULL DEFAULT NULL,
        `TELEPHONE` INT NULL DEFAULT NULL ,
        `EMAIL` VARCHAR(64) NULL DEFAULT NULL ,

        `CARD_NUMBER` INT(16) UNSIGNED NULL DEFAULT NULL ,

        PRIMARY KEY (`USERNAME`) ,

        CONSTRAINT `FK_CARD_NUMBER`
            FOREIGN KEY (`CARD_NUMBER`) REFERENCES `CREDIT_CARD`(`CARD_NUMBER`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT

        ) ENGINE = InnoDB;

CREATE TABLE `DBAUTH`.`SINDEETAI` (
        # Credit card properties
        `CARD_NUMBER` INT(16) UNSIGNED NULL DEFAULT NULL ,
        `PIN` INT(4) UNSIGNED NULL DEFAULT NULL ,
        `OWNER` VARCHAR(64) NULL DEFAULT NULL ,
        `XREWSTIKO_YPOLOIPO` DOUBLE NULL DEFAULT NULL ,
        `CCV` INT(4) NULL DEFAULT NULL ,
        `EXPIRATION_DATE` DATE NULL DEFAULT NULL ,

        # e-banking properties
        `USERNAME` VARCHAR(64) NULL DEFAULT NULL ,
        `PASSWORD` VARCHAR(64) NULL DEFAULT NULL ,
        `UID` INT NULL DEFAULT NULL ,
        `NAME` VARCHAR(64) NULL DEFAULT NULL ,
        `SURNAME` VARCHAR(64) NULL DEFAULT NULL ,
        `AFM` VARCHAR(9) NULL DEFAULT NULL ,
        `BIRTHDAY` DATE NULL DEFAULT NULL,
        `TELEPHONE` INT NULL DEFAULT NULL ,
        `EMAIL` VARCHAR(64) NULL DEFAULT NULL ,

        PRIMARY KEY (`CARD_NUMBER`),

        CONSTRAINT `FK_CARD_NUMBER2`
            FOREIGN KEY (`CARD_NUMBER`) REFERENCES `CREDIT_CARD`(`CARD_NUMBER`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT,

        CONSTRAINT `FK_USERNAME`
            FOREIGN KEY (`USERNAME`) REFERENCES `LOGARIASMOS_EBANKING`(`USERNAME`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT

        ) ENGINE = InnoDB;

CREATE TABLE `DBAUTH`.`DANEIO` (
        `LOAN_ID` INT NULL DEFAULT NULL ,
        `INTEREST` DOUBLE NULL DEFAULT NULL ,
        `DURATION` INT NULL DEFAULT NULL ,
        `ALIKTO_KEFALAIO` DOUBLE NULL DEFAULT NULL ,
        `DANEISMENO_KEFALAIO` DOUBLE NULL DEFAULT NULL ,
        `YPOLOIPOMENES_DOSEIS` INT NULL DEFAULT NULL ,

        PRIMARY KEY (`LOAN_ID`)

        ) ENGINE = InnoDB;

# Logariasmos e-banking parakolouthei daneio
CREATE TABLE `DBAUTH`.`PARAKOLOUTHEI` (
        `USERNAME` VARCHAR(64) NULL DEFAULT NULL ,
        `LOAN_ID` INT NULL DEFAULT NULL,

        CONSTRAINT `FK_LOAN_ID`
            FOREIGN KEY (`LOAN_ID`) REFERENCES `DANEIO`(`LOAN_ID`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT,

        CONSTRAINT `FK_USERNAME2`
            FOREIGN KEY (`USERNAME`) REFERENCES `LOGARIASMOS_EBANKING`(`USERNAME`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT

        ) ENGINE = InnoDB;

CREATE TABLE `DBAUTH`.`LOGARIASMOS_KATATHESEWN` (
        `IBAN` VARCHAR(64) NULL DEFAULT NULL ,
        `BIC` VARCHAR(64) NULL DEFAULT NULL ,
        `DIATHESIMO_YPOLOIPO` DOUBLE NULL DEFAULT NULL ,
        `LOGISTIKO_YPOLOIPO` DOUBLE NULL DEFAULT NULL ,
        `CARD_NUMBER` INT(16) UNSIGNED NULL DEFAULT NULL ,

        PRIMARY KEY (`IBAN`)

        ) ENGINE = InnoDB;

# Logariasmos katathesewn sindeetai me logariasmo e-banking
CREATE TABLE `DBAUTH`.`SINDEETAI2` (
        `USERNAME` VARCHAR(64) NULL DEFAULT NULL ,
        `IBAN` VARCHAR(64)  NULL DEFAULT NULL ,

        CONSTRAINT `FK_IBAN`
            FOREIGN KEY (`IBAN`) REFERENCES `LOGARIASMOS_KATATHESEWN`(`IBAN`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT,

        CONSTRAINT `FK_USERNAME3`
            FOREIGN KEY (`USERNAME`) REFERENCES `LOGARIASMOS_EBANKING`(`USERNAME`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT

        ) ENGINE = InnoDB;

# Daneio apoplirwnetai apo logariasmo katathesewn
CREATE TABLE `DBAUTH`.`APOPLIRWNETAI` (
        # Loand properties
        `LOAN_ID` INT NULL DEFAULT NULL ,
        `INTEREST` DOUBLE NULL DEFAULT NULL ,
        `DURATION` INT NULL DEFAULT NULL ,
        `ALIKTO_KEFALAIO` DOUBLE NULL DEFAULT NULL ,
        `DANEISMENO_KEFALAIO` DOUBLE NULL DEFAULT NULL ,
        `YPOLOIPOMENES_DOSEIS` INT NULL DEFAULT NULL ,

        # Logariasmos katathesewn properties
        `IBAN` VARCHAR(64) NULL DEFAULT NULL ,
        `BIC` VARCHAR(64) NULL DEFAULT NULL ,
        `DIATHESIMO_YPOLOIPO` DOUBLE NULL DEFAULT NULL ,
        `LOGISTIKO_YPOLOIPO` DOUBLE NULL DEFAULT NULL ,

        PRIMARY KEY (`LOAN_ID`)

        ) ENGINE = InnoDB;

# Karta sinallasetai me logariasmo katathesewn
CREATE TABLE `DBAUTH`.`SINALASSETAI` (
        `SINALLAGMA` VARCHAR(8) NULL DEFAULT NULL ,
        `POSO` DOUBLE NULL DEFAULT NULL ,
        `SXOLIO` TEXT  NULL DEFAULT NULL ,
        `DATE` DATE NULL DEFAULT NULL,

        `IBAN` VARCHAR(64) NULL DEFAULT NULL ,
        `CARD_NUMBER` INT(16) UNSIGNED NULL DEFAULT NULL ,

        CONSTRAINT `FK_IBAN2`
            FOREIGN KEY (`IBAN`) REFERENCES `LOGARIASMOS_KATATHESEWN`(`IBAN`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT,

        CONSTRAINT `FK_CARD_NUMBER3`
            FOREIGN KEY (`CARD_NUMBER`) REFERENCES `LOGARIASMOS_EBANKING`(`CARD_NUMBER`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT
        ) ENGINE = InnoDB;


# Logariasmos katathesewn (apostoleas) sinallasetai me logariasmo katathesewn (paraliptis)
CREATE TABLE `DBAUTH`.`SINALASSETAI2` (
        `APOSTOLEAS IBAN` VARCHAR(64) NULL DEFAULT NULL,
        `PARALIPTIS IBAN` VARCHAR(64) NULL DEFAULT NULL,

        PRIMARY KEY ( `APOSTOLEAS IBAN`, `PARALIPTIS IBAN`),

        CONSTRAINT `FK_APOSTOLEAS_IBAN`
            FOREIGN KEY (`APOSTOLEAS IBAN`) REFERENCES `LOGARIASMOS_KATATHESEWN`(`IBAN`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT,

        CONSTRAINT `FK_PARALIPTIS_IBAN`
            FOREIGN KEY (`PARALIPTIS IBAN`) REFERENCES `LOGARIASMOS_KATATHESEWN`(`IBAN`)
            ON DELETE RESTRICT
            ON UPDATE RESTRICT
        ) ENGINE = InnoDB;