package main

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

func (app *App) createTables() error {
	query1 := `CREATE TABLE IF NOT EXISTS shop(
		id INTEGER PRIMARY KEY AUTO_INCREMENT,
		name varchar(255) UNIQUE,
		url varchar(255) UNIQUE,
		short_url TEXT,
		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,
		shop int,
		name TEXT,
		url TEXT,
		short_url TEXT,
		abv DECIMAL(10,2),
		volume DECIMAL(10,2),
		original_price INT,
		discounted_price INT,
		base_price INT,
		image_url TEXT,
		spirit_type TEXT,
		website TEXT,
		website_raw TEXT,
		valid_until INT DEFAULT NULL,
		created_at INT,
		FOREIGN KEY(shop) REFERENCES shop(id)
	)`
	_, 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, angebot.abv, angebot.volume, angebot.url as long_url, angebot.short_url as url,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.short_url as shop_url, shop.shipping_costs/100 as shipping_costs, shop.free_shipping, ROUND(100-((discounted_price/original_price)*100)) AS procent, spirit_type, created_at
		FROM angebot
		JOIN shop ON angebot.shop = shop.id
		WHERE 
			` + spirit_type + ` (valid_until IS NULL OR valid_until > (SELECT UNIX_TIMESTAMP()))`
		_, err = app.DB.Exec(view_query)
		if err != nil {
			return err
		}
	}

	return err
}

func (app *App) save_offer(W []Angebot) error {

	query := `INSERT INTO angebot (shop, name, url, abv, volume, 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 {
		return err
	}
	defer stmt.Close()

	for _, o := range W {

		var found int

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

		err := app.DB.QueryRow("SELECT 1 FROM angebot WHERE shop = ? AND name = ? AND url = ? AND original_price = ? AND discounted_price = ? AND spirit_type = ?", o.Shop, o.Name, o.Url, o.Original_price, o.Discounted_price, o.Spirit_type).Scan(&found)

		/*
		 */

		if err == sql.ErrNoRows {

			if 0 == o.Valid_until {
				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, o.Original_price, o.Discounted_price, o.Base_price, sql.NullInt64{}, o.Image_url, o.Website, o.Spirit_type, app.Now)
			} else {
				_, err = stmt.Exec(o.Shop, o.Name, o.Url, o.Abv, o.Volume, 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 {
				return err
			}

		}
	}

	//return app.remove_expired(W)
	return nil
}

func (app *App) remove_expired(W []Angebot, shop_id int) error {

	query := `SELECT id, shop, name, url, original_price, discounted_price FROM all_view WHERE shop = ? AND created_at < ?`

	rows, err := app.DB.Queryx(query, shop_id, app.Now)
	if err != nil {
		return err
	}
	defer rows.Close()

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

		if err != nil {
			return err
		}

		if !app.offer_contains(W, offer_db) {
			expire_query := `UPDATE angebot SET valid_until = ? WHERE id = ?`
			_, err = app.DB.Exec(expire_query, app.Now, offer_db.Id)
			if err != nil {
				return err
			}
		}
	}

	return nil
}

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

	for _, v := range W {
		if (v.Shop == offer_db.Shop) && (v.Name == offer_db.Name) && (v.Original_price == offer_db.Original_price) && (v.Discounted_price == offer_db.Discounted_price) {

			if app.Config.Debug {
				log.Println("Contains: " + v.Name)
				log.Println("")
			}

			return true

		}
	}

	if app.Config.Debug {
		log.Println("Contains not: " + offer_db.Name)
		log.Println("")
	}

	return false
}