package database

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

	"github.com/op/go-logging"
)

var log = logging.MustGetLogger("mediasrv-server")

func OpenDatabase(data_dir string) (*sql.DB, bool, error) {
	// create data directory
	err := os.MkdirAll(data_dir, 0755)
	if err != nil {
		return nil, false, fmt.Errorf("Could not create data directory: %v", err)
	}

	// open database
	db_path := filepath.Join(data_dir, "server.db")

	db, err := sql.Open("sqlite3", db_path)
	if err != nil {
		return nil, false, fmt.Errorf("Could not open database: %v", err)
	}

	// check if the database is new
	is_db_new := `SELECT CASE WHEN EXISTS(SELECT 1 FROM sqlite_master) THEN 0 ELSE 1 END AS IsEmpty;`

	// initalize database
	db_init := `
CREATE TABLE tvshows(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    original_title TEXT,
    show_title TEXT,
    year INTEGER,
    description TEXT,
    poster TEXT
);
CREATE TABLE episodes(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	title TEXT,
	show_title TEXT,
    number INTEGER,
    season INTEGER,
    original_filename TEXT
);
`

	// transaction new check isn't racy
	tx, err := db.Begin()
	if err != nil {
		return nil, false, fmt.Errorf("Could not begin db transaction: %v", err)
	}
	defer tx.Commit()

	// check if the database is new
	rows, err := tx.Query(is_db_new)
	if err != nil {
		return nil, false, fmt.Errorf("Could not check if database was new: %v", err)
	}
	defer rows.Close()

	rows.Next()
	var isEmpty int
	rows.Scan(&isEmpty)

	// if the sqlite_master table is empty (the databse is newly created)
	if isEmpty == 1 {
		log.Infof("Creating new database at %v\n", db_path)
		// initalize database
		_, err = tx.Exec(db_init)
		if err != nil {
			return nil, false, fmt.Errorf("Could not initalize database: %v", err)
		}
		return db, true, nil
	} else {
		log.Infof("Existing database found at %v", db_path)
		return db, false, nil
	}

}