import { NodeWithI18n } from '@angular/compiler';
import { Injectable } from '@angular/core';
import { transpile } from 'typescript';
import { InputField } from '../classes';
import { DbConnectionService } from '../db-connection.service';

@Injectable({
  providedIn: 'root'
})
export class QueryService {

  /**
   *  Datasets, edit to show different data
   *  @param query (required) Query used to display data
   *    -> must contain primary key(s) to edit or delete data
   *
   *  @param PK Column name that holds the Primary Key(s)
   *    -> used for editing and deleting data
   *    -> single primary key stored as string e.g. "key"
   *    -> multiple primary keys stored as array e.g. ["key1", "key2"]
   *
   *  @param tableName Table name
   *    -> used for deleting data
   *
   *  @param form InputFields of the form
   *    @param InputField.Text
   *    @param InputField.Number
   *    @param InputField.Date
   *    @param InputField.Reference: Requires a query with 2 columns: the primary key and a string value that will be displayed in the dropdown field. The second field name should be equal to the field name of the first table!
   */
  // this is a comment
  datasets = {
    categories: {
      PK: "name",
      tableName: "Category",
      query: "SELECT * FROM Category",
      form: {
        name: InputField.Text,
      }
    },
    CategoryValues: {
      PK: ["name", "category"],
      tableName: "CategoryValue",
      query: "select * from CategoryValue",
      form: {
        category: InputField.Reference("select name, name as category from Category"),
        name: InputField.Text
      }
    },
    Places: {
      PK: "name",
      tableName: "Place",
      query: "select * from Place",
      form: {
        name: InputField.Text,
        mapNumber: InputField.Number
      }
    },
    MainActivityTypes: {
      PK: "name",
      tableName: "MainActivityType",
      query: "select name, recommendedNights, place, picture, status from MainActivityType",
      form: {
        name: InputField.Text,
        status: InputField.Reference("select name, name as status from Status"),
        description: InputField.Text,
        longDescription: InputField.Text,
        recommendedNights: InputField.Number,
        minNights: InputField.Number,
        maxNights: InputField.Number,
        place: InputField.Reference("select name, name as place from Place"),
        carDaysEqualsDays: InputField.Number,
        carDays: InputField.Number,
        picture: InputField.Image, 
      }
    },
    MainActivityTypePictures: {
      PK: "pictureID", 
      tableName: "MainActivityTypePicture",
      query: "select * from MainActivityTypePicture",
      form: {
        mainActivityType: InputField.Reference("select name, name as mainActivityType from MainActivityType"),
        picture: InputField.Image, 
      }
    },
    CategoryValueSelected: {
      PK: ["value", "mainActivityType"],
      tableName: "CategoryValueSelected",
      query: "select * from CategoryValueSelected",
      form: {
        mainActivityType: InputField.Reference("select name, name as mainActivityType from MainActivityType"),
        value: InputField.Reference("select name, name as value from CategoryValue")
      }
    },
    Transport: {
      PK: "name",
      tableName: "Transport",
      query: "select * from Transport",
      form: {
        name: InputField.Text,
        isCar: InputField.Number,
        pricePerDay: InputField.Number,
        pricePerDayPerPerson: InputField.Number,
      }
    },
    Transport_MainActivityType: {
      PK: "transport, mainActivityType",
      tableName: "Transport_MainActivityType",
      query: "select * from Transport_MainActivityType",
      form: {
        transport: InputField.Reference("select name, name as transport from Transport"),
        mainActivityType: InputField.Reference("select name, name as mainActivityType from MainActivityType")
      }
    },
    MainActivityPrices: {
      PK: ["mainActivityPriceID"],
      tableName: "MainActivityPrice",
      query: "select mainActivityType, priceFixed, pricePP, pricePD, pricePN, pricePPPD, pricePPPN, persons, nights, totalPrice description from MainActivityPrice",
      form: {
        mainActivityType: InputField.Reference("select name, name as mainActivityType from MainActivityType"),
        priceFixed: InputField.Number,
        pricePP: InputField.Number,
        pricePD: InputField.Number,
        pricePN: InputField.Number,
        pricePPPD: InputField.Number,
        pricePPPN: InputField.Number,
        persons: InputField.Number,
        nights: InputField.Number,
        totalPrice: InputField.Number,
        commissionPercentage: InputField.Number,
        description: InputField.Text
      }
    },
    DayActivityTypes: {
      PK: "name",
      tableName: "DayActivityType",
      query: "select name, status, description from DayActivityType",
      form: {
        name: InputField.Text,
        status: InputField.Reference("select name, name as status from Status"),
        description: InputField.Text,
        picture: InputField.Image,
        isDefault: InputField.Number
      }
    },
    DayActivityTypePictures: {
      PK: "pictureID", 
      tableName: "DayActivityTypePicture",
      query: "select * from DayActivityTypePicture",
      form: {
        dayActivityType: InputField.Reference("select name, name as dayActivityType from DayActivityType"),
        picture: InputField.Image, 
      }
    },
    MainActivityType_DayActivityType: {
      PK: ["mainActivityType", "dayActivityType"],
      tableName: `MainActivityType_DayActivityType`,
      query: "select * from MainActivityType_DayActivityType",
      form: {
        mainActivityType: InputField.Reference("select name, name as mainActivityType from MainActivityType "),
        dayActivityType: InputField.Reference("select name, name as dayActivityType from DayActivityType "),
      }

    },
    DayActivityPrices: {
      PK: "dayActivityPriceID",
      tableName: "DayActivityPrice",
      query: "select dayActivityType, priceFixed, pricePP, persons, totalPrice, commissionPercentage, description from DayActivityPrice",
      form: {
        dayActivityType: InputField.Reference("select name, name as dayActivityType from DayActivityType"),
        priceFixed: InputField.Number,
        pricePP: InputField.Number,
        persons: InputField.Number,
        totalPrice: InputField.Number,
        commissionPercentage: InputField.Number,
        description: InputField.Text
      }
    },
    Accomodations: {
      PK: "name",
      tableName: "Accomodation",
      query: "select name,status, pricePerNight, activity, place, level from Accomodation",
      form: {
        name: InputField.Text,
        status: InputField.Reference("select name, name as status from Status"),
        tagline: InputField.Text,
        description: InputField.Text,
        pricePerNight: InputField.Text,
        commissionPercentage: InputField.Number,
        level: InputField.Reference("select level as level1, level from AccomodationLevel"),
        stars: InputField.Number,
        activity: InputField.Reference("select name, name as activity from MainActivityType"),
        place: InputField.Reference("select name, name as place from Place"),
        picture: InputField.Image
      }
    },
    AccommodationPictures: {
      PK: "pictureID", 
      tableName: "AccommodationPicture",
      query: "select * from AccommodationPicture",
      form: {
        accommodation: InputField.Reference("select name, name as accommodation from Accomodation"),
        picture: InputField.Image, 
      }
    },
    Trips: {
      PK: "tripID",
      tableName: "Trip",
      query: "select tripID, startDate, endDate, persons, comment, arrivalPlace, transport from Trip",
      form: {
        customerID: InputField.Reference("select customerID, customerID as custID from Customer"),
        startDate: InputField.Date,
        endDate: InputField.Date,
        persons: InputField.Number,
        comment: InputField.Text,
        arrivalPlace: InputField.Reference("select name, name as arrivalPlace from Place"),
        firstAccomodation: InputField.Reference("select name, name as firstAccomodation from Accomodation"),
        departurePlace: InputField.Reference("select name, name as departurePlace from Place"),
        transport: InputField.Reference("select name, name as transport from Transport"),
        status: InputField.Text,
        commissionPercentage: InputField.Number,
      }
    },
    'Main Activities': {
      PK: "activityID",
      tableName: "MainActivity",
      query: "select * from MainActivity",
      form: {
        tripID: InputField.Reference("select tripID, tripID as trip from Trip"),
        mainActivityType: InputField.Reference("select name, name as mainActivityType from MainActivityType"),
        startDate: InputField.Date,
        nights: InputField.Number,
        persons: InputField.Number,
        accomodation: InputField.Reference("select name, name as accomodation from Accomodation"),
        transport: InputField.Reference("select name, name as transport from Transport")
        //totalPrice: InputField.Number
      }
    },
    'Day Activities': {
      PK: "dayActivityID",
      tableName: "DayActivity",
      query: "select * from DayActivity",
      form: {
        activityID: InputField.Reference("select activityID, concat(tripID, ' ' , mainActivityType) as activity from MainActivity"),
        dayActivityType: InputField.Reference("select name, name as dayActivityType from DayActivityType"),
        persons: InputField.Number,
        date: InputField.Date,
        //totalPrice: InputField.Number,
      }
    },

    MainActivityPricesView: {
      query: "select * from MainActivityAndAccomodationPricesView"
    },
    DayActivityPricesView: {
      query: "select * from DayActivityPricesView"
    },
    ItineraryEditView: {
      query: "select * from ItineraryEditView"
    },
    ShortOverview: {
      query: "select * from ShortOverview"
    },
    ItineraryOverview: {
      query: "SELECT * FROM ItineraryOverview"
    }
  }

