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

from sqlalchemy.engine import URL
from sqlalchemy import create_engine,text
import pandas as pd

# Parametres bdd
# user = 'cen_admin'
# pwd  = '#CEN38@venir'
# user = 'admin_ra'
# pwd  = 'adminRAsig'

# adr  = '91.134.194.221'
# port = '5432'
# base = 'bd_cen38'

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

usr = {
    'usr_siege': ['abavarot','aguedou','apagano','cgeier','dlopez-pinot','dmichallet','jlgrossi','mjuton','msimean','nbiron'],
    'usr_glps':  ['lquay','gmaillet','jlucas'],
    'usr_plt' :  ['bpont','yprat-mairet','clebreton','cfregat','mbounous']
}


def revoke_group(con,user,group):
    alter = 'ALTER GROUP "{grp}" DROP USER "{usr}";'
    with con.begin() as cnx:
        cnx.execute(alter.format(usr=user,grp=group))


def add_group(con,user,group):
    alter = 'ALTER GROUP "{grp}" ADD USER "{usr}";'
    with con.begin() as cnx:
        cnx.execute(alter.format(usr=user,grp=group))


def deactivate_user(con,user):
    alter = 'ALTER USER "{usr}" WITH NOLOGIN;'
    with con.begin() as cnx:
        cnx.execute(alter.format(usr=user))


def add_limit_user(con,user,date_limit):
    alter = """ALTER USER "{usr}" VALID UNTIL '{date} 00:00:00';"""
    with con.begin() as cnx:
        cnx.execute(alter.format(usr=user,date=date_limit))


def change_password(con,user,pwd):
    alter = """ALTER USER "{usr}" WITH PASSWORD '{password}';"""
    with con.begin() as cnx:
        cnx.execute(alter.format(usr=user,password=pwd))


def create_grp(con,grp):
    sql = """CREATE ROLE {grp} WITH 
        NOSUPERUSER
        NOCREATEDB
        NOCREATEROLE
        INHERIT
        NOLOGIN
        NOREPLICATION
        NOBYPASSRLS
        CONNECTION LIMIT -1;""".format(grp=grp)
    with con.begin() as cnx:
        cnx.execute(sql)

def create_usr(con,usr,pwd):
    sql = """CREATE USER "{usr}" WITH 
        NOSUPERUSER
        NOCREATEDB
        NOCREATEROLE
        INHERIT
        LOGIN
        NOREPLICATION
        NOBYPASSRLS
        PASSWORD '{pwd}'
        CONNECTION LIMIT -1;""".format(usr=usr,pwd=pwd)
    with con.begin() as cnx:
        cnx.execute(sql)
# sql = "select * from pg_catalog.pg_user" # where tableowner = 'gpasquier'"


if __name__ == "__main__":
    from pycen import con_bdcen,con_fon,con

    for grp in usr.keys():
        # create grp_role
        create_grp(con_fon,grp)

        # create usr
        for user in usr[grp]:
            sql = """
            DO
            $do$
            BEGIN
                IF NOT EXISTS (
                    SELECT FROM pg_catalog.pg_roles 
                    WHERE  rolname = '{usr}') THEN

                    CREATE ROLE "{usr}" LOGIN PASSWORD '{usr}';
                END IF;
                GRANT {grp} TO "{usr}" ;
            END $do$""".format(usr=user,grp=grp)
            with con.begin() as cnx:
                cnx.execute(text(sql))


    # grant grp_role
    for grp in usr.keys():
        sql = """GRANT grp_consult TO {usr} ;""".format(usr=grp)
        with con.begin() as cnx:
            cnx.execute(sql)