-- use prova_pis;
drop table if exists moviments ;
drop table if exists ingressos ;
drop table if exists butxaques ;

CREATE TABLE butxaques (
	id	MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
	nom	VARCHAR(50) NOT NULL,
	principal	BOOL,

	PRIMARY KEY (id)
	) TYPE = InnoDB;

-- ingressos i despeses
CREATE TABLE ingressos (
	data	TIMESTAMP NOT NULL,
	data_modif	TIMESTAMP NOT NULL,
	rao	TINYTEXT NULL,
	quantitat	DECIMAL(18,10) SIGNED NOT NULL,
	butxaca		MEDIUMINT UNSIGNED NOT NULL,
	comentari	TEXT,

	PRIMARY KEY (data),
	INDEX (butxaca),
	FOREIGN KEY (butxaca) REFERENCES butxaques (id)
	) TYPE = InnoDB;

-- moviments de diners entre butxaques
CREATE TABLE moviments (
	data	TIMESTAMP NOT NULL,
	data_modif	TIMESTAMP NOT NULL,
	rao	TINYTEXT NOT NULL,
	quantitat	DECIMAL(18,10) SIGNED NOT NULL,
	comentari	TEXT,
	butxaca_origen		MEDIUMINT UNSIGNED NOT NULL,
	butxaca_desti		MEDIUMINT UNSIGNED NOT NULL,

	PRIMARY KEY (data),
	INDEX (butxaca_origen),
	INDEX (butxaca_desti),
	FOREIGN KEY (butxaca_origen) REFERENCES butxaques (id),
	FOREIGN KEY (butxaca_desti) REFERENCES butxaques (id)
	) TYPE = InnoDB;