# Copyright (C) 2015 The Meme Factory, Inc.  http://www.meme.com/
#
#    This file is part of Gombe-MI.
#
#    Gombe-MI is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 3 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with Gombe-MI.  If not, see <http://www.gnu.org/licenses/>.
#
# Upload file into arbitrary relation in db.
#
# Karl O. Pinc <kop@meme.com>
#
# Bugs:
#  All data is presented to the db as a string, which could result
# in problems with type coercion.

# Write python 3 compatible code.
from __future__ import print_function
from __future__ import unicode_literals
from __future__ import absolute_import
from __future__ import division

from pyramid.view import view_config
from pyramid.renderers import render_to_response
import logging
import markupsafe
import psycopg2.errorcodes
from psycopg2 import ProgrammingError

from gmi_pyramid.lib.upload import (
    UploadEngine
    , Error
    , DataLineError
    , textualize
    , DataLineProcessor
    , UploadDoubleTableForm
    , TabularFileUploadHandler
    , UploadData
    , doublequote
    , escape_eol
    , is_checked
    )

log = logging.getLogger(__name__)

class NoTableError(Error):
    '''No table uploaded'''
    def __init__(self, e, descr='', detail=''):
        super(NoTableError, self).__init__(e, descr, detail)

class BadTableError(Error):
    '''Supplied name does not work for a table or view'''
    def __init__(self, e, descr='', detail=''):
        super(BadTableError, self).__init__(e, descr, detail)

class MissingTableError(BadTableError):
    '''The supplied table or view does not exist'''
    def __init__(self, e, descr='', detail=''):
        super(MissingTableError, self).__init__(e, descr, detail)

class MissingSchemaError(BadTableError):
    '''The schema portion of the supplied table or view does not exist'''
    def __init__(self, e, descr='', detail=''):
        super(MissingSchemaError, self).__init__(e, descr, detail)

class CannotInsertError(BadTableError):
    '''Cannot insert into the supplied table or view'''
    def __init__(self, e, descr='', detail=''):
        super(CannotInsertError, self).__init__(e, descr, detail)

class BadHeadersError(Error):
    '''The headers in the uploaded file are bad.'''
    def __init__(self, e, descr='', detail=''):
        super(BadHeadersError, self).__init__(e, descr, detail)


class SaveLine(DataLineProcessor):
    def __init__(self, ue, uh, insert_stmt):
        '''
        ue             UploadEngine instance
        uh             UploadHandler instance
        insert_stmt    Statement used to insert into db.
                       (psycopg2 formatted for substituion)
        '''
        super(SaveLine, self).__init__(ue, uh)
        self.insert_stmt = insert_stmt

    def eat(self, udl):
        '''
        Upload a line of data into the db.

        udl  An UploadDataLine instance
        '''
        self.cur.execute(self.insert_stmt, udl.tuples)


