from mm_stats.auth import PostgresDb
from mm_stats.definitions import logger


def create_table(path: str, schema: str = "data_preparation") -> None:
    """Create sessions table and import the data from csv."""
    logger.info("starting to upload sessions into pgsql")

    db = PostgresDb()

    # create temporary table
    sql_create_temp = f"""
    CREATE TABLE {schema}.sessions_temp(
        project_id integer,
        task_id integer,
        action varchar,
        userId bigint,
        startTime timestamp,
        endTime timestamp);
    """

    db.query(sql_create_temp)

    # import csv to temporary table
    sql_copy_temp = f"""
        COPY {schema}.sessions_temp
        FROM stdin WITH CSV HEADER
        DELIMITER as ',';
    """

    with open(path, "r") as fob:
        db.copy_expert(sql=sql_copy_temp, file=fob)
        logger.info("sessions imported into pgsql as temporary table")

    # drop existing and create new sessions table
    sql_insert = f"""
        DROP TABLE IF EXISTS {schema}.sessions CASCADE;
        CREATE TABLE {schema}.sessions AS
        SELECT
            project_id || '_' || task_id || '_' || userid || '_' || (extract(epoch from starttime) * 100000) as session_id  -- # noqa E501
            ,*
        FROM {schema}.sessions_temp;

        CREATE INDEX session_session_id_idx
            ON {schema}.sessions USING btree (session_id);
        CREATE INDEX session_project_id_idx
            ON {schema}.sessions USING btree (project_id);
        CREATE INDEX session_task_id_idx
            ON {schema}.sessions USING btree (task_id);
        CREATE INDEX session_user_id_idx
            ON {schema}.sessions USING btree (userId);
        CREATE INDEX session_startTime_idx
            ON {schema}.sessions USING btree (startTime);
        CREATE INDEX session_endTime_idx
            ON {schema}.sessions USING btree (endTime);
    """

    db.query(sql_insert)
    logger.info("sessions copied to final table, including indices")

    # drop temporary table
    sql_drop_temp = f"""
        DROP TABLE IF EXISTS {schema}.sessions_temp;
    """

    db.query(sql_drop_temp)
    logger.info("finalized session import to psql")