# [SublimeLinter @python:3]

import os
from itertools import zip_longest
import re
import win32console

from .base import Command, InvalidCommandArgs
from .sqlhelpers import sqlfmt, get_sql_name
from .arghelpers import get_separator, get_files, process_files
from ..utils import echo


class GenerateScriptCommand(Command):

    cli_command = "create-script"
    cli_command_aliases = ("create-upload-script", "crupsc", )
    cli_command_help = "create a SQL script and a bcp BAT file for data uploading"

    gui_order = 9
    gui_command = "Create Stript"
    gui_description = ("Create the 'CREATE TABLE' SQL script and a BAT file "
                       "for data uploading")
    gui_files_info_label_id = "LabelScriptFilesInfo"
    gui_info_message_widget = "MessageScriptInfo"

    gui_variables = ("separator", "script_regex_fname", "script_regex_table",
                     "script_header", "script_fast")
    gui_default = {"script_header": 1}

    gui_help_tooltips = {
        "CheckbuttonScriptWithQuotes": "Not supported by BULK INSERT",
    }

    ############################################################################

    @classmethod
    def run(cls, args):
        files = get_files(args)
        regex_fname = args.regex_fname
        regex_table = args.regex_table

        sep = bytes([get_separator(args)])
        headers = {}
        lengths = {}

        for file in files:
            file_name = os.path.basename(file)
            table_name = re.sub(regex_fname, regex_table, os.path.basename(file))

            if not table_name:
                raise ValueError("Cannot find table name: {}".format(file_name))

        base_dir = os.path.dirname(files[0])

        def round_length(x):
            if x > 1000:
                return 4000
            if x > 255:
                return 1000
            if x > 50:
                return 255
            return 50

        for file in process_files(args):
            file_size = os.path.getsize(file)
            progress = None

            with open(file, "rb") as fpi:
                fpi_tell = fpi.tell

                it = iter(fpi)
                lns = []

                if args.has_header:
                    header = [x.decode("cp1251") for x in next(it).rstrip(b"\r\n").split(sep)]
                    lns = [1] * len(header)
                else:
                    header = None

                if args.fast_scan:
                    it = (x for i, x in enumerate(it) if i < 1000)

                for i, line in enumerate(it):
                    new_lns = []

                    for x, s in zip_longest(lns, line.rstrip(b"\r\n").split(sep)):
                        if x is None:
                            x = 1

                        if s is None:
                            s = ""

                        new_lns.append(max(x, len(s)))

                    lns = new_lns

                    new_progess = 100 * fpi_tell() // file_size

                    if new_progess != progress:
                        echo("Progress: {}%".format(new_progess))
                        progress = new_progess

                if args.fast_scan:
                    lns = [round_length(x) for x in lns]

                headers[file] = header
                lengths[file] = lns

            if progress != 100:
                echo("Progress: 100%")

        query = []
        batch = ['set "SERVER=localhost"',
                 'set "DATABASE=my_database"']

        for file, header in headers.items():
            file_name = os.path.basename(file)
            table_name = re.sub(regex_fname, regex_table, os.path.basename(file))

            if not table_name:
                raise ValueError("Cannot find table name: {}".format(file_name))

            bcp_cmd = ('bcp "[%DATABASE%].dbo.{}" in "{}" -S "%SERVER%" '
                       '-T -m 0 -c -C 1251 -k').format(
                get_sql_name(table_name), file.replace('"', '""'))

            if sep != b"\t":
                bcp_cmd += ' -t "{}"'.format(sep.decode("ascii").replace('"', '""'))

            if args.has_header:
                bcp_cmd += " -F 2"

            batch.append(bcp_cmd)

            lns = lengths[file]

            if header is None:
                header = ["Column {}".format(i + 1) for i in range(len(lns))]

            sql = ["IF OBJECT_ID({table.name.str}) IS NOT NULL",
                   "    DROP TABLE {table.name};",
                   "GO", "",
                   "CREATE TABLE {table.name} ("]

            for i, (col_name, col_len) in enumerate(zip(header, lns)):
                sql.append(sqlfmt("   " + ("," if i else " ") \
                    + "{.name} nvarchar({.int})", col_name, col_len))

            sql.append(");")
            sql.append("GO")
            sql.append("")

            query.append(sqlfmt("\n".join(sql), table=table_name))

        query_file = os.path.join(base_dir, "dufi_create_tables.sql")

        with open(query_file, "w") as fp:
            fp.write("\n".join(query))

        batch_file = os.path.join(base_dir, "dufi_upload.bat")

        cp = win32console.GetConsoleOutputCP()

        with open(batch_file, "w", encoding="cp{}".format(cp)) as fp:
            fp.write("\n".join(batch))

        echo("Files `{}` and `{}` saved into `{}`".format(
            os.path.basename(query_file),
            os.path.basename(batch_file),
            base_dir))

    ############################################################################

    @classmethod
    def _add_arguments(cls, parser):
        cls._add_csv_arguments(parser, qualifier=False)
        parser.add_argument(
            "-a", "--regex-fname",
            default=r"(.*)\.csv",
            metavar="MASK",
            help="regex mask for file name parsing (default: '(.*)\\.csv')",
        )
        parser.add_argument(
            "-b", "--regex-table",
            default=r"\1",
            metavar="MASK",
            help="table name group number in `--regex-fname` (default: '\\1')",
        )
        parser.add_argument(
            "-e", "--has-header",
            action="store_true",
            help="files has headers",
        )
        parser.add_argument(
            "-f", "--fast-scan",
            action="store_true",
            help=("scan only the first 1000 rows (all lengths will be round up "
                  "with 50, 255, 1000, 4000)"),
        )
        cls._add_files_arguments(parser)

    ############################################################################

    @classmethod
    def _validate_cmd_args(cls, var):
        cls._validate_files(var)
        cls._validate_separator(var)

        if not var.script_regex_fname.strip() and not var.script_regex_table.strip():
            raise InvalidCommandArgs("Regex masks must be specified!")

    ############################################################################

    @classmethod
    def _get_cmd_args(cls, var):
        args = []
        args.extend(cls._get_cmd_args_separator(var))

        script_regex_fname = var.script_regex_fname.strip()

        if script_regex_fname:
            args.extend(["--regex-fname", script_regex_fname])

        script_regex_table = var.script_regex_table.strip()

        if script_regex_table:
            args.extend(["--regex-table", script_regex_table])

        if var.script_header:
            args.append("--has-header")

        if var.script_fast:
            args.append("--fast-scan")

        return args
