-- issue #1171

----------------------------------------------------------------------------------
--- Script GW 4.6

--- REPLACE xxx_metadata with the metadata schema name
--- REPLACE xxx_data with the data schema name
--- GSC, 2023.12
----------------------------------------------------------------------------------


----------------------------------------------------------------------------------
-- I M P O R T A N T
-- gwa_function relation should already exist
----------------------------------------------------------------------------------



-- Table: xxx_data.gwr_report

-- DROP TABLE xxx_data.gwr_report;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_report
(
    id_report integer NOT NULL,
    cod_report character varying(50) COLLATE pg_catalog."default" NOT NULL,
    name_report character varying(200) COLLATE pg_catalog."default" NOT NULL,
    descr_report character varying(500) COLLATE pg_catalog."default",
    author_report character varying(200) COLLATE pg_catalog."default" NOT NULL,
    date_report timestamp without time zone NOT NULL,
    pdf_output integer,
    xlsx_output integer,
    docx_output integer,
    is_active integer,
    type_generation character varying(50) COLLATE pg_catalog."default",
    main_report_name character varying(200) COLLATE pg_catalog."default" NOT NULL,
    report_resources bytea,
    is_custom integer,
    cod_report_category character varying(50) COLLATE pg_catalog."default",
    report_resources_name character varying(100) COLLATE pg_catalog."default",
    report_resources_ctype character varying(100) COLLATE pg_catalog."default",
    has_parameters_to_prompt integer DEFAULT 0,
    is_filterable integer DEFAULT 0,
    filterable_gw_class_names character varying COLLATE pg_catalog."default",
    is_filterable_from_detail integer DEFAULT 0,
    is_in_toolbar integer,
    is_in_toolbar_detail integer,
    CONSTRAINT pk_gwr_report PRIMARY KEY (id_report),
    CONSTRAINT cod_report_unique UNIQUE (cod_report)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_report
    OWNER to test_gw46data;

COMMENT ON COLUMN xxx_data.gwr_report.filterable_gw_class_names
    IS 'List of comma separated gwClassName
Ex:
gwclassname1,gwclassname2

As result the report would to be executable from the gwClassList with the current applied filter';

-------------------------------------------------------------
-- Table: xxx_data.gwr_report_category

-- DROP TABLE xxx_data.gwr_report_category;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_report_category
(
    id_report_category integer NOT NULL,
    cod_report_category character varying(50) COLLATE pg_catalog."default",
    name_report_category character varying(200) COLLATE pg_catalog."default",
    descr_report_category character varying(500) COLLATE pg_catalog."default",
    ord_report_category integer,
    CONSTRAINT pk_gwr_report_category PRIMARY KEY (id_report_category),
    CONSTRAINT cod_report_category_unique UNIQUE (cod_report_category)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_report_category
    OWNER to test_gw46data;

-------------------------------------------------------------
-- Table: xxx_data.gwr_model

-- DROP TABLE xxx_data.gwr_model;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_model
(
    id_model integer NOT NULL,
    cod_model character varying(50) COLLATE pg_catalog."default" NOT NULL,
    name_model character varying(200) COLLATE pg_catalog."default" NOT NULL,
    descr_model character varying(500) COLLATE pg_catalog."default",
    author_model character varying(200) COLLATE pg_catalog."default" NOT NULL,
    date_model timestamp without time zone NOT NULL,
    type_header_model character varying(100) COLLATE pg_catalog."default" NOT NULL,
    header_model_string character varying COLLATE pg_catalog."default",
    header_model_image bytea,
    type_footer_model character varying(100) COLLATE pg_catalog."default" NOT NULL,
    footer_model_string character varying COLLATE pg_catalog."default",
    footer_model_image bytea,
    is_custom integer,
    header_model_image_name character varying COLLATE pg_catalog."default",
    header_model_image_ctype character varying COLLATE pg_catalog."default",
    footer_model_image_name character varying COLLATE pg_catalog."default",
    footer_model_image_ctype character varying COLLATE pg_catalog."default",
    header_h_alignment character varying COLLATE pg_catalog."default",
    footer_h_alignment character varying COLLATE pg_catalog."default",
    header_model_html character varying COLLATE pg_catalog."default",
    footer_model_html character varying COLLATE pg_catalog."default",
    CONSTRAINT pk_gwr_model PRIMARY KEY (id_model),
    CONSTRAINT cod_model_unique UNIQUE (cod_model)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_model
    OWNER to test_gw46data;

COMMENT ON COLUMN xxx_data.gwr_model.type_header_model
    IS 'allowed values: [''image'', ''string'', ''html'']';

COMMENT ON COLUMN xxx_data.gwr_model.type_footer_model
    IS 'allowed values: [''image'', ''string'', ''html'']';

COMMENT ON COLUMN xxx_data.gwr_model.header_h_alignment
    IS 'horizontal alignment. possible values ["left", "center", "right"], default "left"';

COMMENT ON COLUMN xxx_data.gwr_model.footer_h_alignment
    IS 'horizontal alignment. possible values ["left", "center", "right"], default "left"';

COMMENT ON COLUMN xxx_data.gwr_model.header_model_html
    IS 'it contains complex html, including svg elements';

COMMENT ON COLUMN xxx_data.gwr_model.footer_model_html
    IS 'it contains complex html, including svg elements';

-------------------------------------------------------------
-- Table: xxx_data.gwr_param

-- DROP TABLE xxx_data.gwr_param;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_param
(
    id_report_param integer NOT NULL,
    cod_report character varying(50) COLLATE pg_catalog."default" NOT NULL,
    cod_report_param character varying(50) COLLATE pg_catalog."default" NOT NULL,
    name_report_param character varying(200) COLLATE pg_catalog."default" NOT NULL,
    type_report_param character varying(100) COLLATE pg_catalog."default" NOT NULL,
    string_value character varying(4000) COLLATE pg_catalog."default",
    image_value bytea,
    image_value_name character varying COLLATE pg_catalog."default",
    image_value_ctype character varying COLLATE pg_catalog."default",
    CONSTRAINT pk_gwr_param PRIMARY KEY (id_report_param),
    CONSTRAINT fk_gwr_report FOREIGN KEY (cod_report)
        REFERENCES xxx_data.gwr_report (cod_report) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_param
    OWNER to test_gw46data;

-------------------------------------------------------------
-- Table: xxx_data.gwr_r_report_model

-- DROP TABLE xxx_data.gwr_r_report_model;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_r_report_model
(
    id_r_report_model integer NOT NULL,
    cod_report character varying COLLATE pg_catalog."default" NOT NULL,
    cod_model character varying(50) COLLATE pg_catalog."default" NOT NULL,
    is_default integer NOT NULL,
    CONSTRAINT id_r_report_model_pk PRIMARY KEY (id_r_report_model),
    CONSTRAINT fk_gwr_model FOREIGN KEY (cod_model)
        REFERENCES xxx_data.gwr_model (cod_model) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT fk_gwr_report FOREIGN KEY (cod_report)
        REFERENCES xxx_data.gwr_report (cod_report) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_r_report_model
    OWNER to test_gw46data;

-------------------------------------------------------------
-- Table: xxx_data.gwr_r_report_function

-- DROP TABLE xxx_data.gwr_r_report_function;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_r_report_function
(
    id_r_report_function integer NOT NULL,
    cod_report character varying(50) COLLATE pg_catalog."default" NOT NULL,
    cod_function character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT pk_gwr_report_r_gwa_func PRIMARY KEY (id_r_report_function),
    CONSTRAINT fk_gwr_function FOREIGN KEY (cod_function)
        REFERENCES xxx_data.gwa_func (cod_func) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT fk_gwr_report FOREIGN KEY (cod_report)
        REFERENCES xxx_data.gwr_report (cod_report) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_r_report_function
    OWNER to test_gw46data;

-------------------------------------------------------------
-- Table: xxx_data.gwr_r_rep_cat_func

-- DROP TABLE xxx_data.gwr_r_rep_cat_func;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_r_rep_cat_func
(
    id_r_rep_cat_func integer NOT NULL,
    cod_report character varying COLLATE pg_catalog."default" NOT NULL,
    cod_report_category character varying COLLATE pg_catalog."default" NOT NULL,
    ord_report integer,
    cod_func character varying COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT gwr_r_report_rep_cat_pkey PRIMARY KEY (id_r_rep_cat_func),
    CONSTRAINT "unique" UNIQUE (cod_report, cod_report_category, cod_func),
    CONSTRAINT fk_gwa_func FOREIGN KEY (cod_func)
        REFERENCES xxx_data.gwa_func (cod_func) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT fk_gwr_report FOREIGN KEY (cod_report)
        REFERENCES xxx_data.gwr_report (cod_report) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID,
    CONSTRAINT fk_gwr_report_category FOREIGN KEY (cod_report_category)
        REFERENCES xxx_data.gwr_report_category (cod_report_category) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
        NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_r_rep_cat_func
    OWNER to test_gw46data;

-------------------------------------------------------------
-- Table: xxx_data.gwr_filterable_classes

-- DROP TABLE xxx_data.gwr_filterable_classes;

CREATE TABLE IF NOT EXISTS xxx_data.gwr_filterable_classes
(
    id_gwr_filterable_classes integer NOT NULL,
    gw_class_name character varying COLLATE pg_catalog."default" NOT NULL,
    gw_class_label character varying COLLATE pg_catalog."default",
    CONSTRAINT gwr_filterable_classes_pkey PRIMARY KEY (id_gwr_filterable_classes)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE xxx_data.gwr_filterable_classes
    OWNER to test_gw46data;

COMMENT ON TABLE xxx_data.gwr_filterable_classes
    IS 'tabella usata per esplicitare su quali classi le report esportabili possono essere collegate (e quindi comparire nei rispettivi menu), al fine di usare la gwClassList od il gwClassDetail per lanciare le report esportabili con specifici filtri';
-------------------------------------------------------------