  constructor(private db: DbConnectionService) { }

  /**
   * executes a delete query
   * @param PK Object {primary key: value}
   * @param datasetName table name
   * @param datasetID primary key column name
   * @returns promise with db response
   */
  deleteEntry(PK: object, datasetName: string) {
    return this.db.executeQuery(`DELETE FROM ${datasetName} WHERE ${Object.entries(PK).map(([k, v]) => `${k}="${v}"`).join(" AND ")}`)
  }

  /**
   * executes a select query
   * @param PK Object {primary key: value}
   * @param datasetName table name
   * @param datasetID primary key column name
   * @returns promise with requested data
   */
  getEntry(PK: object, datasetName: string) {
    return this.db.executeQuery(`SELECT * FROM ${datasetName} WHERE ${Object.entries(PK).map(([k, v]) => `${k}="${v}"`).join(" AND ")}`)
  }

  /**
   * executes an insert query
   * @param datasetName table name
   * @param fields object containing columnnames as keys and data as values
   * @returns promise with db response
   */
  createEntry(datasetName: string, fields: Object) {
    return this.db.executeQuery(`INSERT INTO ${datasetName} (${Object.keys(fields).join(", ")}) values (${Object.values(fields).map(x => `"${x}"`).join(", ")})`);
  }

  /**
   * executes an update query
   * @param datasetName table name
   * @param fields object containing columnnames as keys and data as values
   * @param datasetID primary key column name
   * @param PK Object {primary key: value}
   * @returns promise with db response
   */
  editEntry(datasetName: string, fields: Object, PK: object) {
    return this.db.executeQuery(`UPDATE ${datasetName} set ${Object.entries(fields).map(([k, v]) => `${k}='${v}'`).join(", ")} WHERE ${Object.entries(PK).map(([k, v]) => `${k}="${v}"`).join(" AND ")}`)
  }
}