<?php

class DB {

	private $conn;

	public function __construct () {
		include 'connexion.php';
		try {
			$this->conn = new PDO('pgsql:host=localhost;port=5432;dbname=apisub', $db_user, $db_pass);
		} catch (PDOException $e) {
			die('Connection failed: ' . $e->getMessage());
		}
	}

	public function subList($utclogin) {
		$sql = 'SELECT *
						FROM vsubscription
						WHERE	utclogin=:utclogin';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function apiList($semester, $year) {
		$sql = 'SELECT *
						FROM vapi
						WHERE semester=:semester AND year=:year';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':semester',$semester,PDO::PARAM_STR);
		$st->bindValue(':year',$year,PDO::PARAM_INT);
		$st->execute();
		$res = $st->fetchAll(PDO::FETCH_ASSOC);
		return $res;
	}

	public function subToApi($utclogin, $api) {
		$today = date('Ymd');
		$sql = 'INSERT INTO subscribe(utclogin, api, subdate) VALUES (:utclogin, :api, :today)';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$st->bindValue(':today',$today,PDO::PARAM_STR);
		$res = $st->execute();
		return $res;
	}

	public function unsubToApi($utclogin, $api) {
		$sql = 'DELETE FROM subscribe WHERE utclogin=:utclogin AND api=:api';
		$st = $this->conn->prepare($sql);
		$st->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st->bindValue(':api',$api,PDO::PARAM_INT);
		$res = $st->execute();
		return $res;
	}

	public function copyUser($utclogin, $surname, $firstname) {
		// Function used to create a local copy of surname and firstname of each user, in order to link to DFP files without utclogin
		$sql = 'SELECT utclogin FROM localuser WHERE utclogin=:utclogin';
		$st1 = $this->conn->prepare($sql);
		$st1->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
		$st1->execute();
		if (!$st1->fetch(PDO::FETCH_ASSOC)) {			
			// If user has never logged in yet, he is added to local copy
			$sql = 'INSERT INTO localuser(utclogin, firstname, surname) VALUES (:utclogin, :firstname, :surname)';
			$st2 = $this->conn->prepare($sql);
			$st2->bindValue(':utclogin',$utclogin,PDO::PARAM_STR);
			$st2->bindValue(':firstname',$firstname,PDO::PARAM_STR);
			$st2->bindValue(':surname',$surname,PDO::PARAM_STR);
			$res = $st2->execute();
			return $res;
		}
		else {
			return 0;
		};

	}

}