#!/usr/bin/env python3
# -*- coding: UTF-8 -*-.

from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL
from datetime import datetime as dt
import pandas as pd
import geopandas as gpd


# Parametres bdd
user = 'cen_admin'
pwd  = '#CEN38@venir'
adr  = '91.134.194.221'
port = '5432'
base = 'azalee'

url = URL.create('postgresql+psycopg2', 
  username=user,
  password=pwd,
  host=adr,
  database=base,
)
con = create_engine(url)

v_rpg2021_ilots_anonymes_isere = """
DROP VIEW IF EXISTS ref_territoire.v_rpg2021_ilots_anonymes_isere;
CREATE OR REPLACE VIEW ref_territoire.v_rpg2021_ilots_anonymes_isere
AS SELECT --s.id,
    s.*
   FROM ref_territoire.rpg2021_ilots_anonymes_reg s,
    ref_territoire.dept_isere reg
  WHERE st_intersects(s.geom, reg.geom);
"""
grant_rpg2021_ilots = """
ALTER TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere OWNER TO cen_admin;
GRANT ALL ON TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere TO cen_admin;
GRANT ALL ON TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere TO grp_admin;
GRANT SELECT ON TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere TO grp_consult;
"""
with con.begin() as cnx:
    cnx.execute(v_rpg2021_ilots_anonymes_isere)
    cnx.execute(grant_rpg2021_ilots)


v_rpg2021_parcelles_graphiques_isere = (v_rpg2021_ilots_anonymes_isere
  .replace('v_rpg2021_ilots_anonymes_isere','v_rpg2021_parcelles_graphiques_isere')
  .replace('rpg2021_ilots_anonymes_reg','rpg2021_parcelles_graphiques_reg'))
grant = grant_rpg2021_ilots.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2021_parcelles_graphiques_isere')
with con.begin() as cnx:
    cnx.execute(v_rpg2021_parcelles_graphiques_isere)
    cnx.execute(grant)


v_rpg2020_ilots_anonymes_isere = v_rpg2021_ilots_anonymes_isere.replace('2021','2020')
grant = grant_rpg2021_ilots.replace('2021','2020')
with con.begin() as cnx:
    cnx.execute(v_rpg2020_ilots_anonymes_isere)
    cnx.execute(grant)


v_rpg2020_parcelles_graphiques_isere = v_rpg2021_ilots_anonymes_isere.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2020_parcelles_graphiques_isere')
grant = grant_rpg2021_ilots.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2020_parcelles_graphiques_isere')
with con.begin() as cnx:
    cnx.execute(v_rpg2020_parcelles_graphiques_isere)
    cnx.execute(grant)