db.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ADMMIN MODEL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy.dialects.mysql import base \n",
    "base.ischema_names['tinyint'] = base.BOOLEAN\n",
    "base.ischema_names['mediumtext'] = base.TEXT \n",
    "# from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy import (\n",
    "    create_engine,\n",
    "    Column,\n",
    "    Integer,\n",
    "    String,\n",
    "    Text,\n",
    "    Time,\n",
    "    DateTime,\n",
    "    Date,\n",
    "    Float,\n",
    "    Boolean,\n",
    "    ForeignKey,\n",
    "    UniqueConstraint\n",
    ")\n",
    "from sqlalchemy.orm import sessionmaker, declarative_base\n",
    "from sqlalchemy import inspect\n",
    "from sqlalchemy import MetaData\n",
    "from sqlalchemy import Table as _Table\n",
    "from sqlalchemy.sql import select, and_\n",
    "from sqlalchemy.sql import insert\n",
    "from sqlalchemy.sql import update\n",
    "from sqlalchemy.sql import delete\n",
    "from sqlalchemy.sql import text\n",
    "#from sqlalchemy.sql.expression import func\n",
    "\n",
    "from sqlalchemy import func\n",
    "import datetime\n",
    "import json\n",
    "import hashlib\n",
    "import os\n",
    "from sqlalchemy.types import TypeDecorator, Unicode\n",
    "import sys\n",
    "from sqlalchemy.engine.url import URL\n",
    "import copy\n",
    "#from sqlalchemy_utils import database_exists, create_database\n",
    "import re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlalchemy as sa\n",
    "import openpyxl as xl\n",
    "from passlib.hash import pbkdf2_sha256\n",
    "from passlib.context import CryptContext\n",
    "pwd_context = CryptContext(schemes=[\"bcrypt\"], deprecated=\"auto\")\n",
    "from sqlalchemy_utils import database_exists, create_database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "link = 'tmp/EXEMPLE_20231112.parquet'"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "database = 'ADMIN_DB'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_conf = {\n",
    "    \"drivername\": \"mysql+pymysql\",\n",
    "    \"username\": \"root\",\n",
    "    \"password\": \"1234\",\n",
    "    \"host\": \"localhost\",\n",
    "    \"port\": 3306,\n",
    "    \"database\": database\n",
    "}\n",
    "db_conf = {\n",
    "    \"drivername\": \"postgresql+psycopg2\",\n",
    "    \"username\": \"postgres\",\n",
    "    \"password\": \"1234\",\n",
    "    \"host\": \"localhost\",\n",
    "    \"port\": 5432,\n",
    "    \"database\": database\n",
    "}\n",
    "db_conf = {\n",
    "    \"drivername\": \"sqlite\",\n",
    "    \"database\": \"database/{db}.db\".format(db = database)\n",
    "}\n",
    "url = URL.create(**db_conf)\n",
    "engine = create_engine(url, echo = False)\n",
    "if not database_exists(engine.url):\n",
    "    create_database(engine.url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SESSION1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session = sessionmaker(bind = engine, autoflush = True)\n",
    "session = Session()\n",
    "Base = declarative_base()\n",
    "metadata = MetaData()\n",
    "metadata.reflect(engine)\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "cols = [str(f).replace('app' + '.','') for f in metadata.tables['app'].c]\n",
    "cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data = {}"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ADMIN DB MODELS"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Langs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Lang(Base):\n",
    "    __tablename__ = 'lang'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Languages',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    lang_id     = Column(Integer, primary_key = True, autoincrement = True, comment = \"Lang ID\")\n",
    "    lang        = Column(String(4), unique = True, nullable = False, comment = \"Language\")\n",
    "    lang_desc   = Column(String(200), nullable = True, comment = \"Description\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "now = datetime.datetime.now()\n",
    "_data['lang'] = [\n",
    "    {'lang_id': 1, 'lang': 'en', 'lang_desc': 'Languages', 'created_at': now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Roles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Role(Base):\n",
    "    __tablename__ = 'role'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Roles',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    role_id     = Column(Integer, primary_key = True, autoincrement = True, comment = \"Role ID\")\n",
    "    role        = Column(String(20), nullable = False, unique = True, comment = \"Role\")\n",
    "    role_desc   = Column(Text, nullable = True, comment = \"Description\")\n",
    "    config      = Column(Text, nullable = True, comment = \"Config\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sess_2_dict(sess):\n",
    "    return \"{'\" + re.sub(r'\\s+=', \"': \", re.sub(r',\\s+', \", '\", sess)) + \"}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['role'] = [\n",
    "    {'role_id':  1, 'role':  'root', 'role_desc':  'Root role', 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class User(Base):\n",
    "    __tablename__ = 'user'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Users',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    user_id     = Column(Integer, primary_key = True, autoincrement = True, comment = \"User ID\")\n",
    "    username    = Column(String(50), unique = True, nullable = False, comment = \"Username\")\n",
    "    first_name  = Column(String(50), nullable = False, comment = \"Fisrt Name\")\n",
    "    last_name   = Column(String(50), nullable = False, comment = \"Last Name\")\n",
    "    email       = Column(String(50), unique = True, nullable = True, comment = \"Email\")\n",
    "    phone       = Column(String(50), unique = True, nullable = True, comment = \"Phone\")\n",
    "    password    = Column(String(200), nullable = False, comment = \"Password\")\n",
    "    role_id     = Column(Integer, ForeignKey('role.role_id'), comment = \"Default Role ID\")\n",
    "    lang_id     = Column(Integer, ForeignKey('lang.lang_id'), comment = \"Lang ID\")\n",
    "    timezone    = Column(String(50), nullable = True, comment = \"Timezone\")\n",
    "    attach_profile_pic = Column(String(200), nullable = True, comment = \"Profile Picture\")\n",
    "    active      = Column(Boolean, default = True, comment = \"Active\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def verify_password(plain_password: str, hashed_password: str) -> bool:\n",
    "        return pwd_context.verify(plain_password, hashed_password)\n",
    "def get_password_hash(password: str) -> str:\n",
    "    return pwd_context.hash(password)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    passw = get_password_hash('sdagfkjasgkfagsuy')\n",
    "    print(passw)\n",
    "except Exception as _err:# pylint: disable=broad-exception-caught\n",
    "    print(str(_err))\n",
    "    passw = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#verify_password('sdagfkjasgkfagsuy', passw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "passw = hashlib.md5('1234'.encode('utf-8')).hexdigest()\n",
    "passw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['user'] = [\n",
    "    {'user_id':  1, 'username':  'root', 'first_name':  'Super', 'last_name':  'Admin', 'email':  'root@domain.com', 'password':  passw, 'role_id':  1, 'lang_id':  1, 'active':  True, 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## User Roles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class UserRole(Base):\n",
    "    __tablename__ = 'user_role'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'User Roles',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    user_role_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"User Role ID\")\n",
    "    user_id      = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    role_id      = Column(Integer, ForeignKey('role.role_id'), comment = \"Role ID\")\n",
    "    active       = Column(Boolean, default = True, comment = \"Active\")\n",
    "    created_at   = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at   = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded     = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Apps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class App(Base):\n",
    "    __tablename__ = 'app'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Applications',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    app_id      = Column(Integer, primary_key = True, autoincrement = True, comment = \"App ID\")\n",
    "    app         = Column(String(20),  unique = True, nullable = False, comment = \"App Name\")\n",
    "    app_desc    = Column(Text,        nullable = True,  comment = \"Description\")\n",
    "    version     = Column(String(10),  nullable = False, comment = \"Version\")\n",
    "    email       = Column(String(200), nullable = True,  comment = \"Email\")\n",
    "    db          = Column(String(20),  nullable = False, comment = \"Database\")\n",
    "    attach_logo = Column(String(200), nullable = True,  comment = \"Logo\") \n",
    "    config      = Column(Text,        nullable = True,  comment = \"Config\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at  = Column(DateTime,    nullable = True,  comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime,    nullable = True,  comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean,     default = False,  comment = \"Excluded\")\n",
    "    # UniqueConstraint('app', 'version', name='uix_1')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "appConfig = {}\n",
    "_data['app'] = [\n",
    "    {'app_id':  1, 'app':  'ADMIN', 'app_desc':  'Admin', 'version':  '1.0.0', 'email':  None, 'db':  database, 'attach_logo':  None, 'config':  json.dumps(appConfig, ensure_ascii=False), 'user_id':  1, 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Menus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Menu(Base):\n",
    "    __tablename__ = 'menu'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Menus',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    menu_id     = Column(Integer, primary_key = True, autoincrement = True, comment = \"Menu ID\")\n",
    "    menu        = Column(String(20), nullable = False, comment = \"Menu\")\n",
    "    menu_desc   = Column(Text, nullable = True, comment = \"Description\")\n",
    "    menu_icon   = Column(String(20), nullable = True, comment = \"Icon\")\n",
    "    menu_order  = Column(Integer, nullable = True, comment = \"Order\")\n",
    "    menu_config = Column(Text, nullable = True, comment = \"Config\")\n",
    "    app_id      = Column(Integer, ForeignKey('app.app_id'), nullable = False, comment = \"App ID\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), nullable = False, comment = \"User ID\")\n",
    "    active      = Column(Boolean,  default = True, comment = \"Active\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['menu'] = [\n",
    "    {'menu_id':  1, 'menu':  'Admin', 'menu_desc':  'Admin', 'menu_icon':  'user-group', 'menu_order':  1, 'app_id':  1, 'user_id':  1, 'active':  True, 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'menu_id':  2, 'menu':  'Params', 'menu_desc':  None, 'menu_icon':  'adjustments', 'menu_order':  2, 'app_id':  1, 'user_id':  1, 'active':  True, 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'menu_id':  3, 'menu':  'ETL / Report / Base', 'menu_desc':  None, 'menu_icon':  'circle-stack', 'menu_order':  3, 'app_id':  1, 'user_id':  1, 'active':  True, 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Table(Base):\n",
    "    __tablename__ = 'table'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine'   : 'InnoDB',\n",
    "        'comment'        : 'Tables',\n",
    "        'mysql_charset'  : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    table_id     = Column(Integer, primary_key = True, autoincrement = True, comment = \"Table ID\")\n",
    "    table        = Column(String(50), nullable = False, comment = \"Table\")\n",
    "    table_desc   = Column(String(200), nullable = True, comment = \"Description\")\n",
    "    db           = Column(String(50), nullable = True, comment = \"Database\")\n",
    "    requires_rla = Column(Boolean,  default  = False, comment = \"Requires Row Level Access\")\n",
    "    user_id      = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at   = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at   = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded     = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## MenuTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class MenuTable(Base):\n",
    "    __tablename__ = 'menu_table'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine'   : 'InnoDB',\n",
    "        'comment'        : 'Menu Tables',\n",
    "        'mysql_charset'  : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    menu_table_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Menu Table ID\")\n",
    "    menu_id       = Column(Integer, ForeignKey('menu.menu_id'),   comment = \"Menu ID\")\n",
    "    table_id      = Column(Integer, ForeignKey('table.table_id'), comment = \"Table ID\")\n",
    "    app_id        = Column(Integer, ForeignKey('app.app_id'),     comment = \"App ID\")\n",
    "    user_id       = Column(Integer, ForeignKey('user.user_id'),   comment = \"User ID\")\n",
    "    active        = Column(Boolean,  default  = True,             comment = \"Active\")\n",
    "    requires_rla  = Column(Boolean,  default  = False,            comment = \"Requires Row Level Access\")\n",
    "    menu_table_cnf= Column(Text,     nullable = True,             comment = \"Config\")\n",
    "    created_at    = Column(DateTime, nullable = True,             comment = \"Created at\")\n",
    "    updated_at    = Column(DateTime, nullable = True,             comment = \"Updated at\")\n",
    "    excluded      = Column(Boolean,  default  = False,            comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RoleApp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RoleApp(Base):\n",
    "    __tablename__ = 'role_app'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine'   : 'InnoDB',\n",
    "        'comment'        : 'Role Apps',\n",
    "        'mysql_charset'  : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    role_app_id  = Column(Integer, primary_key = True, autoincrement = True, comment = \"Role App ID\")\n",
    "    role_id      = Column(Integer, ForeignKey('role.role_id'), comment = \"Role ID\")\n",
    "    app_id       = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    access       = Column(Boolean, default = True, comment = \"Access\")\n",
    "    user_id      = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at   = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at   = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded     = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Role App Menus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RoleAppMenu(Base):\n",
    "    __tablename__ = 'role_app_menu'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Role App Menus',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    role_app_menu_id  = Column(Integer, primary_key = True, autoincrement = True, comment = \"Role App Menu ID\")\n",
    "    role_id      = Column(Integer, ForeignKey('role.role_id'), comment = \"Role ID\")\n",
    "    app_id       = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    menu_id      = Column(Integer, ForeignKey('menu.menu_id'), comment = \"Menu ID\")\n",
    "    access       = Column(Boolean, default = True, comment = \"Access\")\n",
    "    user_id      = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at   = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at   = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded     = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Role App Menu Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RoleAppMenuTable(Base):\n",
    "    __tablename__ = 'role_app_menu_table'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Role App Menu Tables',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    role_app_menu_table_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Role App Menu Table ID\")\n",
    "    role_id      = Column(Integer, ForeignKey('role.role_id'), comment = \"Role ID\")\n",
    "    app_id       = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    menu_id      = Column(Integer, ForeignKey('menu.menu_id'), comment = \"Menu ID\")\n",
    "    table_id     = Column(Integer, ForeignKey('table.table_id'), comment = \"Table ID\")\n",
    "    create       = Column(Boolean, default = False, comment = \"Create\")\n",
    "    read         = Column(Boolean, default = False, comment = \"Read\")\n",
    "    update       = Column(Boolean, default = False, comment = \"Update\")\n",
    "    delete       = Column(Boolean, default = False, comment = \"Delete\")\n",
    "    user_id      = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at   = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at   = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded     = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Users Logs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class UserLog(Base):\n",
    "    __tablename__ = 'user_log'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'User Logs',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    user_log_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"User Log ID\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    action      = Column(String(200), nullable = False, comment = \"Action\")\n",
    "    req_ip      = Column(String(200), nullable = True, comment = \"Request IP\")\n",
    "    req_at      = Column(DateTime, nullable = True, comment = \"Request at\")\n",
    "    req_data    = Column(Text, nullable = True, comment = \"Request Data\")\n",
    "    res_at      = Column(DateTime, nullable = True, comment = \"Response at\")\n",
    "    res_type    = Column(String(200), nullable = True, comment = \"Response Type\")\n",
    "    res_msg     = Column(String(500), nullable = True, comment = \"Response Message\")\n",
    "    res_data    = Column(Text, nullable = True, comment = \"Request Data\")\n",
    "    table       = Column(String(200), nullable = True, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = True, comment = \"Database\")\n",
    "    row_id      = Column(Integer, nullable = True, comment = \"Database\")\n",
    "    app_id      = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    # old_data    = Column(Text, nullable = True, comment = \"Old Data\")\n",
    "    new_data    = Column(Text, nullable = True, comment = \"New Data\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Custom Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class CustomTable(Base):\n",
    "    __tablename__ = 'custom_table'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Custom Table',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    custom_table_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"User Log ID\")\n",
    "    table       = Column(String(200), nullable = True, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = True, comment = \"Database\")\n",
    "    config      = Column(Text, nullable = True, comment = \"Config\")\n",
    "    app_id      = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Custom Form"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class CustomForm(Base):\n",
    "    __tablename__ = 'custom_form'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Custom Form',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    custom_form_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Custom Form ID\")\n",
    "    table       = Column(String(200), nullable = True, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = True, comment = \"Database\")\n",
    "    config      = Column(Text, nullable = True, comment = \"Config\")\n",
    "    app_id      = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Role Row Level Access"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RoleRowLevelAccess(Base):\n",
    "    __tablename__ = 'role_row_level_access'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Role Row Level Access',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    role_row_level_access_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Role Row Level Access ID\")\n",
    "    role_id     = Column(Integer,  ForeignKey('role.role_id'), comment = \"Role ID\")\n",
    "    row_id      = Column(Integer, nullable = False, comment = \"Row ID\")\n",
    "    table_id    = Column(Integer, ForeignKey('table.table_id'), comment = \"Table ID\")\n",
    "    table       = Column(String(200), nullable = False, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    user_id     = Column(Integer,  ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id      = Column(Integer,  ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    read        = Column(Boolean,  default  = False, comment = \"Read\")\n",
    "    update      = Column(Boolean,  default  = False, comment = \"Update\")\n",
    "    delete      = Column(Boolean,  default  = False, comment = \"Delete\")\n",
    "    share       = Column(Boolean,  default  = False, comment = \"Share\")\n",
    "    created_at  = Column(DateTime, nullable = True,  comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True,  comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean,  default  = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Column Level Access"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ColumnLevelAccess(Base):\n",
    "    __tablename__ = 'column_level_access'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Column Level Access',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    column_level_access_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Column Level Access ID\")\n",
    "    column      = Column(Integer, nullable = False, comment = \"Column\")\n",
    "    table_id    = Column(Integer, ForeignKey('table.table_id'), comment = \"Table ID\")\n",
    "    table       = Column(String(200), nullable = False, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id      = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    create       = Column(Boolean, default = False, comment = \"Create\")\n",
    "    read        = Column(Boolean, default = False, comment = \"Read\")\n",
    "    update      = Column(Boolean, default = False, comment = \"Update\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Row Level Access"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RowLevelAccess(Base):\n",
    "    __tablename__ = 'row_level_access'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Row Level Access',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    row_level_access_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Row Level Access ID\")\n",
    "    row_id      = Column(Integer, nullable = False, comment = \"Row ID\")\n",
    "    table_id    = Column(Integer, ForeignKey('table.table_id'), comment = \"Table ID\")\n",
    "    table       = Column(String(200), nullable = False, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    user_id     = Column(Integer,  ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id      = Column(Integer,  ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    read        = Column(Boolean,  default  = False, comment = \"Read\")\n",
    "    update      = Column(Boolean,  default  = False, comment = \"Update\")\n",
    "    delete      = Column(Boolean,  default  = False, comment = \"Delete\")\n",
    "    share       = Column(Boolean,  default  = False, comment = \"Share\")\n",
    "    created_at  = Column(DateTime, nullable = True,  comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True,  comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean,  default  = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Translate Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class TranslateTable(Base):\n",
    "    __tablename__ = 'translate_table'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Translate Table',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    transl_tbl_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Translate Table ID\")\n",
    "    table_org_desc  = Column(String(200), nullable = False, comment = \"Table Org. Desc\")\n",
    "    table_transl_desc  = Column(String(200), nullable = False, comment = \"Table Transl. Desc\")\n",
    "    table       = Column(String(200), nullable = False, comment = \"Table\")\n",
    "    db          = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    lang        = Column(String(5), nullable = False, comment = \"Lang\")\n",
    "    user_id     = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id      = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at  = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at  = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded    = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Translate Table Fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class TranslateTableField(Base):\n",
    "    __tablename__ = 'translate_table_field'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Translate Table Fields',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    transl_tbl_field_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Translate Table Field ID\")\n",
    "    field_org_desc      = Column(String(200), nullable = False, comment = \"Field Org. Desc\")\n",
    "    field_transl_desc   = Column(String(200), nullable = False, comment = \"Field Transl. Desc\")\n",
    "    field               = Column(String(200), nullable = False, comment = \"Field\")\n",
    "    table               = Column(String(200), nullable = False, comment = \"Table\")\n",
    "    db                  = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    lang                = Column(String(5),   nullable = False, comment = \"Lang\")\n",
    "    user_id             = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id              = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at          = Column(DateTime,    nullable = True, comment = \"Created at\")\n",
    "    updated_at          = Column(DateTime,    nullable = True, comment = \"Updated at\")\n",
    "    excluded            = Column(Boolean,     default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# REPORTS / BASES"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Periodicity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Periodicity(Base):\n",
    "    __tablename__ = 'periodicity'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Periodicity',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    periodicity_id  = Column(Integer, primary_key = True, autoincrement = True, comment = \"Periodicity ID\")\n",
    "    periodicity     = Column(String(100), unique = True, nullable = False, comment = \"Periodicity\")\n",
    "    created_at      = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at      = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded        = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['periodicity'] = [\n",
    "    {'periodicity_id':  1, 'periodicity':  'Daily', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'periodicity_id':  2, 'periodicity':  'Monthly', 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ETL / REPORT / BASE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBase(Base):\n",
    "    __tablename__ = 'etl_report_base'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'ETL / Report / Base',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_report_base_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_report_base       = Column(String(200), unique = True, nullable = False, comment = \"ETL / Report / Base\")\n",
    "    etl_report_base_desc  = Column(Text, nullable = True, comment = \"Description\")   \n",
    "    attach_etl_rbase_doc  = Column(String(200), nullable = True, comment = \"Documentation\")\n",
    "    periodicity_id        = Column(Integer, ForeignKey('periodicity.periodicity_id'), comment = \"Periodicity\")\n",
    "    database              = Column(String(200), nullable = False, comment = \"Database\") \n",
    "    includes_data_quality = Column(Boolean, default = False, comment = \"Includes Data Quality\")\n",
    "    includes_data_reconci = Column(Boolean, default = False, comment = \"Includes Data Reconc.\")\n",
    "    includes_exports      = Column(Boolean, default = True, comment = \"Includes Exports\")\n",
    "    includes_notify       = Column(Boolean, default = True, comment = \"Includes Notefy\")\n",
    "    etl_report_base_conf  = Column(Text, nullable = True, comment = \"Config\")\n",
    "    active                = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id               = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at            = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at            = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded              = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## InputType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class InputType(Base):\n",
    "    __tablename__ = 'input_type'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Input Type',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    input_type_id  = Column(Integer, primary_key = True, autoincrement = True, comment = \"Input Type ID\")\n",
    "    input_type     = Column(String(100), unique = True, nullable = False, comment = \"Input Type\")\n",
    "    created_at     = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at     = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['input_type'] = [\n",
    "    {'input_type_id':  1, 'input_type':  'Aux', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'input_type_id':  2, 'input_type':  'Main', 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SourceType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class SourceType(Base):\n",
    "    __tablename__ = 'source_type'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Source Type',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    source_type_id = Column(Integer, primary_key = True, autoincrement = True, comment = \"Source Type ID\")\n",
    "    source_type    = Column(String(100), unique = True, nullable = False, comment = \"Souce Type\")\n",
    "    created_at     = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at     = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['source_type'] = [\n",
    "    {'source_type_id':  1, 'source_type':  'File', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'source_type_id':  2, 'source_type':  'Database', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'source_type_id':  3, 'source_type':  'FTP', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'source_type_id':  4, 'source_type':  'eMail', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'source_type_id':  5, 'source_type':  'FileSystem', 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INPUT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseInput(Base):\n",
    "    __tablename__ = 'etl_rbase_input'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Inputs',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rbase_input_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rbase_input       = Column(String(200), nullable = False, comment = \"Input\")\n",
    "    etl_rbase_input_desc  = Column(Text, nullable = True, comment = \"Input Description\")\n",
    "    etl_report_base_id    = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), nullable = False, comment = \"ETL / Report / Base ID\")\n",
    "    input_type_id         = Column(Integer, ForeignKey('input_type.input_type_id'), comment = \"Input Type ID\")\n",
    "    save_only_temp        = Column(Boolean, default = True, comment = \"Save only temp\")\n",
    "    replace_existing_data = Column(Boolean, default = False, comment = \"Replace existing data\")\n",
    "    check_ref_date        = Column(Boolean, default = False, comment = \"Check ref. date\")\n",
    "    ref_date_field        = Column(String(200), nullable = True, comment = \"Ref. date field\")\n",
    "    date_format_org       = Column(String(200), nullable = True, comment = \"Date Format in origin\")\n",
    "    other_date_fields     = Column(String(200), nullable = True, comment = \"Other Date Fields\")\n",
    "    ref_id_keys           = Column(String(200), nullable = True, comment = \"Reference / Id Keys\")\n",
    "    last_update_date_field= Column(String(200), nullable = True, comment = \"Last update date field\")\n",
    "    incremental_extract   = Column(Boolean, default = False, comment = \"Incremental Extraction\")\n",
    "    destination_table     = Column(String(200), nullable = False, comment = \"Destination Table\")\n",
    "    database              = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    allow_import          = Column(Boolean, default = True, comment = \"Allow Import\")\n",
    "    multiple_sheets       = Column(Boolean, default = False, comment = \"Multiple Sheets\")\n",
    "    specific_sheets       = Column(String(200), nullable = True, comment = \"Specific Sheets\")\n",
    "    specific_range        = Column(String(200), nullable = True, comment = \"Specific Range\")\n",
    "    columns_to_import     = Column(String(200), nullable = True, comment = \"Columns to Import\")\n",
    "    txt_fix_format_layout = Column(String(200), nullable = True, comment = \"Text with fixed format Layout\")\n",
    "    headers               = Column(String(200), nullable = True, comment = \"Headers\")\n",
    "    spreadsheet_forms     = Column(Boolean, default = False, comment = \"Spreadsheet Forms\")\n",
    "    spreadsheet_forms_map = Column(String(200), nullable = True, comment = \"Spreadsheet Forms Map\")\n",
    "    etl_rbase_input_conf  = Column(Text, nullable = True, comment = \"Config\")\n",
    "    active                = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id               = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at            = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at            = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded              = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Output Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class OutputType(Base):\n",
    "    __tablename__ = 'output_type'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Output Type',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    output_type_id  = Column(Integer, primary_key = True, autoincrement = True, comment = \"Output Type ID\")\n",
    "    output_type     = Column(String(100), unique = True, nullable = False, comment = \"Souce Type\")\n",
    "    created_at      = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at      = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded        = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['output_type'] = [\n",
    "    {'output_type_id':  1, 'output_type':  'Table', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'output_type_id':  2, 'output_type':  'View', 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OUTPUT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseOutput(Base):\n",
    "    __tablename__ = 'etl_rbase_output'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Outputs',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rbase_output_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rbase_output       = Column(String(200), unique = False, nullable = False, comment = \"Output\")\n",
    "    etl_rbase_output_desc  = Column(Text, nullable = True, comment = \"Output Description\")\n",
    "    etl_report_base_id     = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), nullable = False, comment = \"ETL / Report / Base ID\")\n",
    "    output_type_id         = Column(Integer, ForeignKey('output_type.output_type_id'), comment = \"Input Type ID\")\n",
    "    date_field             = Column(String(200), nullable = True, comment = \"Date field\")\n",
    "    date_field_format      = Column(String(200), nullable = True, comment = \"Date Format\")\n",
    "    destination_table      = Column(String(200), nullable = False, comment = \"Destination Table\")\n",
    "    database               = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    append_it              = Column(Boolean, default = False, comment = \"Append\")\n",
    "    output_order           = Column(Integer, nullable = True, comment = \"Field Order\")\n",
    "    etl_rbase_output_conf  = Column(Text, nullable = True, comment = \"Config\")\n",
    "    active                 = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id                = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                 = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at             = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at             = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded               = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## OUTPUT FIELDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseOutputFields(Base):\n",
    "    __tablename__ = 'etl_rb_output_field'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Output Fields',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rb_output_field_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rb_output_field       = Column(String(200), nullable = False, comment = \"Field\")\n",
    "    etl_rb_output_field_desc  = Column(Text, nullable = True, comment = \"Field Description\")\n",
    "    etl_rbase_output_id       = Column(Integer, ForeignKey('etl_rbase_output.etl_rbase_output_id'), nullable = False, comment = \"Output ID\")\n",
    "    sql_select                = Column(Text, nullable = False, comment = \"SELECT\")\n",
    "    sql_from                  = Column(Text, nullable = True,  comment = \"FROM\")\n",
    "    sql_join                  = Column(Text, nullable = True,  comment = \"JOIN\")\n",
    "    sql_where                 = Column(Text, nullable = True,  comment = \"WHERE\")\n",
    "    sql_group_by              = Column(Text, nullable = True,  comment = \"GROUP BY\")\n",
    "    sql_order_by              = Column(Text, nullable = True,  comment = \"ORDER BY\")\n",
    "    sql_window                = Column(Text, nullable = True,  comment = \"WINDOW\")\n",
    "    sql_having                = Column(Text, nullable = True,  comment = \"HAVING\")\n",
    "    field_order               = Column(Integer, nullable = True, comment = \"Field Order\")\n",
    "    fields_used               = Column(Text, nullable = True, comment = \"Fields Used\")\n",
    "    etl_report_base_id        = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), comment = \"ETL / Report / Base ID\")\n",
    "    active                    = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id                   = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                    = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at                = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at                = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded                  = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## DATA QUALITY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseDataQuality(Base):\n",
    "    __tablename__ = 'etl_rbase_quality'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Data Quality',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rbase_quality_id   = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rbase_quality      = Column(String(200), unique = False, nullable = False, comment = \"Data Quality Rule\")\n",
    "    etl_rbase_quality_desc = Column(Text, nullable = True, comment = \"Rule Description\")\n",
    "    etl_report_base_id     = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), nullable = False, comment = \"ETL / Report / Base ID\")\n",
    "    sql_quality_check      = Column(Text, nullable = False, comment = \"SQL Quality Check\")\n",
    "    sql_quality_fix        = Column(Text, nullable = True, comment = \"SQL Quality Fix\")\n",
    "    comments               = Column(Text, nullable = True, comment = \"Comments / Justifications\")\n",
    "    fields                 = Column(Text, nullable = True, comment = \"Fields\")\n",
    "    tables                 = Column(Text, nullable = True, comment = \"Tables\")\n",
    "    database               = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    etl_rbase_quality_conf = Column(Text, nullable = True, comment = \"Rule Description\")\n",
    "    active                 = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id                = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                 = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at             = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at             = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded               = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RECONCILIATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseDataReconciliation(Base):\n",
    "    __tablename__ = 'etl_rb_reconcilia'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Data Reconciliation',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rb_reconcilia_id   = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rb_reconcilia      = Column(String(200), unique = False, nullable = False, comment = \"Data Reconciliation Rule\")\n",
    "    etl_rb_reconcilia_desc = Column(Text, nullable = True, comment = \"Rule Description\")\n",
    "    etl_rb_reconc_template = Column(Text, nullable = True, comment = \"Template\")\n",
    "    database               = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    active                 = Column(Boolean, default = True, comment = \"Active\")\n",
    "    etl_report_base_id     = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), nullable = False, comment = \"ETL / Report / Base ID\")\n",
    "    user_id                = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                 = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at             = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at             = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded               = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## RECONCILIATION DEATILS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseDataReconciDetail(Base):\n",
    "    __tablename__ = 'etl_rb_reconc_dtail'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Data Reconciliation',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rb_reconc_dtail_id   = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rb_reconc_dtail      = Column(String(50), nullable = False, comment = \"Variable Name\")\n",
    "    etl_rb_reconc_dtail_desc = Column(String(200), nullable = False, comment = \"Var. Description\")\n",
    "    sql_query_val_1          = Column(Text, nullable = True, comment = \"SQL Query Valor 1\")\n",
    "    sql_query_val_2          = Column(Text, nullable = True, comment = \"SQL Query Valor 2\")\n",
    "    is_eval_formula          = Column(Boolean, default = True, comment = \"Is Eval Formula\")\n",
    "    sql_reconcilia_query     = Column(Text, nullable = True, comment = \"SQL / Formula Reconc\")\n",
    "    comments                 = Column(Text, nullable = True, comment = \"Comments / Justifications\")\n",
    "    etl_rb_reconcilia_id     = Column(Integer, ForeignKey('etl_rb_reconcilia.etl_rb_reconcilia_id'), nullable = False, comment = \"Data Reconciliation ID\")\n",
    "    etl_report_base_id       = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), comment = \"ETL / Report / Base ID\")\n",
    "    active                   = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id                  = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                   = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at               = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at               = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded                 = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ExportType(Base):\n",
    "    __tablename__ = 'export_type'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Export Type',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    export_type_id  = Column(Integer, primary_key = True, autoincrement = True, comment = \"Export Type ID\")\n",
    "    export_type     = Column(String(100), unique = True, nullable = False, comment = \"Export Type\")\n",
    "    created_at      = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at      = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded        = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['export_type'] = [\n",
    "    {'export_type_id':  1, 'export_type':  'File', 'created_at':  now, 'updated_at':  now, 'excluded':  False},\n",
    "    {'export_type_id':  2, 'export_type':  'Template', 'created_at':  now, 'updated_at':  now, 'excluded':  False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## EXPORTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseExport(Base):\n",
    "    __tablename__ = 'etl_rbase_export'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Exports',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rbase_export_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rbase_export       = Column(String(200), unique = False, nullable = False, comment = \"Export\")\n",
    "    etl_rbase_export_desc  = Column(Text, nullable = True, comment = \"Export Description\")\n",
    "    etl_report_base_id     = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), nullable = False, comment = \"ETL / Report / Base ID\")\n",
    "    export_type_id         = Column(Integer, ForeignKey('export_type.export_type_id'), nullable = False, comment = \"Export ID\")\n",
    "    attach_file_template   = Column(String(200), nullable = False, comment = \"File name / Template\")\n",
    "    txt_fix_format_layout  = Column(Text, nullable = True, comment = \"Text with fixed format Layout\")\n",
    "    txt_fix_format_header  = Column(Text, nullable = True, comment = \"Text with fixed format Headers\")\n",
    "    etl_rbase_export_conf  = Column(Text, nullable = True, comment = \"Config\")\n",
    "    etl_rbase_output_id    = Column(Integer, ForeignKey('etl_rbase_output.etl_rbase_output_id'), nullable = False, comment = \"Output ID\")\n",
    "    database               = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    active                 = Column(Boolean, default = True, comment = \"Active\")\n",
    "    ignore                 = Column(Boolean, default = False, comment = \"Ignore\")\n",
    "    user_id                = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                 = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at             = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at             = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded               = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## EXPORTS DETAILS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseExportDetail(Base):\n",
    "    __tablename__ = 'etl_rb_exp_dtail'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Export Details',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    etl_rb_exp_dtail_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    etl_rb_exp_dtail       = Column(String(200), unique = False, nullable = False, comment = \"Export Detail\")\n",
    "    etl_rb_exp_dtail_desc  = Column(Text, nullable = True, comment = \"Export Detail Description\")\n",
    "    etl_rbase_export_id    = Column(Integer, ForeignKey('etl_rbase_export.etl_rbase_export_id'), nullable = False, comment = \"Export ID\")\n",
    "    etl_report_base_id     = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), nullable = False, comment = \"ETL / Report / Base ID\")\n",
    "    sql_export_query       = Column(Text, nullable = True, comment = \"Export SQL Query\")\n",
    "    database               = Column(String(200), nullable = False, comment = \"Database\")\n",
    "    # txt_fix_format_layout  = Column(Text, nullable = True, comment = \"Text with fixed format Layout\")\n",
    "    dest_sheet_name        = Column(String(200), nullable = True, comment = \"Dest. Sheet Name\")\n",
    "    dest_table_name        = Column(String(200), nullable = True, comment = \"Dest. Table Name\")\n",
    "    etl_rb_exp_dtail_conf  = Column(Text, nullable = True, comment = \"Config\")\n",
    "    active                 = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id                = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id                 = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at             = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at             = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded               = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## LOGS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseLogs(Base):\n",
    "    __tablename__ = 'etl_report_base_log'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Logs ETL / Report / Base',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    log_id   = Column(Integer, primary_key=True, autoincrement=True, comment=\"ID Log\")\n",
    "    type     = Column(String(50), nullable=False, comment=\"Type\")\n",
    "    name     = Column(String(100), nullable=False, comment=\"Name\")\n",
    "    ref      = Column(Date, nullable=True, comment=\"Ref. Date\")\n",
    "    start    = Column(DateTime, nullable=True, comment=\"Started At\")\n",
    "    end      = Column(DateTime, nullable=True, comment=\"End At\")\n",
    "    timer    = Column(String(10), nullable=True, comment=\"Duration\")\n",
    "    success  = Column(Boolean, nullable=True, comment=\"Success\")\n",
    "    msg      = Column(Text, nullable=True, comment=\"Response\")\n",
    "    num_rows = Column(Integer, nullable=True, comment=\"Affected Rows\")\n",
    "    errors   = Column(Integer, nullable=True, comment=\"Errors\")\n",
    "    fixes    = Column(Integer, nullable=True, comment=\"Automated Fixes\")\n",
    "    fname    = Column(String(200), nullable=True, comment=\"Generated Files\")\n",
    "    html     = Column(Text, nullable=True, comment=\"Html\")\n",
    "    etl_report_base_id = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), comment = \"ETL / Report / Base ID\")\n",
    "    user_id    = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded   = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NOTIFICATIONS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ETLReportBaseNotify(Base):\n",
    "    __tablename__ = 'etl_rbase_notify'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Notify',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    notify_id      = Column(Integer, primary_key=True, autoincrement=True, comment=\"ID\")\n",
    "    notify_subject = Column(String(200), nullable=False, comment=\"Subject\")\n",
    "    notify_body    = Column(Text, nullable=False, comment=\"Body\")\n",
    "    notify_to      = Column(String(200), nullable=False, comment=\"Mail TO\")\n",
    "    notify_cc      = Column(String(200), nullable=True, comment=\"Mail CC\")\n",
    "    notify_attach_exports = Column(Boolean, nullable=True, default = False, comment=\"Attach Exports\")\n",
    "    notify_copy_exports_to = Column(Boolean, nullable=True, comment=\"Copy Exports\")\n",
    "    notify_copy_exports_path = Column(String(200), nullable=True, comment=\"Copy Exports Path\")\n",
    "    notify_conf    = Column(Text, nullable=True, comment=\"Conf\")\n",
    "    send_email     = Column(Boolean, default = True, comment = \"Send Email\")\n",
    "    active         = Column(Boolean, default = True, comment = \"Active\")\n",
    "    etl_report_base_id = Column(Integer, ForeignKey('etl_report_base.etl_report_base_id'), comment = \"ETL / Report / Base ID\")\n",
    "    user_id        = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    created_at     = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at     = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QUERY MANEGMENT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class ManageQuery(Base):\n",
    "    __tablename__ = 'manage_query'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine' : 'InnoDB',\n",
    "        'comment'      : 'Queries',\n",
    "        'mysql_charset': 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    manage_query_id    = Column(Integer, primary_key = True, autoincrement = True, comment = \"ID\")\n",
    "    manage_query       = Column(String(200), unique = False, nullable = False, comment = \"Query Desc\")\n",
    "    database           = Column(String(200), nullable = False, comment = \"Database\") \n",
    "    manage_query_conf  = Column(Text, nullable = True, comment = \"Query Config\")\n",
    "    active             = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id            = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id             = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at         = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at         = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded           = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DASHBOARD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Dashboard(Base):\n",
    "    __tablename__ = 'dashboard'\n",
    "    __table_args__ = {\n",
    "        'extend_existing': True,\n",
    "        'mysql_engine'   : 'InnoDB',\n",
    "        'comment'        : 'Dashboards',\n",
    "        'mysql_charset'  : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    dashboard_id   = Column(Integer, primary_key = True, autoincrement = True, comment = \"Dashboard ID\")\n",
    "    dashboard      = Column(String(200), comment = \"Dashboard\")\n",
    "    dashboard_desc = Column(Text, nullable = True, comment = \"Description\")\n",
    "    dashboard_conf = Column(Text, nullable = False, comment = \"Conf / Params\")\n",
    "    order          = Column(Integer, nullable = True, comment = \"Order\")\n",
    "    active         = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id        = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id         = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at     = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at     = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TASKS"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calendar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Calendar(Base):\n",
    "    __tablename__ = 'calendar'\n",
    "    __table_args__ = {\n",
    "        'extend_existing'     : True,\n",
    "        'mysql_engine'        : 'InnoDB',\n",
    "        'comment'             : 'Calendar',\n",
    "        'mysql_charset'       : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    calendar_id    = Column(Integer, primary_key=True, autoincrement=True, comment=\"Calendar ID\")\n",
    "    calendar       = Column(String(100), nullable=False, comment=\"Calendar\")\n",
    "    calendar_desc  = Column(Text, nullable=True, comment=\"Description\")\n",
    "    calendar_email = Column(String(200), nullable=True, comment=\"Email\")\n",
    "    calendar_color = Column(String(50), nullable=True, comment=\"Calendar Color\")\n",
    "    user_id        = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id         = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at     = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at     = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### TaskStatus"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class TaskStatus(Base):\n",
    "    __tablename__ = 'task_status'\n",
    "    __table_args__ = {\n",
    "        'extend_existing'     : True,\n",
    "        'mysql_engine'        : 'InnoDB',\n",
    "        'comment'             : 'Status',\n",
    "        'mysql_charset'       : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    task_status_id = Column(Integer, primary_key=True, autoincrement=True, comment=\"Status ID\")\n",
    "    task_status    = Column(String(10), nullable=False, comment=\"Status\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['task_status'] = [\n",
    "    {'task_status_id': 1, 'task_status':  'Atribuido', 'excluded': False},\n",
    "    {'task_status_id': 2, 'task_status':  'Staterd', 'excluded': False},\n",
    "    {'task_status_id': 3, 'task_status':  'On Standby', 'excluded': False},\n",
    "    {'task_status_id': 4, 'task_status':  'Concluded', 'excluded': False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### RepeatType"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class RepeatType(Base):\n",
    "    __tablename__ = 'repeat_type'\n",
    "    __table_args__ = {\n",
    "        'extend_existing'     : True,\n",
    "        'mysql_engine'        : 'InnoDB',\n",
    "        'comment'             : 'Type of repetition',\n",
    "        'mysql_charset'       : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    repeat_type_id = Column(Integer, primary_key=True, autoincrement=True, comment=\"Type of repetition ID\")\n",
    "    repeat_type    = Column(String(10), nullable=False, comment=\"Type of Repetition\")\n",
    "    excluded       = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_data['repeat_type'] = [\n",
    "    {'repeat_type_id': 1, 'repeat_type':  'Daily', 'excluded': False},\n",
    "    {'repeat_type_id': 2, 'repeat_type':  'Weekly', 'excluded': False},\n",
    "    {'repeat_type_id': 3, 'repeat_type':  'Monthly', 'excluded': False},\n",
    "    {'repeat_type_id': 4, 'repeat_type':  'Anualy', 'excluded': False}\n",
    "]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Task"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class Task(Base):\n",
    "    __tablename__ = 'task'\n",
    "    __table_args__ = {\n",
    "        'extend_existing'     : True,\n",
    "        'mysql_engine'        : 'InnoDB',\n",
    "        'comment'             : 'Tasks',\n",
    "        'mysql_charset'       : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    task_id           = Column(Integer, primary_key=True, autoincrement=True, comment=\"Task Id\")\n",
    "    task              = Column(String(100), nullable=False, comment=\"task\")\n",
    "    task_desc         = Column(Text, nullable=True, comment=\"Description\")\n",
    "    starts_at         = Column(DateTime, nullable=False, comment=\"Starts at\")\n",
    "    ends_at           = Column(DateTime, nullable=True, comment=\"Ends at\")\n",
    "    calendar_id       = Column(Integer, ForeignKey('calendar.calendar_id'), comment=\"Calendar ID\")\n",
    "    calendar_color    = Column(String(50), nullable=True, comment=\"Calendar Color\")\n",
    "    calendar_email    = Column(String(200), nullable=True, comment=\"Email\")\n",
    "    task_status_id    = Column(Integer, ForeignKey('task_status.task_status_id'), nullable=True, default=None, comment=\"Status ID\")\n",
    "    task_status       = Column(String(50), nullable=True, default=None, comment=\"Status\")\n",
    "    attach_task       = Column(String(200), nullable=True, comment=\"Attachment\")\n",
    "    # REPEATE\n",
    "    repeat            = Column(Boolean, nullable=True, default=True, comment=\"Repeat\")\n",
    "    repeat_type_id    = Column(Integer, ForeignKey('repeat_type.repeat_type_id'), nullable=True, default=None, comment=\"Repeat Type ID\")\n",
    "    days_of_week      = Column(String(200), nullable=True, comment=\"Days of week\") #daysOfWeek\n",
    "    repeat_start_date = Column(Date, nullable=True, comment=\"Repeat start date\") #startRecur\n",
    "    repeat_start_time = Column(Time, nullable=True, comment=\"Repeat start time\") #startTime\n",
    "    repeat_end_date   = Column(Date, nullable=True, comment=\"Repeat end date\")  #endRecur\n",
    "    repeat_end_time   = Column(Time, nullable=True, comment=\"Repeat end time\")  #endTime\n",
    "    attendees         = Column(Text, nullable=True, comment=\"Attendees\")\n",
    "    active            = Column(Boolean, default = True, comment = \"Active\")\n",
    "    user_id           = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id            = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at        = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at        = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded          = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### TaskTrack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class TaskTrack(Base):\n",
    "    __tablename__ = 'task_track'\n",
    "    __table_args__ = {\n",
    "        'extend_existing'     : True,\n",
    "        'mysql_engine'        : 'InnoDB',\n",
    "        'comment'             : 'Status Updates',\n",
    "        'mysql_charset'       : 'utf8',\n",
    "        'sqlite_autoincrement': True\n",
    "    }\n",
    "    task_track_id     = Column(Integer, primary_key=True, autoincrement=True, comment=\"Status Update ID\")\n",
    "    task_track        = Column(String(100), nullable=False, comment=\"Status Update\")\n",
    "    task_track_desc   = Column(Text, nullable=True, comment=\"Description\")\n",
    "    task_track_date   = Column(DateTime, nullable=False, comment=\"Date\")\n",
    "    task_status_id    = Column(Integer, ForeignKey('task_status.task_status_id'), nullable=True, default=None, comment=\"Status ID\")\n",
    "    attach_task_track = Column(String(200), nullable=True, comment=\"Attachment\")\n",
    "    task_id           = Column(Integer, ForeignKey('task.task_id'), comment=\"Task ID\")\n",
    "    user_id           = Column(Integer, ForeignKey('user.user_id'), comment = \"User ID\")\n",
    "    app_id            = Column(Integer, ForeignKey('app.app_id'), comment = \"App ID\")\n",
    "    created_at        = Column(DateTime, nullable = True, comment = \"Created at\")\n",
    "    updated_at        = Column(DateTime, nullable = True, comment = \"Updated at\")\n",
    "    excluded          = Column(Boolean, default = False, comment = \"Excluded\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DROP FIRST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-10T15:37:33.344680Z",
     "start_time": "2020-09-10T15:37:33.341320Z"
    },
    "execution": {
     "iopub.execute_input": "2021-04-10T20:57:56.267455Z",
     "iopub.status.busy": "2021-04-10T20:57:56.267455Z",
     "iopub.status.idle": "2021-04-10T20:57:56.282450Z",
     "shell.execute_reply": "2021-04-10T20:57:56.281449Z",
     "shell.execute_reply.started": "2021-04-10T20:57:56.267455Z"
    }
   },
   "outputs": [],
   "source": [
    "#session.close()\n",
    "#ETLReportBase.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseInput.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseOutput.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseOutputFields.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseDataQuality.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseDataReconciDetail.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseDataReconciliation.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseNotify.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseExport.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseExportDetail.__table__.drop(bind = engine, checkfirst = True)\n",
    "#ETLReportBaseLogs.__table__.drop(bind = engine, checkfirst = True)\n",
    "#Task.__table__.drop(bind = engine, checkfirst = True)\n",
    "ETLReportBaseOutputFields.__table__.drop(bind = engine, checkfirst = True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DROP ALL FIRST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-10T15:37:35.633120Z",
     "start_time": "2020-09-10T15:37:34.926803Z"
    },
    "execution": {
     "iopub.execute_input": "2021-04-10T21:11:22.296302Z",
     "iopub.status.busy": "2021-04-10T21:11:22.296302Z",
     "iopub.status.idle": "2021-04-10T21:11:22.339809Z",
     "shell.execute_reply": "2021-04-10T21:11:22.338808Z",
     "shell.execute_reply.started": "2021-04-10T21:11:22.296302Z"
    }
   },
   "outputs": [],
   "source": [
    "#session.close()\n",
    "try:\n",
    "    pass\n",
    "    #Base.metadata.drop_all(bind = engine, checkfirst = True)\n",
    "except Exception as e:\n",
    "    print(str(e))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TAXY YYYYMMDD\n",
    "joao.a.d.tavares91@gmail.com\n",
    "~/Downloads/TaxiTest/YYYY/MM/DD"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CREATE ALL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-10T15:37:39.969942Z",
     "start_time": "2020-09-10T15:37:37.742791Z"
    },
    "execution": {
     "iopub.execute_input": "2021-04-10T21:11:30.121805Z",
     "iopub.status.busy": "2021-04-10T21:11:30.121805Z",
     "iopub.status.idle": "2021-04-10T21:11:30.816548Z",
     "shell.execute_reply": "2021-04-10T21:11:30.815548Z",
     "shell.execute_reply.started": "2021-04-10T21:11:30.121805Z"
    }
   },
   "outputs": [],
   "source": [
    "Base.metadata.create_all(bind = engine, checkfirst = True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ADD DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#session.close()\n",
    "tables = []\n",
    "for cls in Base.__subclasses__():\n",
    "    tables.append(cls.__tablename__)\n",
    "#print(tables)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "_updated_tables = None # ['periodicity', 'etl_report_base', 'input_type', 'source_type', 'etl_rbase_input', 'output_type', 'etl_rbase_output', 'etl_rb_output_field', 'etl_rbase_quality', 'etl_rb_reconcilia', 'export_type', 'etl_rbase_export', 'etl_rb_exp_dtail', 'etl_report_base_log', 'etl_rbase_notify']\n",
    "_updated_tables = None #['calendar', 'repeat_type', 'task_status', 'task', 'task_track']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for table in _data:\n",
    "    if not _updated_tables:\n",
    "        pass\n",
    "    elif len(_updated_tables) == 0:\n",
    "        pass\n",
    "    elif table not in _updated_tables:\n",
    "        continue    \n",
    "    print(table, '...')\n",
    "    tbl = _Table(table, metadata, autoload_with = engine)\n",
    "    with engine.connect() as conn_admin:\n",
    "        try: \n",
    "            sql = insert(tbl).values(_data.get(table))\n",
    "            result = conn_admin.execute(sql)\n",
    "            print(table, result.rowcount, 'rows inserted')\n",
    "            result.close()\n",
    "            conn_admin.commit()\n",
    "            conn_admin.close()\n",
    "        except Exception as e:\n",
    "            print(str(e)[0:100])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = Base.metadata.tables\n",
    "print(tables)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# REGISTER ALL THE TABLE AND THE COMMENTS / LABELS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-10T15:37:40.341633Z",
     "start_time": "2020-09-10T15:37:40.312595Z"
    },
    "code_folding": [
     8,
     21,
     33
    ],
    "execution": {
     "iopub.execute_input": "2021-04-10T14:22:00.706720Z",
     "iopub.status.busy": "2021-04-10T14:22:00.706720Z",
     "iopub.status.idle": "2021-04-10T14:22:00.978660Z",
     "shell.execute_reply": "2021-04-10T14:22:00.978660Z",
     "shell.execute_reply.started": "2021-04-10T14:22:00.706720Z"
    }
   },
   "outputs": [],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "now = datetime.datetime.now()\n",
    "_data = {}\n",
    "for cls in Base.__subclasses__():\n",
    "    if not _updated_tables:\n",
    "        pass\n",
    "    elif len(_updated_tables) == 0:\n",
    "        pass\n",
    "    elif cls.__tablename__ not in _updated_tables:\n",
    "        continue\n",
    "    args = cls.__table_args__\n",
    "    if not _data.get('table'):\n",
    "        _data['table'] = []\n",
    "    _data['table'].append({\n",
    "        'table': cls.__tablename__,\n",
    "        'table_desc': args['comment'],\n",
    "        'db': database,\n",
    "        'user_id': 1,\n",
    "        'created_at': now,\n",
    "        'updated_at': now,\n",
    "        'excluded': False\n",
    "    })\n",
    "    if not _data.get('translate_table'):\n",
    "        _data['translate_table'] = []\n",
    "    _data['translate_table'].append({\n",
    "        'table_org_desc': args['comment'],\n",
    "        'table_transl_desc': args['comment'],\n",
    "        'table': cls.__tablename__,\n",
    "        'db': database,\n",
    "        'lang': 'en',\n",
    "        'user_id': 1,\n",
    "        'created_at': now,\n",
    "        'updated_at': now,\n",
    "        'excluded': False\n",
    "    })\n",
    "    for c in cls.__table__.columns:\n",
    "        if not _data.get('translate_table_field'):\n",
    "            _data['translate_table_field'] = []\n",
    "        _data['translate_table_field'].append({\n",
    "                'field_org_desc': c.comment,\n",
    "                'field_transl_desc': c.comment,\n",
    "                'field': str(c.name),\n",
    "                'table': cls.__tablename__,\n",
    "                'db': database,\n",
    "                'lang': 'en',\n",
    "                'user_id': 1,\n",
    "                'created_at': now,\n",
    "                'updated_at': now,\n",
    "                'excluded': False\n",
    "            })\n",
    "        j += 1\n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "chk_first = True\n",
    "for table in _data:\n",
    "    print(table, '...')\n",
    "    tbl = _Table(table, metadata, autoload_with = engine)\n",
    "    sql = delete(tbl).where(tbl.c.db == database)\n",
    "    with engine.connect() as conn_admin:\n",
    "        if chk_first == True:\n",
    "            for d in _data.get(table):\n",
    "                sql = select(tbl.c)\\\n",
    "                    .select_from(tbl)\\\n",
    "                    .where(and_(\n",
    "                        tbl.c.db == database,\n",
    "                        tbl.c.table == d.get('table')\n",
    "                    ))\n",
    "                if table == 'translate_table_field':\n",
    "                    sql = select(tbl.c)\\\n",
    "                        .select_from(tbl)\\\n",
    "                        .where(and_(\n",
    "                            tbl.c.db == database,\n",
    "                            tbl.c.table == d.get('table'),\n",
    "                            tbl.c.field == d.get('field')\n",
    "                        ))\n",
    "                _curr = conn_admin.execute(sql).fetchone()\n",
    "                if _curr:\n",
    "                    # print('_curr:', _curr)\n",
    "                    sql = update(tbl)\\\n",
    "                        .where(and_(\n",
    "                            tbl.c.db == database,\n",
    "                            tbl.c.table == d.get('table')\n",
    "                        ))\\\n",
    "                        .values(d)\n",
    "                    if table == 'translate_table_field':\n",
    "                        sql = update(tbl)\\\n",
    "                            .where(and_(\n",
    "                                tbl.c.db == database,\n",
    "                                tbl.c.table == d.get('table'),\n",
    "                                tbl.c.field == d.get('field')\n",
    "                            )).values(d)\n",
    "                    result = conn_admin.execute(sql)\n",
    "                    print(d.get('table'), d.get('field'), result.rowcount, 'rows updated')\n",
    "                    result.close()\n",
    "                else:\n",
    "                    sql = insert(tbl).values(d)\n",
    "                    result = conn_admin.execute(sql)\n",
    "                    print(table, result.rowcount, 'rows inserted')\n",
    "                    result.close()\n",
    "        else:\n",
    "            result = conn_admin.execute(sql)\n",
    "            print(table, result.rowcount, 'rows deleted')\n",
    "            result.close()\n",
    "            sql = insert(tbl).values(_data.get(table))\n",
    "            result = conn_admin.execute(sql)\n",
    "            print(table, result.rowcount, 'rows inserted')\n",
    "            result.close()\n",
    "        conn_admin.commit()\n",
    "        conn_admin.close()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COMMIT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-04-10T14:22:04.514958Z",
     "iopub.status.busy": "2021-04-10T14:22:04.514958Z",
     "iopub.status.idle": "2021-04-10T14:22:04.522695Z",
     "shell.execute_reply": "2021-04-10T14:22:04.521416Z",
     "shell.execute_reply.started": "2021-04-10T14:22:04.514958Z"
    }
   },
   "outputs": [],
   "source": [
    "commit = True\n",
    "#session.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-10T15:37:44.939064Z",
     "start_time": "2020-09-10T15:37:42.766969Z"
    },
    "execution": {
     "iopub.execute_input": "2021-04-10T14:22:06.524948Z",
     "iopub.status.busy": "2021-04-10T14:22:06.524948Z",
     "iopub.status.idle": "2021-04-10T14:22:06.552418Z",
     "shell.execute_reply": "2021-04-10T14:22:06.550950Z",
     "shell.execute_reply.started": "2021-04-10T14:22:06.524948Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    if commit == True:\n",
    "        session.commit()\n",
    "except Exception as e:\n",
    "    print(str(e))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CONFIG INTERFACE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dash_conf = {\n",
    "    \"label\": \"dashboard\",\n",
    "    \"tooltip\": \"dashboard_desc\",\n",
    "    \"load_items\": {\n",
    "        \"table\": \"dashboard\",\n",
    "        \"tables\": [\"dashboard\"]\n",
    "    }\n",
    "}\n",
    "etl_conf = {\n",
    "    \"label\": \"etl_report_base\",\n",
    "    \"tooltip\": \"etl_report_base_desc\",\n",
    "    \"load_items\": {\n",
    "        \"table\": \"etl_report_base\",\n",
    "        \"tables\": [\"etl_report_base\"]\n",
    "    }\n",
    "}\n",
    "task_conf = {\n",
    "    \"label\": \"etl_report_base\",\n",
    "    \"tooltip\": \"etl_report_base_desc\",\n",
    "    \"load_items\": {\n",
    "        \"table\": \"etl_report_base\",\n",
    "        \"tables\": [\"etl_report_base\"]\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-09-10T15:37:58.148853Z",
     "start_time": "2020-09-10T15:37:58.141858Z"
    },
    "code_folding": [],
    "execution": {
     "iopub.execute_input": "2021-04-09T13:03:38.571597Z",
     "iopub.status.busy": "2021-04-09T13:03:38.571597Z",
     "iopub.status.idle": "2021-04-09T13:03:38.593119Z",
     "shell.execute_reply": "2021-04-09T13:03:38.592119Z",
     "shell.execute_reply.started": "2021-04-09T13:03:38.571597Z"
    }
   },
   "outputs": [],
   "source": [
    "interface_conf = {\n",
    "    'Dashboards': {\n",
    "        'menu_icon': 'document-report',\n",
    "        'menu_order': 0,\n",
    "        'active': True,\n",
    "        'menu_config': json.dumps(dash_conf),\n",
    "        'tables': [\n",
    "            'dashboard'\n",
    "        ]\n",
    "    },\n",
    "    'Admin': {\n",
    "        'menu_icon': 'web',\n",
    "        'menu_order': 1,\n",
    "        'active': True,\n",
    "        'tables': [\n",
    "            'app',\n",
    "            'menu',\n",
    "            'role',\n",
    "            'user',\n",
    "            'user_log',\n",
    "            'custom_table',\n",
    "            'custom_form',\n",
    "            'table',\n",
    "        ]\n",
    "    },\n",
    "    'Params':{\n",
    "        'menu_icon': 'settings',\n",
    "        'menu_order': 2,\n",
    "        'active': True,\n",
    "        'tables': [\n",
    "            'lang'\n",
    "        ]\n",
    "    },\n",
    "    'ETL / Report / Base':{\n",
    "        'menu_icon': 'circle-stack',\n",
    "        'menu_order': 3,\n",
    "        'active': True,\n",
    "        #'menu_config': json.dumps(etl_conf),\n",
    "        'tables': [\n",
    "            {'table': 'periodicity', 'active': False}, \n",
    "            'etl_report_base',\n",
    "            'manage_query',\n",
    "            {'table': 'input_type', 'active': False},\n",
    "            {'table': 'source_type', 'active': False},\n",
    "            {'table': 'etl_rbase_input', 'active': False},\n",
    "            {'table': 'output_type', 'active': False},\n",
    "            {'table': 'etl_rbase_output', 'active': False},\n",
    "            {'table': 'etl_rb_output_field', 'active': False}, \n",
    "            {'table': 'etl_rbase_quality', 'active': False},\n",
    "            {'table': 'etl_rb_reconcilia', 'active': False},\n",
    "            {'table': 'etl_rb_reconc_dtail', 'active': False}, \n",
    "            {'table': 'export_type', 'active': False},\n",
    "            {'table': 'etl_rbase_export', 'active': False},\n",
    "            {'table': 'etl_rb_exp_dtail', 'active': False}, \n",
    "            {'table': 'etl_report_base_log', 'active': False},\n",
    "            {'table': 'etl_rbase_notify', 'active': False},\n",
    "        ]\n",
    "    },\n",
    "    'Tasks':{\n",
    "        'menu_icon': 'calendar-days',\n",
    "        '_menu_icon': 'rectangle-stack',\n",
    "        'menu_order': 4,\n",
    "        'active': True,\n",
    "        # 'menu_config': json.dumps(task_conf),\n",
    "        'tables': [\n",
    "            {'table': 'calendar', 'active': False},\n",
    "            {'table': 'task_status', 'active': False},\n",
    "            {'table': 'repeat_type', 'active': False},\n",
    "            {'table': 'task', 'active': True}, \n",
    "            {'table': 'task_track', 'active': False}\n",
    "        ]\n",
    "    } \n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54482cb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def _run(interface_conf):\n",
    "    tbl_app = _Table('app', metadata, autoload_with = engine)\n",
    "    tbl_menu = _Table('menu', metadata, autoload_with = engine)\n",
    "    tbl_table = _Table('table', metadata, autoload_with = engine)\n",
    "    tbl_menu_table = _Table('menu_table', metadata, autoload_with = engine)\n",
    "    sql = select(tbl_app.c).select_from(tbl_app).where(tbl_app.c.db == database)\n",
    "    now = datetime.datetime.now()\n",
    "    with engine.connect() as conn_admin:\n",
    "        app = conn_admin.execute(sql).fetchone()\n",
    "        for m in interface_conf:\n",
    "            if interface_conf[m].get('active') == True:\n",
    "                sql = select(tbl_menu.c)\\\n",
    "                        .select_from(tbl_menu)\\\n",
    "                        .where(and_(\n",
    "                            tbl_menu.c.menu == m,\n",
    "                            tbl_menu.c.app_id == app.app_id\n",
    "                        ))\n",
    "                menu = conn_admin.execute(sql).fetchone()\n",
    "                # print(menu)\n",
    "                if menu:\n",
    "                    print(m, menu.menu_id)\n",
    "                else:\n",
    "                    data = {\n",
    "                        'menu':  m,\n",
    "                        'menu_desc': m,\n",
    "                        'menu_icon': interface_conf[m].get('menu_icon'),\n",
    "                        'active': interface_conf[m].get('active'),\n",
    "                        'menu_order': interface_conf[m].get('menu_order'),\n",
    "                        'menu_config': interface_conf[m].get('menu_config'),\n",
    "                        'app_id': app.app_id,\n",
    "                        'user_id': 1,\n",
    "                        'created_at': now,\n",
    "                        'updated_at': now,\n",
    "                        'excluded': False\n",
    "                    }\n",
    "                    sql2 = insert(tbl_menu).values(data)\n",
    "                    result = conn_admin.execute(sql2)\n",
    "                    result.close()\n",
    "                    menu = conn_admin.execute(sql).fetchone()  \n",
    "                    print(m, 'Adicionado: ', menu.menu_id)\n",
    "                # MENU TABELA\n",
    "                if interface_conf[m].get('tables'):\n",
    "                    for t in interface_conf[m].get('tables'):\n",
    "                        _active = True\n",
    "                        if type(t) == dict:\n",
    "                            _tbl = t.get('table')\n",
    "                            _active = t.get('active')\n",
    "                        else:\n",
    "                            _tbl = t\n",
    "                        sql = select(tbl_table.c)\\\n",
    "                                .select_from(tbl_table)\\\n",
    "                                .where(and_(\n",
    "                                    tbl_table.c.table == _tbl,\n",
    "                                    tbl_table.c.db == database\n",
    "                                ))\n",
    "                        table = conn_admin.execute(sql).fetchone()\n",
    "                        # print(_tbl, database, table)\n",
    "                        try:\n",
    "                            # print(_tbl, database, table, table)\n",
    "                            sql = select(tbl_menu_table.c)\\\n",
    "                                    .select_from(tbl_menu_table)\\\n",
    "                                    .where(and_(\n",
    "                                        tbl_menu_table.c.table_id == table.table_id,\n",
    "                                        tbl_menu_table.c.menu_id == menu.menu_id,\n",
    "                                        tbl_menu_table.c.app_id == app.app_id\n",
    "                                    ))\n",
    "                            menu_table = conn_admin.execute(sql).fetchone()\n",
    "                            if not menu_table:\n",
    "                                data = {\n",
    "                                    'menu_id': menu.menu_id,\n",
    "                                    'table_id': table.table_id,\n",
    "                                    'app_id': app.app_id,\n",
    "                                    'active': _active,\n",
    "                                    'user_id': 1,\n",
    "                                    'created_at': now,\n",
    "                                    'updated_at': now,\n",
    "                                    'excluded': False\n",
    "                                }\n",
    "                                sql2 = insert(tbl_menu_table).values(data)\n",
    "                                result = conn_admin.execute(sql2)\n",
    "                                result.close()\n",
    "                        except Exception as e:\n",
    "                            print(_tbl, table, str(e))\n",
    "        conn_admin.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59f83afb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#_run(interface_conf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.2 64-bit",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.9"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "1941572a340475f3146cdb73f2cc291ffceee1d403854ab220cc5a370cfec1e8"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}