import { Injectable } from '@angular/core';
import { StockTakingAllOutput } from '../entities/output/stockTakingAllOutput';
import { WarehouseAllOutput } from '../entities/output/warehouseAllOutput';
import { WarehouseItemAllOutput } from '../entities/output/warehouseItemAllOutput';

// tslint:disable-next-line:variable-name
const Sqlite = require('nativescript-sqlite');

@Injectable()
export class DatabaseService {

    database: any;

    constructor() {
        new Sqlite('db_stock_taking').then(database => {
            this.database = database;
            // this.deleteTables();
            this.createTables();
        });
    }

    deleteAllItems() {
        this.database.execSQL('DELETE FROM item').then(() => {

            console.log(' Items deleted successfully!');
        });
    }

    deleteTables() {
        this.database.execSQL('DROP TABLE item').then(() => {
            console.log('tabulka item vymazana.');
        });
        this.database.execSQL('DROP TABLE stock_taking').then(() => {
            console.log('tabulka stock_taking vymazana.');
        });
        this.database.execSQL('DROP TABLE warehouse').then(() => {
            console.log('tabulka warehouse vymazana.');
        });
    }

    insertInventory(inventory: StockTakingAllOutput) {
        return this.database.execSQL(`
        INSERT OR REPLACE INTO stock_taking (
                id,
                name,
                stock_taking_state,
                warehouses) VALUES (?,?,?,?)`,
            [
                inventory.ID,
                inventory.DisplayName,
                inventory.ID_StockTakingState,
                inventory.Warehouses])
            .then(() => {
                // console.log("inventura c." + inventory.ID + " byla uspesne vlozena.")
                }, error => {
                console.log('ERR-inventura c.' + inventory.ID + ' NEBYLA uspesne vlozena ' + error);
                });
    }

    insertWarehouse(warehouse: WarehouseAllOutput) {
        return this.database.execSQL(`
        INSERT OR REPLACE INTO warehouse (
                id,
                name) VALUES (?,?)`,
            [
                warehouse.ID,
                warehouse.DisplayName])
            .then(() => {
                // console.log("sklad " + warehouse.DisplayName + " byl uspesne vlozen.")
                }, error => {
                console.log('ERR-sklad ' + warehouse.DisplayName + ' NEBYL uspesne vlozen ' + error);
                });
    }

