<?php

namespace RedUNIT\Mysql;

use RedUNIT\Mysql as Mysql;
use RedBeanPHP\Facade as R;

/**
 * Foreignkeys
 *
 * Tests creation and validity of foreign keys,
 * foreign key constraints and indexes in Mysql/MariaDB.
 * Also tests whether the correct contraint action has been selected.
 *
 * @file    RedUNIT/Mysql/Foreignkeys.php
 * @desc    Tests creation of foreign keys.
 * @author  Gabor de Mooij and the RedBeanPHP Community
 * @license New BSD/GPLv2
 *
 * (c) G.J.G.T. (Gabor) de Mooij and the RedBeanPHP Community.
 * This source file is subject to the New BSD/GPLv2 License that is bundled
 * with this source code in the file license.txt.
 */
class Foreignkeys extends Mysql
{
	/**
	 * Test whether we can use foreign keys with keywords.
	 *
	 * @return void
	 */
	public function testKWConflicts()
	{
		R::nuke();
		$metrics = R::dispense( 'metrics' );
		$constraint = R::dispense( 'constraint' );
		$constraint->xownMetrics[] = $metrics;
		R::store( $constraint );
		asrt( 1, R::count( 'metrics' ) );
		R::trash($constraint);
		asrt( 0, R::count( 'metrics') );
	}

