package main

func (app *App) createTables() error {
	var err error

	/**
	 * Copied from schema.sql
	 * TODO: Load this from the file itself.
	 */
	queries := []string{`
CREATE TABLE IF NOT EXISTS platform (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) UNIQUE NOT NULL,
	url VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;
	`,
		`
CREATE TABLE IF NOT EXISTS language (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;
	`,
		`
CREATE TABLE IF NOT EXISTS update_period (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) UNIQUE NOT NULL
) CHARSET=utf8;
	`,
		`
CREATE TABLE IF NOT EXISTS owner (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	url VARCHAR(255) NOT NULL UNIQUE,
	platform INT NOT NULL,
	CONSTRAINT fk_owner_platform FOREIGN KEY (platform) REFERENCES platform(id)
) CHARSET=utf8;
	`,
		`
CREATE TABLE IF NOT EXISTS entry (
	id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(255) NOT NULL,
	synopsis VARCHAR(255) NOT NULL,
	owner INT NOT NULL,
	platform INT NOT NULL,
	url VARCHAR(255) UNIQUE NOT NULL,
	language INT NOT NULL,
	stars VARCHAR(255) NOT NULL,
	update_period INT NOT NULL,
	created_at TIMESTAMP NOT NULL,
	CONSTRAINT fk_entry_owner FOREIGN KEY (owner) REFERENCES owner(id),
	CONSTRAINT fk_entry_platform FOREIGN KEY (platform) REFERENCES platform(id),
	CONSTRAINT fk_entry_language FOREIGN KEY (language) REFERENCES language(id),
	CONSTRAINT fk_entry_period FOREIGN KEY (update_period) REFERENCES update_period(id)
) CHARSET=utf8;
	`,
		`
		CREATE OR REPLACE VIEW entry_view AS
SELECT
        entry.id,
        title,
        synopsis,
        owner.name AS owner,
        platform.name AS platform,
        platform.url AS platform_url,
        CONCAT( platform.url, entry.url) AS url,
        language.name AS language,
        stars,
        created_at
FROM entry
JOIN owner ON
        entry.owner = owner.id
JOIN platform on
        entry.platform = platform.id
JOIN language
        ON entry.language = language.id
ORDER BY created_at DESC;
	`,
	}

	for _, query := range queries {
		_, err := app.DB.Exec(query)
		if err != nil {
			return err
		}
	}

	/**
	 * Populates database with first entries.
	 * TODO: Make this customizable.
	 */
	init_platform_query := `
		INSERT IGNORE INTO platform (id, name, url) VALUES (
			NULL,
			"Github",
			"https://github.com"
		);
	`
	_, err = app.DB.Exec(init_platform_query)
	if err != nil {
		return err
	}

	init_language_query := `
		INSERT IGNORE INTO language (name) VALUES (
			"Go"
		),(
			"PHP"
		), (
			"Javascript"
		), (
			"CSS"
		), (
			"HTML"
		), (
			"Java"
		), (
			"SQL"
		), (
			"Python"
		);
	`
	_, err = app.DB.Exec(init_language_query)
	if err != nil {
		return err
	}

	init_period_query := `
		INSERT IGNORE INTO update_period (name) VALUES (
			"Daily"
		),(
			"Weekly"
		), (
			"Monthly"
		);
	`
	_, err = app.DB.Exec(init_period_query)
	if err != nil {
		return err
	}

	return nil
}

func (app *App) SaveEntries(entries []Entry) error {
	query := `
		INSERT IGNORE INTO entry (
			id,
			title,
			synopsis,
			owner,
			platform,
			url,
			language,
			stars,
			update_period,
			created_at
		) VALUES (
			NULL,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?,
			?
		);
	`
	stmt, err := app.DB.Prepare(query)
	if err != nil {
		Warn(err, "SaveEntries: Preparing query failed")
		return err
	}
	defer stmt.Close()

	for _, e := range entries {

		err = app.SaveOwner(*e.Owner)
		if err != nil {
			continue
		}

		// Populates the owner struct with the correct id
		e.Owner, err = app.GetOwnerID(e.Owner)
		if err != nil {
			continue
		}
		_, err = stmt.Exec(e.Title, e.Synopsis, e.Owner.ID, e.Platform.ID, e.URL, e.Language.ID, e.Stars, e.UpdatePeriod.ID, app.Now)

		if err != nil {
			Warn(err, "SaveEntries: Statement execution failed")
			return err
		}

	}

	return nil
}

func (app *App) SaveOwner(owner Owner) error {
	query := `
		INSERT IGNORE owner (
			id,
			name,
			url,
			platform
		) VALUES (
			NULL,
			?,
			?,
			?
		);`

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

	_, err = stmt.Exec(owner.Name, owner.URL, owner.Platform.ID)
	if err != nil {
		Warn(err, "SaveOwner: Statement execution failed")
		return err
	}

	return nil
}