package main

import (
	"database/sql"
	"fmt"
	"strings"
)

func (app *App) createTables() error {
	query1 := `CREATE TABLE IF NOT EXISTS shop(
		id INTEGER PRIMARY KEY AUTO_INCREMENT,
		name varchar(255) UNIQUE NOT NULL,
		url varchar(255) UNIQUE NOT NULL,
		short_url varchar(255) UNIQUE,
		logo_url text,
		shipping_costs int,
		free_shipping text
	)`
	_, err := app.DB.Exec(query1)
	if err != nil {
		return err
	}

	query2 := `CREATE TABLE IF NOT EXISTS angebot (
		id INTEGER PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(255) NOT NULL,
		abv DECIMAL(10,2) NOT NULL,
		volume DECIMAL(10,2) NOT NULL,
		age INT NOT NULL DEFAULT 0,
		shop INT NOT NULL,
		url VARCHAR(255) NOT NULL,
		short_url TEXT,
		original_price INT NOT NULL,
		discounted_price INT NOT NULL,
		base_price INT NOT NULL,
		image_url TEXT,
		spirit_type TEXT NOT NULL,
		website LONGTEXT,
		website_raw LONGTEXT NOT NULL,
		valid_until INT NOT NULL DEFAULT 0,
		created_at INT,
		FOREIGN KEY(shop) REFERENCES shop(id),
		UNIQUE(name,shop,volume,abv,original_price,discounted_price,valid_until),
		UNIQUE(name,shop,url,created_at)
	)`
	_, err = app.DB.Exec(query2)
	if err != nil {
		return err
	}

	views := []string{"whisky", "wodka", "rum", "gin", "misc", "all"}

	for _, v := range views {
		var spirit_type string
		if v == "misc" {
			spirit_type = `spirit_type NOT IN ("` + strings.Join(views, `", "`) + `") AND`
		} else if v == "all" {
			spirit_type = "1 = 1 AND "
		} else {
			spirit_type = `spirit_type = "` + v + `" AND`
		}

		view_query := `CREATE OR REPLACE VIEW ` + v + `_view AS
		SELECT
			angebot.id, angebot.name, age, angebot.abv, angebot.volume, angebot.url as long_url, angebot.short_url as url, spirit_type,
			original_price/100 as original_price, discounted_price/100 as discounted_price, angebot.base_price/100 as base_price, image_url,
			shop.name as shop, shop.id as shop_id, shop.short_url as shop_url, shop.shipping_costs/100 as shipping_costs, shop.free_shipping,
			ROUND(100-((discounted_price/original_price)*100)) AS procent, created_at
		FROM angebot
		JOIN shop ON angebot.shop = shop.id
		WHERE 
			` + spirit_type + ` (valid_until = 0 OR valid_until > (SELECT UNIX_TIMESTAMP()))`
		_, err = app.DB.Exec(view_query)
		if err != nil {
			return err
		}
	}

	view_query := `CREATE OR REPLACE VIEW _intern_view AS SELECT angebot.id, angebot.name, angebot.abv, angebot.volume, angebot.url as long_url, angebot.short_url as url, spirit_type, original_price, discounted_price, base_price, image_url, shop.name as shop, shop.id as shop_id, shop.short_url as shop_url, website_raw, created_at, valid_until
		FROM angebot
		JOIN shop ON angebot.shop = shop.id
		WHERE (valid_until = 0 OR valid_until > (SELECT UNIX_TIMESTAMP()))`
	_, err = app.DB.Exec(view_query)
	if err != nil {
		return err
	}

	return err
}

/**
 * Saves scrapped offers in database. Detects which offers are new.
 */
