<?php

DrawHeader( ProgramTitle() );

if ( ! empty( $_REQUEST['values'] )
	&& $_POST['values']
	&& AllowEdit() )
{
	foreach ( (array) $_REQUEST['values'] as $id => $columns )
	{
		// FJ fix SQL bug invalid sort order.

		if ( empty( $columns['SORT_ORDER'] ) || is_numeric( $columns['SORT_ORDER'] ) )
		{
			if ( $id !== 'new' )
			{
				DBUpdate(
					'discipline_field_usage',
					$columns,
					[ 'ID' => (int) $id ]
				);
			}

			// New: check for Title.
			elseif ( $columns['TITLE'] )
			{
				$insert_columns = [
					// ID is added to CATEGORY_ after INSERT, when we retrieve the ID...
					'COLUMN_NAME' => 'CATEGORY_',
				];

				foreach ( (array) $columns as $column => $value )
				{
					if ( $value && $column != 'SORT_ORDER' && $column != 'SELECT_OPTIONS' )
					{
						$insert_columns[ $column ] = $value;
					}
				}

				$id = DBInsert(
					'discipline_fields',
					$insert_columns,
					'id'
				);

				if ( ! $id )
				{
					continue;
				}

				// Update CATEGORY_ with ID now we have it.
				DBQuery( "UPDATE discipline_fields
					SET COLUMN_NAME='CATEGORY_" . $id . "'
					WHERE ID='" . (int) $id . "'" );

				$create_index = true;

				switch ( $columns['DATA_TYPE'] )
				{
					case 'checkbox':
						$sql_type = 'VARCHAR(1)';
						break;

					case 'text':
					case 'multiple_radio':
					case 'multiple_checkbox':
					case 'select':
						/**
						 * MySQL TEXT is limited to 64KB.
						 * With utf8mb4 taking up to 4bytes per characters, there is a limit of
						 * Maximum 65 535 chars (using single-byte characters)
						 * Minimum 16 383 chars (using 4-bytes characters)
						 *
						 * @link https://stackoverflow.com/questions/6766781/maximum-length-for-mysql-type-text
						 */
						$sql_type = 'TEXT';
						break;

					case 'numeric':
						$sql_type = 'NUMERIC(20,2)';
						break;

					case 'date':
						$sql_type = 'DATE';
						break;

					case 'textarea':
						$sql_type = 'TEXT';

						if ( $DatabaseType === 'mysql' )
						{
							/**
							 * MySQL LONGTEXT type is limited to 4GB whereas TEXT is limited to 64KB.
							 *
							 * @since 10.0 MySQL use LONGTEXT type for textarea field
							 *
							 * @link https://stackoverflow.com/questions/6766781/maximum-length-for-mysql-type-text
							 */
							$sql_type = 'LONGTEXT';
						}

						$create_index = false; //FJ SQL bugfix index row size exceeds maximum 2712 for index
						break;
				}

				DBQuery( 'ALTER TABLE discipline_referrals ADD ' .
					DBEscapeIdentifier( 'CATEGORY_' . (int) $id ) . ' ' . $sql_type );

				$max_indices_reached = false;

				if ( $DatabaseType === 'mysql' )
				{
					/**
					 * Fix MySQL error 1069 Too many keys specified; max 64 keys allowed
					 * Count columns having an index
					 *
					 * @since 10.3
					 */
					$indices = DBGet( DBQuery( "SHOW INDEX FROM " . DBEscapeIdentifier( 'discipline_referrals' ) ) );

					$max_indices_reached = count( $indices ) >= 64;
				}

				if ( $create_index
					&& ! $max_indices_reached )
				{
					$key_length = '';

					if ( $sql_type === 'TEXT'
						&& $DatabaseType === 'mysql' )
					{
						/**
						 * Fix MySQL error TEXT column used in key specification without a key length
						 *
						 * @since 10.2.3
						 */
						$key_length = '(255)';
					}

					DBQuery( 'CREATE INDEX ' . DBEscapeIdentifier( 'discipline_referrals_ind' . (int) $id ) .
						' ON discipline_referrals (' .
						DBEscapeIdentifier( 'CATEGORY_' . (int) $id ) . $key_length . ')' );
				}

				$usage_columns = [];

				foreach ( (array) $columns as $column => $value )
				{
					if ( $value && $column != 'DATA_TYPE' )
					{
						$usage_columns[ $column ] = $value;
					}
				}

				DBInsert(
					'discipline_field_usage',
					[
						'DISCIPLINE_FIELD_ID' => (int) $id,
						'SYEAR' => UserSyear(),
						'SCHOOL_ID' => UserSchool(),
					] + $usage_columns
				);
			}
		}
		else
		{
			$error[] = _( 'Please enter a valid Sort Order.' );
		}
	}

	// Unset values & redirect URL.
	RedirectURL( 'values' );
}

if ( $_REQUEST['modfunc'] === 'delete'
	&& AllowEdit() )
{
	if ( DeletePrompt( _( 'Category' ) ) )
	{
		$id = issetVal( $_REQUEST['id'] );

		$delete_sql = "DELETE FROM discipline_fields
			WHERE ID='" . (int) $id . "';";

		$delete_sql .= "DELETE FROM discipline_field_usage
			WHERE DISCIPLINE_FIELD_ID='" . (int) $id . "';";

		DBQuery( $delete_sql );

		$column_name = DBEscapeIdentifier( 'CATEGORY_' . $id );

		DBQuery( "ALTER TABLE discipline_referrals
			DROP COLUMN " . $column_name );

		// Unset modfunc & ID & redirect URL.
		RedirectURL( [ 'modfunc', 'id' ] );
	}
}

if ( $_REQUEST['modfunc'] === 'delete_usage'
	&& AllowEdit() )
{
	if ( DeletePrompt( _( 'Category' ), _( 'Don\'t use' ) ) )
	{
		$id = issetVal( $_REQUEST['id'] );
		DBQuery( "DELETE FROM discipline_field_usage WHERE ID='" . (int) $id . "'" );

		// Unset modfunc & ID & redirect URL.
		RedirectURL( [ 'modfunc', 'id' ] );
	}
}

if ( $_REQUEST['modfunc'] === 'add_usage'
	&& AllowEdit() )
{
	DBQuery( "INSERT INTO discipline_field_usage (DISCIPLINE_FIELD_ID,SYEAR,SCHOOL_ID,TITLE,SELECT_OPTIONS,SORT_ORDER)
		SELECT '" . (int) $_REQUEST['id'] . "' AS DISCIPLINE_FIELD_ID,
		'" . UserSyear() . "' AS SYEAR,'" . UserSchool() . "' AS SCHOOL_ID,TITLE,
		NULL AS SELECT_OPTIONS,NULL AS SORT_ORDER
		FROM discipline_fields WHERE ID='" . (int) $_REQUEST['id'] . "'" );

	// Unset modfunc & ID & redirect URL.
	RedirectURL( [ 'modfunc', 'id' ] );
}

// FJ fix SQL bug invalid sort order
echo ErrorMessage( $error );

if ( ! $_REQUEST['modfunc'] )
{
	$referrals_RET = DBGet(
		"SELECT NULL AS REMOVE,du.ID AS USAGE_ID,df.ID,COALESCE(du.TITLE,df.TITLE) AS TITLE,
		du.SORT_ORDER,df.DATA_TYPE,du.SELECT_OPTIONS
		FROM discipline_fields df LEFT
		OUTER JOIN discipline_field_usage du
		ON (du.DISCIPLINE_FIELD_ID=df.ID
			AND du.SYEAR='" . UserSyear() . "'
			AND du.SCHOOL_ID='" . UserSchool() . "')
		ORDER BY du.SORT_ORDER IS NULL,du.SORT_ORDER,du.ID",
		[
			'REMOVE' => '_makeRemove',
			'TITLE' => '_makeTextInput',
			'SORT_ORDER' => '_makeTextInput',
			'DATA_TYPE' => '_makeType',
			'SELECT_OPTIONS' => '_makeTextAreaInput',
		]
	);

	foreach ( (array) $referrals_RET as $key => $item )
	{
		if ( ! $item['USAGE_ID'] )
		{
			// $referrals_RET[$key]['row_color'] = 'CCCCCC';
		}
	}

	if ( ! empty( $referrals_RET ) )
	{
		$columns = [ 'REMOVE' => '<span class="a11y-hidden">' . _( 'Delete' ) . '</span>' ];
	}
	else
	{
		$columns = [];
	}

	$columns += [
		'TITLE' => _( 'Title' ),
		'SORT_ORDER' => _( 'Sort Order' ),
		'DATA_TYPE' => _( 'Data Type' ),
		'SELECT_OPTIONS' => _( 'Pull-Down' ) . '/' . _( 'Select Multiple from Options' ) . '/' .
			_( 'Select One from Options' ),
	];

	$link['add']['html'] = [
		'REMOVE' => button( 'add' ),
		'TITLE' => _makeTextInput( '', 'TITLE' ),
		'SORT_ORDER' => _makeTextInput( '', 'SORT_ORDER' ),
		'SELECT_OPTIONS' => _makeTextAreaInput( '', 'SELECT_OPTIONS' ),
		'DATA_TYPE' => _makeType( '', 'DATA_TYPE' ),
	];

	echo '<form action="' . URLEscape( 'Modules.php?modname=' . $_REQUEST['modname']  ) . '" method="POST">';

	DrawHeader( '', SubmitButton() );

	ListOutput( $referrals_RET, $columns, 'Referral Form Category', 'Referral Form Categories', $link );
	echo '<div class="center">' . SubmitButton() . '</div>';
	echo '</form>';
}

/**
 * @param $value
 * @param $name
 * @return mixed
 */
function _makeType( $value, $name )
{
	global $THIS_RET;

	if ( ! empty( $THIS_RET['USAGE_ID'] ) )
	{
		$id = $THIS_RET['USAGE_ID'];
	}
	else
	{
		$id = 'new';
	}

	$new_options = [
		'select' => _( 'Pull-Down' ),
		'multiple_radio' => _( 'Select One from Options' ),
		'multiple_checkbox' => _( 'Select Multiple from Options' ),
		'text' => _( 'Text' ),
		'textarea' => _( 'Long Text' ),
		'checkbox' => _( 'Checkbox' ),
		'numeric' => _( 'Number' ),
		'date' => _( 'Date' ),
	];

	if ( ! empty( $THIS_RET['ID'] ) )
	{
		return $new_options[$value];
	}
	else
	{
		return SelectInput( $value, 'values[new][' . $name . ']', '', $new_options, false );
	}
}

/**
 * @param $value
 * @param $name
 * @return mixed
 */
function _makeTextInput( $value, $name )
{
	global $THIS_RET;

	if ( ! empty( $THIS_RET['USAGE_ID'] ) )
	{
		$id = $THIS_RET['USAGE_ID'];
	}
	elseif ( ! empty( $THIS_RET['ID'] ) )
	{
		$id = 'usage';
	}
	else
	{
		$id = 'new';
	}

	$extra = 'maxlength=100';

	if ( $name !== 'TITLE' )
	{
		$extra = 'size=5 maxlength=2';
	}
	elseif ( $id !== 'new' )
	{
		$extra .= ' required';
	}

	$comment = '';

	if ( $name === 'SORT_ORDER' )
	{
		$extra .= ' type="number" min="-9999" max="9999"';

		$comment = '<!-- ' . $value . ' -->';
	}

	if ( $id === 'usage' )
	{
		return $value;
	}
	else
	{
		return $comment .
		TextInput( $value, 'values[' . $id . '][' . $name . ']', '', $extra );
	}
}

/**
 * @param $value
 * @param $name
 * @return mixed
 */
function _makeTextAreaInput( $value, $name )
{
	global $THIS_RET;

	if ( ! empty( $THIS_RET['USAGE_ID'] ) )
	{
		$id = $THIS_RET['USAGE_ID'];
	}
	elseif ( ! empty( $THIS_RET['ID'] ) )
	{
		$id = 'usage';
	}
	else
	{
		$id = 'new';
	}

	if ( $id === 'usage' )
	{
		return $value;
	}
	elseif ( $id === 'new'
		|| $THIS_RET['DATA_TYPE'] === 'multiple_checkbox'
		|| $THIS_RET['DATA_TYPE'] === 'multiple_radio'
		|| $THIS_RET['DATA_TYPE'] === 'select' )
	{
		$return = TextAreaInput( $value, 'values[' . $id . '][' . $name . ']', '', '', $id !== 'new', 'text' );

		//FJ responsive rt td too large
		$return = '<div id="divTextAreaContent' . $id . '" class="rt2colorBox">' .
			$return .
			'</div>';

		return $return;
	}
	else
	{
		return _( 'N/A' );
	}
}

/**
 * @param $value
 * @param $column
 * @return mixed
 */
function _makeRemove( $value, $column )
{
	global $THIS_RET;

	$return = '';

	if ( AllowEdit() )
	{
		if ( ! empty( $THIS_RET['USAGE_ID'] ) )
		{
			$return = button(
				'remove', _( 'Don\'t use' ),
				URLEscape( 'Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=delete_usage&id=' . $THIS_RET['USAGE_ID'] )
			);

			$return .= '<br class="rbr"> ' . button(
				'remove',
				_( 'Delete' ),
				URLEscape( 'Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=delete&id=' . $THIS_RET['ID'] )
			);
		}
		else
		{
			$return = button(
				'add',
				_( 'Use at this school' ),
				URLEscape( 'Modules.php?modname=' . $_REQUEST['modname'] . '&modfunc=add_usage&id=' . $THIS_RET['ID'] )
			);
		}
	}

	return $return;
}