class TableUploadHandler(TabularFileUploadHandler):
    '''
    Attributes:
      request       A pyramid request instance
      uf            A GCUploadForm instance
      session       A pyramid session instance
      ue
      cur
    '''

    def make_form(self):
        '''
        Make the upload form needed by this handler.
        '''
        return UploadDoubleTableForm(self)

    def get_data(self):
        '''
        Return an UploadData instance, with flags set as desired.
        '''
        uf = self.uf
        self.data = UploadData(uf['localfh'],
                               uf['upload_fmt'],
                               uf['upload_null'],
                               uf['null_rep'],
                               trim=uf['trim_upload'])

    def val_input(self):
        '''
        Validate input needed beyond that required to connect to the db.

        Returns:
          A list of Error instances
        '''
        uf = self.uf
        errors = super(TableUploadHandler, self).val_input()

        qualified_table = uf['table']
        if qualified_table == '':
            errors.append(NoTableError('No table or view name supplied'))

        self.double_validator(errors)
        
        return errors

    def write(self, result, errors):
        '''Add double upload key into form.'''
        response = super(TableUploadHandler, self).write(result, errors)
        self.write_double_key(response)
        return response

    def resolve_table(self, qualified_table):
        '''Return (schema, table) tuple of table name, or raise exception
        if not resolvable.
        '''
        try:
            self.cur.execute(
                ('SELECT nspname, relname'
                 '  FROM pg_class'
                 '       JOIN pg_namespace'
                 '            ON (pg_namespace.oid = pg_class.relnamespace)'
                 '  WHERE pg_class.oid = %s::REGCLASS::OID'),
                (qualified_table,))
        except ProgrammingError as err:
            pgcode = err.pgcode
            if pgcode == psycopg2.errorcodes.INVALID_SCHEMA_NAME:
                raise MissingSchemaError(
                    'No such schema',
                    err.diag.message_primary,)
            elif pgcode == psycopg2.errorcodes.UNDEFINED_TABLE:
                raise MissingTableError(
                    'No such table or view',
                    err.diag.message_primary,
                    ('<p>Hint: Check spelling or try qualifying the'
                     ' table name with a schema name</p>'))
            else:
                raise
        return self.cur.fetchone()

    def good_table(self, schema, table):
        '''Is the supplied table or view insertable?
        '''
        sql = ('SELECT 1 FROM information_schema.tables'
               '  WHERE tables.table_name = %s'
               '        AND tables.table_schema = %s'
               "        AND (tables.is_insertable_into = 'YES'"
               # Unfortunatly, as of 9.2, the information_schema
               # tables.is_insertable_into does not reflect whether
               # there's an insert trigger on the table.
               "             OR tables.table_type = 'VIEW')")
        self.cur.execute(sql ,(table, schema))
        return self.cur.fetchone() != None

    def factory(self, ue):
        '''Make a db loader function from an UploadEngine.

        Input:

        Side Effects:
        Yes, lots.
        '''

        self.ue = ue
        self.cur = ue.cur
        data = ue.data
        qualified_table = self.uf['table']

        schema, table = self.resolve_table(qualified_table)

        if not self.good_table(schema, table):
            raise CannotInsertError(
                'Cannot insert into supplied table or view',
                ('({0}) is either is a view'
                 ' that cannot be inserted into'
                 ' or you do not have the necessary'
                 ' permissions to the table or view').format(
                    markupsafe.escape(qualified_table)))

        column_sql = ('SELECT 1 FROM information_schema.columns'
                      '  WHERE columns.table_name = %s'
                      '        AND columns.table_schema = %s'
                      '        AND columns.column_name = %s')
        insert_stmt = 'INSERT INTO {0} ('.format(doublequote(qualified_table))
        value_string = ''
        col_sep = ''
        bad_cols = []
        for col_name in data.headers.tuples:
            # Check that colum name exists
            self.cur.execute(column_sql, (table, schema, col_name))
            if self.cur.fetchone() == None:
                bad_cols.append(col_name)
            else:
                # Add column to sql statement
                insert_stmt += col_sep + col_name
                value_string += col_sep + '%s'
                col_sep = ', '
                
        if bad_cols:
            detail = (('<p>The following columns are not in the ({0})'
                       ' table or you do not have permission to access'
                       ' them:</p><ul>')
                      .format(markupsafe.escape(qualified_table)))
            for bad_col in bad_cols:
                detail += '<li>{0}</li>'.format(markupsafe.escape(bad_col))
            detail += '</ul>'
            raise BadHeadersError(
                'Header line contains unknown column names',
                detail=detail)

        insert_stmt += ') VALUES({0})'.format(value_string)

        return SaveLine(ue, self, insert_stmt)


@view_config(route_name='upload'
             , renderer='gmi_pyramid:templates/upload.mak')
def upload_view(request):

    response = UploadEngine(TableUploadHandler(request)).run()
    if response['db_changed']:
        if is_checked(response['csv_checked']):
            upload_fmt = 'CSV'
        else:
            upload_fmt = 'TAB'
        log.info('Successful upload: File ({filename}): Lines {lines}:'
                 ' Format {format}: Upload Null {null}: Null Rep ({null_rep}):'
                 ' Trim {trim}: DB {db}: Table ({table}): By user {user}'
                 .format(filename=response['filename'],
                         lines=response['lines'],
                         format=upload_fmt,
                         null=is_checked(response['upload_null']),
                         null_rep=escape_eol(response['null_rep']),
                         trim=is_checked(response['trim_upload']),
                         db=response['db'],
                         table=response['table'],
                         user=response['user']))
    return response