-- use pis;
drop table compres if exists;
drop table excepcions if exists;
drop table gent if exists;
drop table compradors if exists;
drop table compradors_excepcions if exists;

CREATE TABLE compres (
	data	TIMESTAMP NOT NULL,
	botiga	CHAR(30) NOT NULL,
	motiu	TEXT,
	preu	DECIMAL(18,10) UNSIGNED NOT NULL,
	comentari	TEXT,

	PRIMARY KEY (data)
	) TYPE = InnoDB;

CREATE TABLE excepcions (
	id	MEDIUMINT NOT NULL AUTO_INCREMENT,
	data	TIMESTAMP NOT NULL,
	article	TINYTEXT,
	preu	DECIMAL(18,10) UNSIGNED NOT NULL,

	KEY data (data),
	PRIMARY KEY (id),
	FOREIGN KEY (data) REFERENCES compres (data)
	) TYPE = InnoDB;

CREATE TABLE gent (
	id	MEDIUMINT NOT NULL AUTO_INCREMENT,
	nom	VARCHAR(50) NOT NULL,
	habitant	BOOL,

	PRIMARY KEY (id)
	) TYPE = InnoDB;

CREATE TABLE compradors (
	id	MEDIUMINT NOT NULL AUTO_INCREMENT,
	data	TIMESTAMP NOT NULL, 
	persona	MEDIUMINT NOT NULL, 
	pagat	 DECIMAL(18,10) UNSIGNED NOT NULL,
	toca_pagar	DECIMAL(18,10) UNSIGNED NOT NULL,

	KEY data (data),
	KEY persona (persona),
	FOREIGN KEY (data) REFERENCES compres(data),
	FOREIGN KEY (persona) REFERENCES gent(id),
	PRIMARY KEY (id)
	) TYPE = InnoDB;

CREATE TABLE compradors_excepcions (
	id	MEDIUMINT NOT NULL AUTO_INCREMENT,
	ex_id	MEDIUMINT NOT NULL,
	persona	MEDIUMINT NOT NULL,
	toca_pagar	DECIMAL(18,10) UNSIGNED NOT NULL,

	KEY ex_id (ex_id),
	KEY persona (persona),
	FOREIGN KEY (ex_id) REFERENCES excepcions (id),
	FOREIGN KEY (persona) REFERENCES gent (id),
	PRIMARY KEY (id)
	) TYPE = InnoDB;
	
CREATE TABLE pagaments (
	data	TIMESTAMP NOT NULL,
	comentari	TEXT,

	PRIMARY KEY (data)
	) TYPE = InnoDB;

CREATE TABLE pagadors (
	id	MEDIUMINT NOT NULL AUTO_INCREMENT,
	data	TIMESTAMP NOT NULL, 
	persona	MEDIUMINT NOT NULL, 
	pagat	 DECIMAL(18,10) UNSIGNED NOT NULL,
	cobrat	 DECIMAL(18,10) UNSIGNED NOT NULL,

	KEY data (data),
	KEY persona (persona),
	FOREIGN KEY (data) REFERENCES pagaments(data),
	FOREIGN KEY (persona) REFERENCES gent(id),
	PRIMARY KEY (id)
	) TYPE = InnoDB;

-- INSERT INTO gent (nom,habitant) VALUES('Lluis', 1);
-- INSERT INTO gent (nom,habitant) VALUES('Marc', 1);
-- INSERT INTO gent (nom,habitant) VALUES('Natxo', 1);
-- INSERT INTO gent (nom,habitant) VALUES('Borja', 1);
-- INSERT INTO gent (nom,habitant) VALUES('Xumi', 0);
-- INSERT INTO gent (nom,habitant) VALUES('Boira', 0);

-- SET @date := NOW();
-- INSERT INTO compres (data,botiga,motiu,preu) VALUES(@date,'Esclat',
-- 	'Compra setmanal',2203);
-- INSERT INTO excepcions (data,article,preu) VALUES(@date,'Bicicleta', 200);
-- INSERT INTO excepcions (data,article,preu) VALUES(@date,'Patates', 3);

-- INSERT INTO compradors (data,persona,pagat,toca_pagar)
-- 	VALUES(@date,1,1203,500);
-- INSERT INTO compradors (data,persona,pagat,toca_pagar)
-- 	VALUES(@date,2,0,500);
-- INSERT INTO compradors (data,persona,pagat,toca_pagar)
-- 	VALUES(@date,3,500,500);
-- INSERT INTO compradors (data,persona,pagat,toca_pagar)
-- 	VALUES(@date,4,500,500);

-- INSERT INTO compradors_excepcions (ex_id, persona, toca_pagar)
	-- VALUES(1,1,200);
-- INSERT INTO compradors_excepcions (ex_id, persona, toca_pagar)
	-- VALUES(2,5,3);