#!/usr/bin/env python3
# This script cleans up a pathways database generated by create_db.py
# Author: Ingmars Melkis <ingmars.melkis@swdfactory.com>
import sqlite3
import argparse
import logging
import os
import sys

# Bootstrapping
parser = argparse.ArgumentParser(description="Script that cleans up a pathways database")
parser.add_argument("--db-name", default="pathways.db", type=str, help="Name of the exported database file.")
parser.add_argument("--loglevel", default="INFO", type=str, help="Logging output level (INFO / WARNING / DEBUG).")
args = parser.parse_args()

logging.basicConfig(level=logging.getLevelName(args.loglevel), format='%(asctime)s - %(levelname)s - %(message)s', datefmt='%d-%b-%y %H:%M:%S')
logging.addLevelName(55, "Hello!")
logging.addLevelName(56, "Goodbye!")

logging.log(55, "Script started.")

if not os.path.isfile(args.db_name):
    sys.exit("Could not find the database file");

conn = sqlite3.connect(args.db_name)
db_cursor = conn.cursor()

# Remove genes without pathways
#SELECT * FROM gene g LEFT JOIN PathwayGene pn ON pn.geneid = g.id WHERE pn.geneid IS NULL
logging.info("Deleting genes without pathways")
db_cursor.execute('''DELETE FROM Gene Where id in (SELECT g.id FROM gene g LEFT JOIN PathwayGene pn ON pn.geneid = g.id WHERE pn.geneid IS NULL)''')

conn.commit()
conn.close()

logging.log(56, "Script finished")