<?php
	$user = "root";
	$pass = "";
	try {
		// MAKE CONNECTION TO OLD DATABASE
	    $dbold = new PDO('mysql:host=localhost;dbname=old', $user, $pass);
	    // GET ALL USERS
	    $userQuery = $dbold->prepare('SELECT * FROM users');
	    $userQuery->execute();
	    $users = $userQuery->fetchAll(PDO::FETCH_OBJ);
	    // MAKE CONNECTION TO NEW DATABASE
	    $dbnew = new PDO('mysql:host=localhost;dbname=new', $user, $pass);
	} catch (PDOException $e) {
	    print "Error!: " . $e->getMessage() . "<br/>";
	    die();
	};
	foreach($users as $user) {
    	// MOVE TO NEW DATABASE TABLE ADDRESS
    	$insertAdress = $dbnew->prepare('INSERT INTO addresses (street, house_number, postcode) VALUES (:street, :house_number, :postcode)');
    	$insertAdress->bindParam(':street', $user->street);
		$insertAdress->bindParam(':house_number', $user->house_number);
		$insertAdress->bindParam(':postcode', $user->postcode);
		$insertAdress->execute();
		// GET UNIQUE ID
		$addressID = $dbnew->lastInsertId();

    	// MOVE TO NEW DATABASE TABLE PROFILE
    	$insertProfile = $dbnew->prepare('INSERT INTO profiles (first_name, last_name) VALUES (:first_name, :last_name)');
    	// SPLIT NAME INTO FIRST AND LAST NAME
    	$name  = explode(" ", $user->name);
		$insertProfile->bindParam(':first_name', $name[0]);
		$insertProfile->bindParam(':last_name', $name[1]);
		$insertProfile->execute();
		// GET UNIQUE ID
		$profileID = $dbnew->lastInsertId();

		// GET OLD ROLES FROM USERS AND MAKE INSERT INTO TABLE ROLES USING OLD ROLE NAMES
		$queryRole = $dbnew->prepare('SELECT COUNT(*) FROM roles WHERE name=:name');
		$queryRole->bindParam(":name", $user->role);
		$queryRole->execute();
		$countRole = $queryRole->fetchColumn();
		// IF ROLE ALREADY EXISTS IN TABLE ROLES SELECT THAT ROLE
		if ($countRole == 1) {
			$selectRole = $dbnew->prepare('SELECT * FROM roles where name=:name');
			$selectRole->bindParam(":name", $user->role);
			$selectRole->execute();
			$role = $selectRole->fetch(PDO::FETCH_OBJ);
			// GET UNIQUE ID
			$roleID = $role->id;
		// IF ROLE DOESN'T EXIST INSERT INTO TABLE
		} else {
			$insertRole = $dbnew->prepare('INSERT INTO roles (name) VALUES (:name)');
			$insertRole->bindParam(':name', $user->role);
			$insertRole->execute();
			// GET UNIQUE ID
			$roleID = $dbnew->lastInsertId();
		}

    	// TO NEW USER
        $insertUser = $dbnew->prepare('INSERT INTO users (email, Profile_id, Address_id, Role_id, password) VALUES (:email, :Profile_id, :Address_id, :Role_id, :password)');
		$insertUser->bindParam(':email', $user->email);
		$insertUser->bindParam(':password', $user->password);
		$insertUser->bindParam(':Profile_id', $profileID);
		$insertUser->bindParam(':Address_id', $addressID);
		$insertUser->bindParam(':Role_id', $roleID);
		$insertUser->execute();
		// GET UNIQUE ID
		$userID = $dbnew->lastInsertId();

		// BLOGS
		// GET ALL FROM OLD DATABASE
		$blogQuery = $dbold->prepare('SELECT * from blog WHERE Users_id=:User_id');
		$blogQuery->bindParam(':User_id', $userID);
	    $blogQuery->execute();
	    $blogs = $blogQuery->fetchAll(PDO::FETCH_OBJ);
	    var_dump($blogs);
		foreach ($blogs as $blog){
			echo "test";
			// MOVE TO NEW DATABASE TABLE BLOGS
			$insertBlogs = $dbnew->prepare('INSERT INTO blogs (title, content, User_id) VALUES (:title, :content, :User_id)');
			$insertBlogs->bindParam(':title', $blog->title);
			$insertBlogs->bindParam(':content', $blog->content);
			$insertBlogs->bindParam(':User_id', $userID);
			$insertBlogs->execute();
			// GET UNIQUE ID
			$blogID = $dbnew->lastInsertId();
			// MOVE TO NEW DATABASE TABLE COMMENTS
			// GET ALL FROM OLD DATABASE
			$commentQuery = $dbold->prepare('SELECT * from comment WHERE Blog_id=:Blog_id');
			$commentQuery->bindParam(':Blog_id', $blogID);
		    $commentQuery->execute();
		    $comments = $commentQuery->fetchAll(PDO::FETCH_OBJ);
			foreach ($comments as $comment) {
				$insertComments = $dbnew->prepare('INSERT INTO comments (text, Blog_id, User_id) VALUES (:text, :Blog_id, :User_id)');
				$insertComments->bindParam(':text', $comment->text);
				$insertComments->bindParam(':User_id', $userID);
				$insertComments->bindParam(':Blog_id', $blogID);
				$insertComments->execute();
			}
		}

		// FILES
		// GET ALL FROM OLD DATABASE
	    $fileQuery = $dbold->prepare('SELECT * from file WHERE uploaded_by=:name');
	    $fileQuery->bindParam(':name', $user->name);
	    $fileQuery->execute();
	    $files = $fileQuery->fetchAll(PDO::FETCH_OBJ);
		foreach ($files as $file){
			// TO NEW FILES
			$insertFiles = $dbnew->prepare('INSERT INTO files (filename, User_id) VALUES (:filename, :User_id)');
			$insertFiles->bindParam(':filename', $file->filename);
			$insertFiles->bindParam(':User_id', $userID);
			$insertFiles->execute();
		}
	}
?>