-- 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;