<?php

function db() {
	static $db = null;
	if ($db == null) {
		$db = new PDO('sqlite:' . __DIR__ . '/data/master.db');
		$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$db->exec('PRAGMA journal_mode=WAL');
	}
	return $db;
}

function db_init($db) {
	$db->exec('
			CREATE TABLE IF NOT EXISTS raw (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				secid TEXT,
				created_at INTEGER,
				updated_at INTEGER,
				deleted_at INTEGER,
				update_int INTEGER,
				data_type TEXT,
				raw_json TEXT
				);
			');
	$db->exec('CREATE INDEX IF NOT EXISTS idx_raw_secid_type ON raw (secid, data_type);');
	$db->exec('CREATE INDEX IF NOT EXISTS idx_raw_deleted ON raw (deleted_at);');
	$db->exec('CREATE INDEX IF NOT EXISTS idx_raw_updated_at ON raw (updated_at);');
	$db->exec('
			CREATE TABLE IF NOT EXISTS last (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				ticker TEXT,
				datetime TEXT,
				last REAL,
				UNIQUE(ticker, datetime)
				);
			');
	$db->exec('CREATE INDEX IF NOT EXISTS idx_last_ticker_datetime ON last (ticker, datetime);');
	$db->exec('
			CREATE TABLE IF NOT EXISTS signal (
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				ticker TEXT,
				datetime TEXT,
				signal TEXT,
				UNIQUE(ticker, datetime)
				);
			');
	$db->exec('CREATE INDEX IF NOT EXISTS idx_signal_ticker_datetime ON signal (ticker, datetime);');
}

function db_insert_or_update_raw($db, $now, $secid, $data_type, $json) {
	$stmt = $db->prepare("SELECT id FROM raw WHERE secid = ? AND data_type = ?");
	$stmt->execute(array($secid, $data_type));
	$row = $stmt->fetch();

	if ($row) {
		$stmt = $db->prepare('
				UPDATE raw SET
				raw_json = ?,
				updated_at = ?
				WHERE secid = ? AND data_type = ?
				');
		return $stmt->execute(array($json, $now, $secid, $data_type));
	} else {
		$stmt = $db->prepare('
				INSERT INTO raw (
					secid, created_at, updated_at, deleted_at,
					update_int, data_type, raw_json
					) VALUES (?, ?, ?, ?, ?, ?, ?)
				');
		return $stmt->execute(array(
					$secid, $now, $now, null, null, $data_type, $json
					));
	}
}

function db_insert_or_ignore_last($db, $now, $data) {
	$stmt = $db->prepare('
			INSERT OR IGNORE INTO last (
				ticker, datetime,
				last
				) VALUES (
				:ticker, :datetime,
				:last
				)
			');
	return $stmt->execute($data);
}

function db_insert_or_ignore_signal($db, $now, $signal) {
	$data = $signal;

	$stmt = $db->prepare('
			INSERT OR IGNORE INTO signal (
				ticker, datetime,
				signal
				) VALUES (
				:ticker, :datetime,
				:signal
				)
			');
	return $stmt->execute($data);
}

function fetch_api_json($url) {
	$json = false;
	for ($i = 1; $i <= 3; $i++) {
		$json = @file_get_contents($url);
		if ($json !== false) {
			sleep(1);
			break;
		}
		if ($i < 3) {
			sleep(2 * $i);
		}
	}
	return $json;
}