#!/usr/bin/env python3
# -*- coding: UTF-8 -*-
from pycen import con_fon

d_typinfosite = '''
    CREATE TABLE IF NOT EXISTS sites.d_typinfosite (
        typinfosite_id serial NOT NULL,
        typinfosite_lib varchar(50) NOT NULL COLLATE pg_catalog."default",
    CONSTRAINT d_typinfosite_pkey PRIMARY KEY (typinfosite_id)
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.d_typinfosite OWNER to grp_sig;
GRANT SELECT ON TABLE sites.d_typinfosite TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.d_typinfosite TO grp_foncier;
GRANT ALL ON TABLE sites.d_typinfosite TO grp_sig;
'''
ins_typinfosite = '''
    INSERT INTO sites.d_typinfosite (typinfosite_lib) VALUES 
    ('Essentiel'),
    ('Historique'),
    ('Patrimoine'),
    ('Objectifs'),
    ('Enjeux')
    ;
'''
r_sites_infos = '''
    DROP TABLE IF EXISTS sites.r_sites_infos;
    CREATE TABLE IF NOT EXISTS sites.r_sites_infos (
        infosite_id serial NOT NULL,
        site_id varchar(10) NOT NULL,
        typinfosite_id integer NOT NULL,
        site_info text,
        maj_user text,
		maj_date varchar(20),
    CONSTRAINT r_sites_infos_pkey PRIMARY KEY (infosite_id),
	CONSTRAINT r_sites_infos_site_id_fkey FOREIGN KEY (site_id)
		REFERENCES sites.sites (site_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
	CONSTRAINT r_sites_infos_typinfosite_id_fkey FOREIGN KEY (typinfosite_id)
		REFERENCES sites.d_typinfosite (typinfosite_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.r_sites_infos OWNER to grp_sig;
GRANT SELECT ON TABLE sites.r_sites_infos TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.r_sites_infos TO grp_foncier;
GRANT ALL ON TABLE sites.r_sites_infos TO grp_sig;

CREATE INDEX IF NOT EXISTS fki_r_sites_infos_infosite_id_fkey
    ON sites.r_sites_infos USING btree
    (infosite_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS fki_r_sites_infos_site_id_fkey
    ON sites.r_sites_infos USING btree
    (site_id ASC NULLS LAST)
    TABLESPACE pg_default;
CREATE INDEX IF NOT EXISTS fki_r_sites_infos_typinfosite_id_fkey
    ON sites.r_sites_infos USING btree
    (typinfosite_id ASC NULLS LAST)
    TABLESPACE pg_default;
'''
with con_fon.begin() as cnx:
    cnx.execute(d_typinfosite)
    cnx.execute(ins_typinfosite)
    cnx.execute(r_sites_infos)  
    

secteurs = '''
    CREATE TABLE IF NOT EXISTS sites.secteurs (
        id_secteur serial NOT NULL,
        site_id varchar(10) NOT NULL,
        secteur_nom text NOT NULL COLLATE pg_catalog."default",
    CONSTRAINT secteurs_pkey PRIMARY KEY (id_secteur),
	CONSTRAINT secteurs_site_id_fkey FOREIGN KEY (site_id)
		REFERENCES sites.sites (site_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.secteurs OWNER to grp_sig;
GRANT SELECT ON TABLE sites.secteurs TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.secteurs TO grp_foncier;
GRANT ALL ON TABLE sites.secteurs TO grp_sig;

CREATE INDEX IF NOT EXISTS fki_secteurs_site_id_fkey
    ON sites.secteurs USING btree
    (site_id ASC NULLS LAST)
    TABLESPACE pg_default;
'''

d_typpartenaire = '''
    DROP TABLE IF EXISTS sites.d_typpartenaire;
    CREATE TABLE IF NOT EXISTS sites.d_typpartenaire (
        typpartenaire_id serial NOT NULL,
        typpartenaire_lib text NOT NULL,
    CONSTRAINT d_typpartenaire_pkey PRIMARY KEY (typpartenaire_id)
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.d_typpartenaire OWNER to grp_sig;
GRANT SELECT ON TABLE sites.d_typpartenaire TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.d_typpartenaire TO grp_foncier;
GRANT ALL ON TABLE sites.d_typpartenaire TO grp_sig;
'''
ins_typpartenaire = '''
    INSERT INTO sites.d_typpartenaire (typpartenaire_lib) VALUES 
    ('Financeurs'),
    ('Partenaires')
    ;
'''

