-- Geração de Modelo físico
-- Sql ANSI 2003 - brModelo.



CREATE TABLE forms_of_payment (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
institution_id INT(11),
description VARCHAR(255) NOT NULL
);

CREATE TABLE users (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
password VARCHAR(50) NOT NULL,
active TINYINT(1)
);

CREATE TABLE profiles (
user_id BIGINT,
name VARCHAR(255) NOT NULL,
sex TINYINT(1),
birthday DATE,
mailling TINYINT(1),
fk_mailling TINYINT(1),
admin TINYINT(1)
);

CREATE TABLE settings (
user_id BIGINT
);

CREATE TABLE expense_types (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL
);

CREATE TABLE spent (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
expense_id INT(11),
bank_options_id INT(11),
money_id INT(11),
charge_id INT(11),
taxes_id BIGINT,
institution_id INT(11),
description VARCHAR(255) NOT NULL,
value DECIMAL(11,2) NOT NULL,
expires DATE,
payment DATE,
charge_value DECIMAL(11,2) NOT NULL,
payment_value DECIMAL(11,2) NOT NULL,
paid TINYINT(1),
monthly TINYINT(2),
annually TINYINT(1),
weekly INT(11) NOT NULL
);

CREATE TABLE bank_options (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL
);

CREATE TABLE contacts (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
subject INT(11),
title VARCHAR(255) NOT NULL,
observation BLOB,
received DATETIME
);

CREATE TABLE help (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE wishlist (
user_id BIGINT,
money_id INT(11),
description VARCHAR(255) NOT NULL,
value DECIMAL(11,2) NOT NULL,
when_i_wish DATE
);

CREATE TABLE money (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL
);

CREATE TABLE charges (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL,
value DECIMAL(11,2) NULL
);

CREATE TABLE incomes (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
money_id INT(11),
taxes_id BIGINT,
income_source_id BIGINT,
payday_type_id INT(11),
payday DATE,
description VARCHAR(255) NOT NULL,
value DECIMAL(11,2) NOT NULL
);

CREATE TABLE taxes (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL,
value DECIMAL(11,2) NOT NULL
);

CREATE TABLE taxes_incomes (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
taxes_id INT(11),
incomes_id BIGINT
);

CREATE TABLE taxes_spent (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
taxes_id INT(11),
spent_id BIGINT
);

CREATE TABLE income_source (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
description VARCHAR(255) NOT NULL
);

CREATE TABLE payday_type (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL,
rule VARCHAR(255) NOT NULL,
monthly TINYINT(2),
annually TINYINT(1),
weekly INT(11) NOT NULL
);

CREATE TABLE institutions (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT,
description VARCHAR(255) NOT NULL,
phone VARCHAR(50) NOT NULL,
public TINYINT(1)
);

CREATE TABLE subjects (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);

CREATE TABLE weekday (
id INT(11) NOT NULL PRIMARY KEY,
description VARCHAR(50) NOT NULL
);

ALTER TABLE forms_of_payment ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE forms_of_payment ADD FOREIGN KEY(institution_id) REFERENCES institutions (id);
ALTER TABLE profiles ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE settings ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE spent ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE spent ADD FOREIGN KEY(expense_id) REFERENCES expense_types (id);
ALTER TABLE spent ADD FOREIGN KEY(bank_options_id) REFERENCES bank_options (id);
ALTER TABLE spent ADD FOREIGN KEY(money_id) REFERENCES money (id);
ALTER TABLE spent ADD FOREIGN KEY(charge_id) REFERENCES charges (id);
ALTER TABLE spent ADD FOREIGN KEY(taxes_id) REFERENCES taxes_spent (id);
ALTER TABLE spent ADD FOREIGN KEY(institution_id) REFERENCES institutions (id);
ALTER TABLE spent ADD FOREIGN KEY(weekly) REFERENCES weekday (id);
ALTER TABLE contacts ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE contacts ADD FOREIGN KEY(subject) REFERENCES subjects (id);
ALTER TABLE wishlist ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE wishlist ADD FOREIGN KEY(money_id) REFERENCES money (id);
ALTER TABLE incomes ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE incomes ADD FOREIGN KEY(money_id) REFERENCES money (id);
ALTER TABLE incomes ADD FOREIGN KEY(taxes_id) REFERENCES taxes_incomes (id);
ALTER TABLE incomes ADD FOREIGN KEY(income_source_id) REFERENCES income_source (id);
ALTER TABLE incomes ADD FOREIGN KEY(payday_type_id) REFERENCES payday_type (id);
ALTER TABLE taxes_incomes ADD FOREIGN KEY(taxes_id) REFERENCES taxes (id);
ALTER TABLE taxes_incomes ADD FOREIGN KEY(incomes_id) REFERENCES incomes (id);
ALTER TABLE taxes_spent ADD FOREIGN KEY(taxes_id) REFERENCES taxes (id);
ALTER TABLE taxes_spent ADD FOREIGN KEY(spent_id) REFERENCES spent (id);
ALTER TABLE income_source ADD FOREIGN KEY(user_id) REFERENCES users (id);
ALTER TABLE payday_type ADD FOREIGN KEY(weekly) REFERENCES weekday (id);
ALTER TABLE institutions ADD FOREIGN KEY(user_id) REFERENCES users (id);