	/**
	 * Basic FK tests.
	 *
	 * @return void
	 */
	public function testFKS()
	{
		$book  = R::dispense( 'book' );
		$page  = R::dispense( 'page' );
		$cover = R::dispense( 'cover' );

		list( $g1, $g2 ) = R::dispense( 'genre', 2 );

		$g1->name = '1';
		$g2->name = '2';

		$book->ownPage = array( $page );

		$book->cover = $cover;

		$book->sharedGenre = array( $g1, $g2 );

		R::store( $book );

		$fkbook  = R::getAll( 'describe book' );
		$fkgenre = R::getAll( 'describe book_genre' );
		$fkpage  = R::getAll( 'describe cover' );

		$j = json_encode( R::getAll( 'SELECT
		ke.referenced_table_name parent,
		ke.table_name child,
		ke.constraint_name
		FROM
		information_schema.KEY_COLUMN_USAGE ke
		WHERE
		ke.referenced_table_name IS NOT NULL
		AND ke.CONSTRAINT_SCHEMA="oodb"
		ORDER BY
		constraint_name;' ) );

		$json = '[
			{
				"parent": "genre",
				"child": "book_genre",
				"constraint_name": "c_fk_book_genre_genre_id"
			},
			{
				"parent": "book",
				"child": "book_genre",
				"constraint_name": "c_fk_book_genre_book_id"
			},
			{
				"parent": "cover",
				"child": "book",
				"constraint_name": "c_fk_book_cover_id"
			},
			{
				"parent": "book",
				"child": "page",
				"constraint_name": "c_fk_page_book_id"
			}
		]';

		$j1 = json_decode( $j, TRUE );

		$j2 = json_decode( $json, TRUE );

		foreach ( $j1 as $jrow ) {
			$s = json_encode( $jrow );

			$found = 0;

			foreach ( $j2 as $k => $j2row ) {

				if ( json_encode( $j2row ) === $s ) {
					pass();

					unset( $j2[$k] );

					$found = 1;
					break;
				}
			}

			if ( !$found ) fail();
		}
	}

	/**
	 * Test widen for constraint.
	 *
	 * @return void
	 */
	public function testWideningColumnForConstraint()
	{
		testpack( 'widening column for constraint' );

		$bean1 = R::dispense( 'project' );
		$bean2 = R::dispense( 'invoice' );

		$bean3 = R::getRedBean()->dispense( 'invoice_project' );

		$bean3->project_id = false;
		$bean3->invoice_id = true;

		R::store( $bean3 );

		$cols = R::getColumns( 'invoice_project' );

		asrt( $cols['project_id'], "int(11) unsigned" );
		asrt( $cols['invoice_id'], "int(11) unsigned" );
	}

	/**
	 * Test adding of constraints directly by invoking
	 * the writer method.
	 *
	 * @return void
	 */
	public function testContrain()
	{
		R::nuke();

		$sql   = '
			CREATE TABLE book (
				id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$sql   = '
			CREATE TABLE page (
				id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$sql   = '
			CREATE TABLE book_page (
				id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
				book_id INT( 11 ) UNSIGNED NOT NULL,
				page_id INT( 11 ) UNSIGNED NOT NULL,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 0 );

		$writer = R::getWriter();

		$writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
		$writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 2 );

		$writer->addFK( 'book_page', 'book', 'book_id', 'id', TRUE );
		$writer->addFK( 'book_page', 'page', 'page_id', 'id', TRUE );

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "book_page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 2 );
	}

	/**
	 * Test adding foreign keys.
	 *
	 * @return void
	 */
	public function testAddingForeignKey()
	{
		R::nuke();

		$sql   = '
			CREATE TABLE book (
				id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$sql   = '
			CREATE TABLE page (
				id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
				book_id INT( 11 ) UNSIGNED NOT NULL,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 0 );

		$writer = R::getWriter();

		//Can we add a foreign key with cascade?
		$writer->addFK('page', 'book', 'book_id', 'id', TRUE);

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 1 );

		//dont add it twice
		$writer->addFK('page', 'book', 'book_id', 'id', TRUE);

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 1 );

		//even if different
		$writer->addFK('page', 'book', 'book_id', 'id', FALSE);

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" ');

		asrt( (int) $numOfFKS, 1 );

		//Now add non-dep key
		R::nuke();

		$sql   = '
			CREATE TABLE book (
				id INT( 11 ) UNSIGNED NULL AUTO_INCREMENT,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$sql   = '
			CREATE TABLE page (
				id INT( 11 ) UNSIGNED AUTO_INCREMENT,
				book_id INT( 11 ) UNSIGNED NULL,
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 0 );

		//even if different
		$writer->addFK('page', 'book', 'book_id', 'id', FALSE);

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" AND DELETE_RULE = "CASCADE"');

		asrt( (int) $numOfFKS, 0 );

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" AND DELETE_RULE = "SET NULL"');

		asrt( (int) $numOfFKS, 1 );

		$writer->addFK('page', 'book', 'book_id', 'id', TRUE);

		$numOfFKS = R::getCell('
			SELECT COUNT(*)
			FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE TABLE_NAME = "page" ');
	}

	/**
	 * Test whether we can manually create indexes.
	 *
	 * @return void
	 */
	public function testAddingIndex()
	{
		R::nuke();

		$sql   = '
			CREATE TABLE song (
				id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
				album_id INT( 11 ) UNSIGNED NOT NULL,
				category VARCHAR( 255 ),
				PRIMARY KEY ( id )
			)
			ENGINE = InnoDB
		';

		R::exec( $sql );

		$sql = 'SHOW INDEX FROM song';

		$indexes = R::getAll( $sql );

		asrt( count( $indexes ), 1 );
		asrt( $indexes[0]['Table'], 'song' );
		asrt( $indexes[0]['Key_name'], 'PRIMARY' );

		$writer = R::getWriter();

		$writer->addIndex('song', 'index1', 'album_id');

		$indexes = R::getAll( 'SHOW INDEX FROM song' );

		asrt( count( $indexes ), 2 );
		asrt( $indexes[0]['Table'], 'song' );
		asrt( $indexes[0]['Key_name'], 'PRIMARY' );
		asrt( $indexes[1]['Table'], 'song' );
		asrt( $indexes[1]['Key_name'], 'index1' );

		//Cant add the same index twice
		$writer->addIndex('song', 'index2', 'category');

		$indexes = R::getAll( 'SHOW INDEX FROM song' );

		asrt( count( $indexes ), 3 );

		//Dont fail, just dont
		try {
			$writer->addIndex('song', 'index3', 'nonexistant');
			pass();
		} catch( \Exception $e ) {
			fail();
		}

		asrt( count( $indexes ), 3 );

		try {
			$writer->addIndex('nonexistant', 'index4', 'nonexistant');
			pass();
		} catch( \Exception $e ) {
			fail();
		}

		asrt( count( $indexes ), 3 );

		try {
			$writer->addIndex('nonexistant', '', 'nonexistant');
			pass();
		} catch( \Exception $e ) {
			fail();
		}

		asrt( count( $indexes ), 3 );
	}
}