r_sites_partenaires = '''
    CREATE TABLE IF NOT EXISTS sites.r_sites_partenaires (
        partenaire_id serial NOT NULL,
        site_id varchar(10) NOT NULL,
        typpartenaire_id integer NOT NULL,
        maj_user text,
        maj_date varchar(20),
    CONSTRAINT r_sites_partenaires_pkey PRIMARY KEY (partenaire_id,site_id,typpartenaire_id),
	CONSTRAINT r_sites_partenaires_site_id_fkey FOREIGN KEY (site_id)
		REFERENCES sites.sites (site_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
	CONSTRAINT r_sites_partenaires_typpartenaire_id_fkey FOREIGN KEY (typpartenaire_id)
		REFERENCES sites.d_typpartenaire (typpartenaire_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.r_sites_partenaires OWNER to grp_sig;
GRANT SELECT ON TABLE sites.r_sites_partenaires TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.r_sites_partenaires TO grp_foncier;
GRANT ALL ON TABLE sites.r_sites_partenaires TO grp_sig;

CREATE INDEX IF NOT EXISTS fki_r_sites_partenaires_site_id_fkey
    ON sites.r_sites_partenaires USING btree
    (site_id ASC NULLS LAST)
    TABLESPACE pg_default;
'''


d_typactnotsite = '''
    CREATE TABLE IF NOT EXISTS sites.d_typactnotsite (
        typactnotsite_id varchar(6) NOT NULL,
        typactnotsite_lib varchar(10) NOT NULL,
    CONSTRAINT d_typactnotsite_pkey PRIMARY KEY (typactnotsite_id)
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.d_typactnotsite OWNER to grp_sig;
GRANT SELECT ON TABLE sites.d_typactnotsite TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.d_typactnotsite TO grp_foncier;
GRANT ALL ON TABLE sites.d_typactnotsite TO grp_sig;
'''

actions_sites = '''
    CREATE TABLE IF NOT EXISTS sites.actions_sites (
        action_site_id serial NOT NULL,
        action_site_date varchar(20) NOT NULL,
        action_site_datemef varchar(20) NOT NULL,
		typactnotsite_id varchar(6) NOT NULL,
        action_site_lib text NOT NULL,
    CONSTRAINT actions_sites_pkey PRIMARY KEY (action_site_id),
	CONSTRAINT r_sites_partenaires_typactnotsite_id_fkey FOREIGN KEY (typactnotsite_id)
		REFERENCES sites.d_typactnotsite (typactnotsite_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.actions_sites OWNER to grp_sig;
GRANT SELECT ON TABLE sites.actions_sites TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.actions_sites TO grp_foncier;
GRANT ALL ON TABLE sites.actions_sites TO grp_sig;
'''

r_sites_actions = '''
    CREATE TABLE IF NOT EXISTS sites.r_sites_actions (
        action_site_id serial NOT NULL,
        site_id varchar(10) NOT NULL,
        maj_user text,
		maj_date varchar(20),
        actif varchar(1) NOT NULL,
    CONSTRAINT r_sites_actions_pkey PRIMARY KEY (action_site_id,site_id),
	CONSTRAINT r_sites_actions_site_id_fkey FOREIGN KEY (site_id)
		REFERENCES sites.sites (site_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
    CONSTRAINT r_sites_actions_action_site_id_fkey FOREIGN KEY (action_site_id)
        REFERENCES sites.actions_sites (action_site_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.r_sites_actions OWNER to grp_sig;
GRANT SELECT ON TABLE sites.r_sites_actions TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.r_sites_actions TO grp_foncier;
GRANT ALL ON TABLE sites.r_sites_actions TO grp_sig;
'''

notes_sites = '''
    CREATE TABLE IF NOT EXISTS sites.notes_sites (
        note_site_id serial NOT NULL,
        site_id varchar(10) NOT NULL,
        note_site_date varchar(20) NOT NULL,
        observateur_id integer NOT NULL,
        typactnotsite_id integer NOT NULL,
        note_site_lib text NOT NULL,
        maj_user text,
		maj_date varchar(20),
    CONSTRAINT notes_sites_pkey PRIMARY KEY (note_site_id),
	CONSTRAINT notes_sites_site_id_fkey FOREIGN KEY (site_id)
		REFERENCES sites.sites (site_id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION
    )
    TABLESPACE pg_default;

ALTER TABLE IF EXISTS sites.notes_sites OWNER to grp_sig;
GRANT SELECT ON TABLE sites.notes_sites TO cen_user;
GRANT DELETE, INSERT, UPDATE ON TABLE sites.notes_sites TO grp_foncier;
GRANT ALL ON TABLE sites.notes_sites TO grp_sig;
'''


with con_fon.begin() as cnx:
    cnx.execute(d_typactnotsite)
    cnx.execute(actions_sites)
    cnx.execute(r_sites_actions)