from sqlalchemy import Column, MetaData, String, Table

from openlog.datamodel.connection.sqlalchemy.sqlachemy_categories_interface import (
    SqlAlchemyCategoriesInterface,
)


class XplordbCategoriesInterface(SqlAlchemyCategoriesInterface):
    def import_categories_table(self, categories):
        """
        Add privileges to all roles.
        """
        for categorie in categories:
            # Create new table
            meta = MetaData()

            table = Table(
                categorie.table_name,
                meta,
                Column(categorie.name_column, String(), primary_key=True),
                Column("description", String()),
                schema=categorie.schema,
            )
            table.create(self.engine)
            self._added_categories_table.append(table)

            # Add to available categories
            ins = self.categories_table.insert().values(
                name=categorie.name,
                table_name=categorie.table_name,
                name_column=categorie.name_column,
                schema=categorie.schema,
            )
            self.session.execute(ins)
            self.session.commit()
            # give sufficient privilege to non-owner users
            q = f'GRANT SELECT ON TABLE public."{categorie.table_name}" TO xdb_viewer;'
            self.session.execute(q)
            q = f'GRANT ALL ON TABLE public."{categorie.table_name}" TO xdb_admin;'
            self.session.execute(q)
            q = f'GRANT ALL ON TABLE public."{categorie.table_name}" TO xdb_logger;'
            self.session.execute(q)
            self.session.commit()
