#!/usr/bin/env python3
# This script creates a sqlite database with pathways data from files provided by NanoString.
# This might get replaced by flyway or a similar mechanism in the future, as we obtain all of the specifications related to the file format.
# Author: Ingmars Melkis <inngmars.melkis@swdfactory.com>
import csv
import logging
import argparse
import sqlite3
import os

# Bootstrapping
parser = argparse.ArgumentParser(description="Script that generates a pathways database from tsv (tab seperated value) files")
parser.add_argument("--pathway-file", default="pathways.tsv", type=str, help="A TSV file that contains the pathway ID, full name and species the pathway is applicable to.")
parser.add_argument("--relationship-file", default="pathway_relationships.tsv", type=str, help="A TSV file that contains pathway IDs in two columns: Parent, Child.")
parser.add_argument("---pathway-gene-file", default="pathway_genes.tsv", type=str, help="A TSV file that contains pathway names, IDs and genes they map to.")
parser.add_argument("--gene-file", default="genes.tsv", type=str, help="A TSV file that contains gene names and their IDs.")
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!")

# Script logic starts here
logging.log(55, "Script started.")

if os.path.isfile(args.db_name):
    logging.info("Existing database detected. Deleting it!")
    os.remove(args.db_name)

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

# Create the tables
logging.info("Creating tables")
db_cursor.execute('''CREATE TABLE Pathway (id text primary key, name text, species text)''')
db_cursor.execute('''CREATE TABLE PathwayRelationship (parent text, child text, FOREIGN KEY (parent) REFERENCES Pathway(id) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (child) REFERENCES Pathway(id) ON DELETE CASCADE ON UPDATE NO ACTION)''')
db_cursor.execute('''CREATE TABLE Gene (id integer primary key, symbol text, name text)''')
db_cursor.execute('''CREATE TABLE PathwayGene (pathwayId text, geneid integer, FOREIGN KEY (pathwayId) references Pathway(id) ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY(geneid) references Gene(id) ON DELETE CASCADE ON UPDATE NO ACTION)''')
logging.info("Tables created successfully")

logging.info("Creating indices")
db_cursor.execute('''CREATE UNIQUE INDEX pathwayid_pathwayid_idx ON Pathway(id)''')
db_cursor.execute('''CREATE UNIQUE INDEX gene_id_idx ON gene(id)''')
db_cursor.execute('''CREATE INDEX pathwaygene_pathwayid_idx ON PathwayGene(pathwayId)''')
db_cursor.execute('''CREATE INDEX pathwaygene_geneid_idx ON PathwayGene(geneid)''')
logging.info("Indices created successfully")

# Parse and insert the data
logging.info("Opening files and parsing data into the database")

with open(args.pathway_file) as pathway_file, open(args.relationship_file) as relationship_file, open(args.pathway_gene_file) as pathway_gene_file, open(args.gene_file) as gene_file:
    pathway_reader = csv.reader(pathway_file, delimiter='\t')
    relationship_reader = csv.reader(relationship_file, delimiter='\t')
    pathway_gene_reader = csv.reader(pathway_gene_file, delimiter='\t')
    gene_reader = csv.reader(gene_file, delimiter='\t')

    # Dump data
    logging.info("Parsing pathways")
    for row in pathway_reader:
        logging.debug(row)
        db_cursor.execute('INSERT INTO Pathway VALUES (?, ?, ?)', (row[0], row[1], row[2]))

    logging.info("Parsing relationships")
    for row in relationship_reader:
        logging.debug(row)
        db_cursor.execute('INSERT INTO PathwayRelationship VALUES (?, ?)', (row[0], row[1]))

    logging.info("Parsing genes. You might see a few warnings - it's safe to ignore them.")
    for row in gene_reader:
        logging.debug(row)
        # This file was generated by Peter, and might be changed later
        # Col 0 - HGNC ID
        # Col 1 - Approved symbol (name)
        # Col 2 - Approved name (long name)
        # Col 3 - NCBI Gene ID
        if row[3] is not '':
            db_cursor.execute('INSERT INTO Gene VALUES(?, ?, ?)', (row[3], row[1], row[2]))
        else:
            logging.warning("Gene not mapped to Entrez GeneID: %s (%s)", row[2], row[1])

    logging.info("Parsing gene to pathway mappings")
    for row in pathway_gene_reader:
        logging.debug(row)
        # Col 0 - Pathway ID
        # Col 1 - Pathway Name
        # The rest - Gene ID
        for col in range(2, len(row)):
            db_cursor.execute('INSERT INTO PathwayGene VALUES (?, ?)', (row[0], row[col]))

logging.info("Database created successfully!")

conn.commit()
conn.close()

logging.log(56, "Script finished!")