func (app *App) save_offer(W []Angebot) error {

	query := `INSERT INTO angebot (shop, name, url, abv, volume, age, original_price, discounted_price, base_price, valid_until, image_url, website_raw, spirit_type, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	stmt, err := app.DB.Prepare(query)
	if err != nil {
		Warn(err, "Save Offer: Preparing query failed")
		return err
	}
	defer stmt.Close()

	for _, o := range W {

		var found int

		if o.Discounted_price == 0 || o.Original_price == 0 {
			continue
		}

		// resembles UNIQUE constraint
		detect_duplicate_query := fmt.Sprintf(`SELECT 1 FROM _intern_view WHERE name = ? AND shop_id = %d AND volume = %4.2f AND abv = %4.2f AND original_price = %d AND discounted_price = %d AND valid_until = %d`, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until)
		err := app.DB.QueryRow(detect_duplicate_query, o.Name).Scan(&found)

		if err == sql.ErrNoRows {

			_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Age, o.Original_price, o.Discounted_price, o.Base_price, o.Valid_until, o.Image_url, o.Website, o.Spirit_type, app.Now)

			if err != nil {
				o.error_msg = err.Error()
				o.error_ctx = fmt.Sprintf(`INSERT INTO angebot (shop, name, url, abv, volume, age, original_price, discounted_price, base_price, valid_until, image_url, spirit_type, created_at) VALUES (%d, "%s", "%s", %f, %f, %d, %d, %d, %d, %d, "%s", "%s", %d)`, o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Age, o.Original_price,
					o.Discounted_price, o.Base_price, o.Valid_until, o.Image_url, o.Spirit_type, app.Now)
				o.Warn(fmt.Sprintf("Save Offer: Inserting offer failed (%d)", found))
			}

			o.Debug("database.go: Inserting offer.")

		} else if err != nil {
			o.error_msg = err.Error()
			o.error_ctx = fmt.Sprintf(strings.Replace(detect_duplicate_query, "?", `"%s"`, 1), o.Name)
			o.Warn("database.go: Duplicate query failed")
		} else {
			/*
			 * If everything went right we update the image url to reflect new changes.
			 */
			update_img_query := fmt.Sprintf(`UPDATE _intern_view SET image_url = ?, website_raw = ? WHERE name = ? AND shop_id = %d AND volume = %4.2f AND abv = %4.2f AND original_price = %d AND discounted_price = %d AND valid_until = %d`, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until)
			update_img_stmt, err := app.DB.Prepare(update_img_query)
			if err != nil {
				o.error_msg = err.Error()
				o.error_ctx = fmt.Sprintf(`UPDATE _intern_view SET image_url = %s, website_raw = %s WHERE name = %s AND shop_id = %d AND volume = %4.2f AND abv = %4.2f AND original_price = %d AND discounted_price = %d AND valid_until = %d`, o.Image_url, "redacted", o.Name, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until)
				o.Warn("database.go: Preparing update_img_query failed")
			}
			_, err = update_img_stmt.Exec(o.Image_url, o.Website, o.Name)
			if err != nil {
				o.error_msg = err.Error()
				o.error_ctx = fmt.Sprintf(`UPDATE _intern_view SET image_url = %s WHERE name = %s AND shop_id = %d AND volume = %4.2f AND abv = %4.2f AND original_price = %d AND discounted_price = %d AND valid_until = %d`, o.Image_url, o.Name, o.Shop, o.Volume, o.Abv, o.Original_price, o.Discounted_price, o.Valid_until)
				o.Warn("database.go: Executing update_img_query failed")
			}
		}
	}

	return nil
}

/**
 * Invalidates expired offers by updating 'valid_until' column. Detects which offers are expired.
 */
func (app *App) remove_expired(W []Angebot, shop Shop) error {

	query := `SELECT id, name, shop, volume, abv, original_price, discounted_price FROM angebot WHERE shop = ? AND created_at < ?
		AND (valid_until = 0 OR valid_until > ?)`

	rows, err := app.DB.Queryx(query, shop.Id, app.Now, app.Now)
	if err != nil {
		Warn(err, "Remove expired: Query failed")
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var offer_db Angebot
		err = rows.StructScan(&offer_db)

		if err != nil {
			Warn(err, "Remove expired: Struct scan failed")
			return err
		}

		if !app.offer_contains(W, offer_db) {
			offer_db.Debug("Contains not - Set to expire")
			expire_query := `UPDATE angebot SET valid_until = ? WHERE id = ?`
			_, err = app.DB.Exec(expire_query, app.Now, offer_db.Id)
			if err != nil {
				offer_db.error_msg = err.Error()
				offer_db.error_ctx = fmt.Sprintf("UPDATE angebot SET valid_until = %d WHERE id = %d", app.Now, offer_db.Id)
				offer_db.Warn("Remove expired: Update failed")
				return err
			}
		}
		offer_db.Debug("Contains! DOES NOT EXPIRE!")
	}

	return nil
}

func (app *App) offer_contains(W []Angebot, offer_db Angebot) bool {
	if len(W) < 1 {
		return false
	}

	for _, v := range W {
		// resembles UNIQUE constraint
		if (v.Name == offer_db.Name) && (v.Shop == offer_db.Shop) && (v.Volume == offer_db.Volume) && (v.Abv == offer_db.Abv) && (v.Original_price == offer_db.Original_price) && (v.Discounted_price == offer_db.Discounted_price) && (v.Valid_until == offer_db.Valid_until) {

			return true

		}
	}

	return false
}