CREATE SCHEMA reporting;

/******************** Add Table:reporting.report ************************/
CREATE TABLE reporting.report
(
	id				SERIAL PRIMARY KEY,
	name			VARCHAR(100) DEFAULT '',
	type			TEXT,
	description		TEXT,
	query			TEXT,
	jrxml			BYTEA DEFAULT NULL,
	jasper		 	BYTEA DEFAULT NULL 
);

CREATE TABLE reporting.parameter_query
(
	id			SERIAL PRIMARY KEY,
	name		VARCHAR(100) NOT NULL,
	comment		TEXT DEFAULT NULL,
	query		TEXT NOT NULL
);

CREATE TABLE reporting.report_parameter
(
	id 				SERIAL PRIMARY KEY,
	report_id		INTEGER,
	name			VARCHAR(100) NOT NULL,
	label			VARCHAR(100) NOT NULL,
	comment			TEXT DEFAULT NULL,
	type			TEXT NOT NULL,
	default_value	TEXT DEFAULT NULL,
	query_id		INTEGER DEFAULT NULL,
	mendatory		BOOLEAN DEFAULT TRUE,
	multi_select	BOOLEAN DEFAULT FALSE
);

ALTER TABLE reporting.report_parameter ADD CONSTRAINT fk_report_parameter_report_id
	FOREIGN KEY (report_id) REFERENCES reporting.report (id) ON DELETE CASCADE;

ALTER TABLE reporting.report_parameter ADD CONSTRAINT fk_report_parameter_query_id
	FOREIGN KEY (query_id) REFERENCES reporting.parameter_query (id) ON DELETE CASCADE;

CREATE INDEX idx_reporting_report_parameter_report_id 
ON reporting.report_parameter USING btree (report_id);
