import math
import psycopg

import click
from flask import current_app, g


def get_db():
    """Returns database connection
    
    sqlite3: https://docs.python.org/3/library/sqlite3.html
    """
    if 'db' not in g:
        # g.db = sqlite3.connect(
        #     current_app.config['DATABASE'],
        #     detect_types=sqlite3.PARSE_DECLTYPES
        # )
        # # Return rows that behave like dicts
        # g.db.row_factory = sqlite3.Row

        g.db = psycopg.connect(
            "dbname=ustayml user=mitsuo",
            row_factory=psycopg.rows.dict_row
        )

    return g.db


def close_db(e=None):
    db = g.pop('db', None)

    if db:
        db.close()

# CLI:
# https://flask.palletsprojects.com/en/3.0.x/cli/

def init_fs():
    """Init file system directories"""
    import os
    dirs = [
        current_app.config['DATASET_PATH'],
        current_app.config['STUDENT_DATA_PATH']
    ]
    for d in dirs:
        os.makedirs(d, exist_ok=True)

def init_db():
    db = get_db()

    with current_app.open_resource('schema.sql') as f:
        db.execute(f.read().decode('utf8'))


@click.command('init-db')
def init_db_command():
    """Clear the existing data and create new tables."""
    init_fs()
    # init_db()
    # click.echo('Initialized the database.')

# Register function with application

def init_app(app):
    app.teardown_appcontext(close_db)       # callback after returning response
    app.cli.add_command(init_db_command)


# Helper functions

def get_paginated_rows(query: str, params: list=[], pagination: dict={}):
    limit = pagination.get('pagesize', 100)
    offset = pagination.get('page', 0) * limit
    with get_db().cursor() as cursor:
        cursor.execute(f"{query} LIMIT %s OFFSET %s;", params + [limit, offset])
        pagination['n_pages'] = math.ceil(cursor.rowcount / limit)
        pagination['rowcount'] = cursor.rowcount
        rows = cursor.fetchall()
    return rows

def get_row(query: str, params: list=[]):
    with get_db().cursor() as cursor:
        cursor.execute(query, params)
        row = cursor.fetchone()
    return row