DROP DATABASE IF EXISTS Imobiliaria;
CREATE DATABASE IF NOT EXISTS Imobiliaria;

-- Criando estrutura:
USE Imobiliaria;

DROP TABLE IF EXISTS locatario;
CREATE TABLE locatario(
    id INT NOT NULL AUTO_INCREMENT,
    nome TEXT,
    email VARCHAR(128) NOT NULL UNIQUE,
    telefone TEXT,
    PRIMARY KEY (id)
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

DROP TABLE IF EXISTS locador;
CREATE TABLE locador(
    id INT NOT NULL AUTO_INCREMENT,
    nome TEXT,
    email VARCHAR(128) NOT NULL UNIQUE,
    telefone TEXT,
    dia_repase DATETIME,
    PRIMARY KEY (id)
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

DROP TABLE IF EXISTS imovel;
CREATE TABLE imovel(
    id INT NOT NULL AUTO_INCREMENT,
    endereco TEXT NOT NULL,
    locador INT,
    PRIMARY KEY (id),
    FOREIGN KEY (locador) REFERENCES locador(id)
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

DROP TABLE IF EXISTS contrato;
CREATE TABLE contrato(
    id INT NOT NULL AUTO_INCREMENT,
    imovel INT,
    locador INT,
    locatario INT,
    inicio DATETIME,
    termino DATETIME,
    taxa_administracao FLOAT,
    valor_aluguel FLOAT,
    taxa_condominio FLOAT,
    valor_iptu FLOAT,
    History TEXT,
    PRIMARY KEY (id),
    FOREIGN KEY (imovel) REFERENCES imovel(id),
    FOREIGN KEY (locador) REFERENCES locador(id),
    FOREIGN KEY (locatario) REFERENCES locatario(id)
)
ENGINE=INNODB
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

-- Criando dados de exemplo
insert into locatario (
    nome,
    email,
    telefone
) values(
    'Perter Park',
    'peterpark@email.com',
    '111111111'
    ),(
    'Alfred Pennyworth',
    'alfredpennyworth@email.com',
    '222222222'
);

insert into locador (
    nome,
    email,
    telefone,
    dia_repase
) values(
    'Tony Stark',
    'tonystark@mail.com',
    '333333333',
    '2021-02-10 12:00:00.000000'
    ),(
    'Bruce Wayne',
    'bruce wayne@mail.com',
    '444444444',
    '2021-02-01 17:00:00.000000'
);

insert into imovel (
    endereco,
    locador
) values(
    '10880 Malibu Point',
    1
    ),(
    '1007 Mountain Drive, Gotham',
    2
);

insert into contrato (
    imovel,
    locador,
    locatario,
    inicio,
    termino,
    taxa_administracao,
    valor_aluguel,
    taxa_condominio,
    valor_iptu,
    History
) values(
    1,
    1,
    1,
    '2021-02-05 12:00:00.000000',
    '2022-02-04 12:00:00.000000',
    130.00,
    1300.00,
    62.89,
    130.00,
    'a:2:{s:7:"aluguel";i:1;s:7:"repasse";i:0;}'
    ),(
    2,
    2,
    2,
    '2021-02-01 12:00:00.000000',
    '2022-01-31 11:00:00.000000',
    190.00,
    1600.00,
    79.00,
    250.32,
    'a:2:{s:7:"aluguel";i:1;s:7:"repasse";i:1;}'
);