    insertItem(item: WarehouseItemAllOutput) {
        return this.database.execSQL(`
        INSERT INTO item (
            id,
            name,
            id_warehouse,
            in_warehouse,
            id_document,
            category,
            description,
            note,
            purchase_price,
            purchase_date,
            inventory_number,
            id_last_inventory,
            inventory_date,
            local_change,
            deletion_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [
            item.ID,
            item.DisplayName,
            item.ID_Warehouse,
            (item.InWarehouse ? 1 : 0),
            item.ID_Document,
            item.WarehouseItemCategory,
            item.Description,
            item.Note,
            item.PurchasePrice,
            item.PurchaseDate,
            item.InventoryNumber,
            item.idLastInventory,
            item.InventoryDate,
            (item.localChange ? 1 : 0),
            (item.DeletionDate ? item.DeletionDate : '')])
        .then(() => {
            // console.log("polozka s c." + item.ID + " - " + item.DisplayName + " byla uspesne vlozena.")
            }, error => {
            if (error.message.indexOf('(code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)') !== -1) {
                // console.log("ERR-polozka s c." + item.ID + " - " + item.DisplayName + " NEBYLA uspesne vlozena - bude update");
                    this.updateItem(item);
                } else {
                console.log('ERR-polozka s c.' + item.ID + ' - ' + item.DisplayName + ' NEBYLA uspesne vlozena ' + error.message);
                }

            });
    }

    updateItem(item: WarehouseItemAllOutput) {
        return this.database.execSQL(`
            UPDATE item
            SET
            name = ?,
            id_warehouse = ?,
            in_warehouse = ?,
            id_document = ?,
            category = ?,
            description = ?,
            note = ?,
            purchase_price = ?,
            purchase_date = ?,
            inventory_number = ?,
            id_last_inventory = ?,
            inventory_date = ?,
            deletion_date = ?
            WHERE id = ?`,
            [
                item.DisplayName,
                item.ID_Warehouse,
                (item.InWarehouse.toString() === 'true' ? 1 : 0),
                item.ID_Document,
                item.WarehouseItemCategory,
                item.Description,
            item.Note,
                item.PurchasePrice,
                item.PurchaseDate,
                item.InventoryNumber,
            item.idLastInventory,
            item.InventoryDate,
                (item.DeletionDate ? item.DeletionDate : ''),
            item.ID])
            .then(() => {
                // console.log("polozka s c." + item.ID + " - " + item.DisplayName + " byla uspesne upravena (vse az na local change")
                }, error => {
                console.log('ERR-polozka s c.' + item.ID + ' - ' + item.DisplayName + ' NEBYLA uspesne upravena (vse az na local change ' + error);
                });
    }

    updateItemLastInventoryId(item: WarehouseItemAllOutput) {
        return this.database.execSQL(`
            UPDATE item
            SET id_last_inventory = ?
            WHERE id = ?`,
            [
                item.idLastInventory ? item.idLastInventory : -1,
                item.ID])
            .then(() => {
                // console.log("polozka s c." + item.ID + " - " + item.DisplayName + " byla uspesne upravena (jeji last inventory id na " + item.idLastInventory + ").")
                }, error => {
                console.log('ERR-polozka s c.' + item.ID + ' - ' + item.DisplayName + ' NEBYLA uspesne upravena (jeji last inventory id) ' + error);
                });
    }

    updateItemLocal(item: WarehouseItemAllOutput) {
        return this.database.execSQL(`
        UPDATE item
        SET local_change = ?
        WHERE id = ?`,
        [
            (item.localChange === true ? 1 : 0),
            item.ID])
        .then(() => {
            // console.log("polozka s c." + item.ID + " - " + item.DisplayName + " byla uspesne upravena (jeji local only na " + item.localChange + ").")
            }, error => {
            console.log('ERR-polozka s c.' + item.ID + ' - ' + item.DisplayName + ' NEBYLA uspesne upravena (jeji local only) ' + error);
            });
    }

    loadItems(idWarehouse: number) {
        return this.database.all(`SELECT * FROM item WHERE id_warehouse = ${idWarehouse} AND deletion_date = ''`)
            .then(rows => {
                const result = [];

                return new Promise((resolve, reject) => {
                    rows.forEach(row => {
                        result.push(this.deserializeItemFromDB(row));
                    });
                    resolve(result);
                });
            }, error => {
                console.log('SELECT ERROR', error);
            });
    }

    deserializeItemFromDB(dbRow: any[]): WarehouseItemAllOutput {
        const item: WarehouseItemAllOutput = new WarehouseItemAllOutput();

        item.ID = dbRow[0];
        item.DisplayName = dbRow[1];
        item.ID_Warehouse = dbRow[2];
        item.InWarehouse = dbRow[3] === 1;
        item.ID_Document = dbRow[4];
        item.WarehouseItemCategory = dbRow[5];
        item.Description = dbRow[6];
        item.Note = dbRow[7];
        item.PurchasePrice = dbRow[8];
        item.PurchaseDate = dbRow[9];
        item.InventoryNumber = dbRow[10];
        item.idLastInventory = dbRow[11] === -1 ? null : dbRow[11];
        item.InventoryDate = dbRow[12];
        item.localChange = dbRow[13] === 1;

        return item;
    }

    loadStockTaking(warehouse: string) {
        return this.database.all(`SELECT * FROM stock_taking WHERE warehouses LIKE '%${warehouse}%' AND stock_taking_state = 'new'`)
            .then(rows => {
                const result = [];

                return new Promise((resolve, reject) => {
                    rows.forEach(row => {
                        // console.log(row);
                        result.push(this.deserializeStockTakingFromDB(row));
                    });
                    resolve(result);
                });
            }, error => {
                console.log('SELECT ERROR', error);
            });
    }

    deserializeStockTakingFromDB(dbRow: any[]): StockTakingAllOutput {
        const inventory: StockTakingAllOutput = new StockTakingAllOutput();

        inventory.ID = dbRow[0];
        inventory.DisplayName = dbRow[1];
        inventory.ID_StockTakingState = dbRow[2];
        inventory.Warehouses = dbRow[5];

        return inventory;
    }

    loadWarehouse(warehouseId: number) {
        return this.database.all(`SELECT * FROM warehouse WHERE id = ${warehouseId}`)
            .then(rows => {
                const result = [];

                return new Promise((resolve, reject) => {
                    rows.forEach(row => {
                        result.push(this.deserializeWarehouseFromDB(row));
                    });
                    resolve(result);
                });
            }, error => {
                console.log('SELECT ERROR', error);
            });
    }

    deserializeWarehouseFromDB(dbRow: any[]): WarehouseAllOutput {
        const warehouse: WarehouseAllOutput = new WarehouseAllOutput();

        warehouse.ID = dbRow[0];
        warehouse.DisplayName = dbRow[1];

        return warehouse;
    }

    /**
     * Method to prepare tables in database
     */
    private createTables(): void {
        this.database.execSQL(`
            CREATE TABLE IF NOT EXISTS stock_taking (
                id INTEGER PRIMARY KEY,
                name TEXT,
                stock_taking_state TEXT,
                warehouses TEXT)`).then(() => {
                // console.log("tabulka stock_taking USPESNE VYVTORENA.");
            },
            err => {
                console.log('pri vytvareni tabulky stock_taking doslo k chybe: ' + err);
            });

        this.database.execSQL(`
            CREATE TABLE IF NOT EXISTS warehouse (
                id INTEGER PRIMARY KEY,
                name TEXT)`).then(() => {
                // console.log("tabulka warehouse USPESNE VYVTORENA.");
            },
            error => {
                console.log('pri vytvareni tabulky warehouse doslo k chybe: ' + error);
            });

        this.database.execSQL(`
            CREATE TABLE IF NOT EXISTS item (
                id INTEGER PRIMARY KEY,
                name TEXT,
                id_warehouse INTEGER,
                in_warehouse INTEGER,
                id_document INTEGER,
                category TEXT,
                description TEXT,
                note TEXT,
                purchase_price INTEGER,
                purchase_date TEXT,
                inventory_number TEXT,
                id_last_inventory INTEGER,
                inventory_date TEXT,
                local_change INTEGER,
                deletion_date TEXT,
                FOREIGN KEY (id_warehouse) REFERENCES warehouse (id),
                FOREIGN KEY (id_last_inventory) REFERENCES stock_taking (id))`).then(() => {
                // console.log("tabulka item USPESNE VYVTORENA.");
            },
            error => {
                console.log('pri vytvareni tabulky item doslo k chybe: ' + error);
            });

    }
}
