#############################################################################

sub wish_to_adjust_options_for_table_columns {

	my ($options) = @_;
	
	$options -> {key} = ['name'];

}

#############################################################################

sub wish_to_clarify_demands_for_table_columns {	

	my ($i, $options) = @_;
	
	$i -> {REMARKS} ||= delete $i -> {label};

	exists $i -> {NULLABLE} or $i -> {NULLABLE} = $i -> {name} eq 'id' ? 0 : 1;

	exists $i -> {COLUMN_DEF} or $i -> {COLUMN_DEF} = undef;

	$i -> {TYPE_NAME} = uc $i -> {TYPE_NAME};
	
	if ($i -> {TYPE_NAME} eq 'NUMERIC') {
		
		$i -> {TYPE_NAME} = 'DECIMAL';
		
	}

	if ($i -> {TYPE_NAME} eq 'MEDIUMINT') {
		
		$i -> {TYPE_NAME} = 'BIGINT';
		
	}

	if ($i -> {TYPE_NAME} =~ /TEXT$/) {
		
		$i -> {TYPE_NAME} = 'TEXT';
		
	}
	
	if ($i -> {TYPE_NAME} eq 'DECIMAL') {
	
		$i -> {COLUMN_SIZE}    ||= 10;
		
		$i -> {DECIMAL_DIGITS} ||= 0;
		
	}
		
	if ($i -> {TYPE_NAME} =~ /VARCHAR$/) {

		$i -> {COLUMN_SIZE} ||= 255;

	}

	if ($i -> {TYPE_NAME} eq 'TIMESTAMP') {

		$i -> {TYPE_NAME} = 'DATETIME';

		$i -> {COLUMN_DEF} = 'GETDATE()';

	}

	if ($i -> {TYPE_NAME} eq 'DATE') {

		$i -> {TYPE_NAME} = 'DATETIME';

	}
	
	if (!$i -> {NULLABLE} && !defined $i -> {COLUMN_DEF} && $i -> {name} ne 'id') {
	
		$i -> {COLUMN_DEF} = 
		
			$i -> {TYPE_NAME} =~ /INT$/     ? 0 : 
			$i -> {TYPE_NAME} eq 'DECIMAL'  ? 0 : 
			$i -> {TYPE_NAME} eq 'DATETIME' ? '1970-01-01' : 
			''
		
	}

	if (defined $i -> {COLUMN_DEF}) {
	
		$i -> {COLUMN_DEF} .= '';
		
	}

}

################################################################################

sub wish_to_explore_existing_table_columns {

	my ($options) = @_;

	my $existing = {};

	sql_select_loop (
		
		q {
			SELECT 
				column_name
				, data_type
				, column_default
				, is_nullable
				, numeric_precision
				, numeric_scale
				, character_maximum_length
			FROM 
				information_schema.columns 
			WHERE 
				table_catalog = db_name()
				AND table_name = ?
		}, 
		
		sub {

			my $name = $i -> {column_name};
			
			$existing -> {$name} = my $def = {
			
				name       => $name,
			
				TYPE_NAME  => uc $i -> {data_type},

				COLUMN_DEF => $i -> {column_default},

				NULLABLE   => ($i -> {is_nullable} =~ /^No/i ? 0 : 1),
				
			};
			
			if ($def -> {COLUMN_DEF} =~ /^\(\'(.*)\'\)$/) {
			
				$def -> {COLUMN_DEF} = $1;
			
			}
			
			if ($def -> {TYPE_NAME} eq 'NUMERIC') {

				$def -> {TYPE_NAME} = 'DECIMAL';

			}

			if ($def -> {TYPE_NAME} eq 'DECIMAL') {
			
				$def -> {COLUMN_SIZE}    = $i -> {numeric_precision};
				$def -> {DECIMAL_DIGITS} = $i -> {numeric_scale};
			
			}
			elsif ($def -> {TYPE_NAME} =~ /CHAR$/) {
			
				$def -> {COLUMN_SIZE}    = $i -> {character_maximum_length};
			
			}
		
		},

		$options -> {table}

	);
	
	sql_select_loop ("EXEC sp_helpindex '$options->{table}'", sub {
	
		foreach my $col (split /\,/, $i -> {index_keys}) {
		
			$col =~ /^\w+/ or next;
			
			push @{$options -> {col2key} -> {lc $1}}, $i -> {index_name};

		}
	
	});

	return $existing;

}

#############################################################################

sub __genereate_sql_fragment_for_column {

	my ($i) = @_;
	
	return if $i -> {SQL};

	$i -> {SQL} = $i -> {TYPE_NAME} . (
					
		$i -> {TYPE_NAME} eq 'DECIMAL' ? " ($i->{COLUMN_SIZE}, $i->{DECIMAL_DIGITS})" :

		$i -> {TYPE_NAME} =~ /CHAR$/ ? " ($i->{COLUMN_SIZE})" :

		'');

	$i -> {SQL} .= $i -> {NULLABLE} ? ' NULL' : ' NOT NULL';

	$i -> {SQL_DEF} = $i -> {SQL};

	if (defined $i -> {COLUMN_DEF}) {
	
		$i -> {COLUMN_DEF} =~ s{'}{''}g; #';

		$i -> {SQL_DEF} .= " DEFAULT '$i->{COLUMN_DEF}'";
	
	}

	%$i = map {$_ => $i -> {$_}} qw (name SQL SQL_DEF NULLABLE TYPE_NAME COLUMN_DEF);

}

#############################################################################

sub wish_to_update_demands_for_table_columns {

	my ($old, $new, $options) = @_;
	
	if ($old -> {TYPE_NAME} =~ /^N.*CHAR$/ and $new -> {TYPE_NAME} !~ /^N/ and $new -> {TYPE_NAME} =~ /(CHAR|TEXT)$/) {
	
		$new -> {TYPE_NAME} = 'N' . $new -> {TYPE_NAME};
	
	}
	
	__adjust_column_dimensions ($old, $new, {
	
		char    => qr {CHAR$},
	
		decimal => 'DECIMAL',

	});

	__genereate_sql_fragment_for_column ($_) foreach ($old, $new);

}

#############################################################################

sub wish_to_schedule_modifications_for_table_columns {

	my ($old, $new, $todo, $options) = @_;

	unless ($old -> {TYPE_NAME} eq $new -> {TYPE_NAME} and $new -> {TYPE_NAME} =~ /VARCHAR$/) {
	
		sql_do ("DROP INDEX [$_]") foreach @{$options -> {col2key} -> {$new -> {name}}};

	}

	push @{$todo -> {$old -> {COLUMN_DEF} eq $new -> {COLUMN_DEF} ? 'alter' : 'recreate'}}, $new;

}

#############################################################################

sub wish_to_actually_alter_table_columns {	

	my ($items, $options) = @_;
	
	sql_do ("ALTER TABLE [$options->{table}] ALTER COLUMN [$_->{name}] $_->{SQL}") foreach @$items;

}

#############################################################################

sub wish_to_actually_create_table_columns {	

	my ($items, $options) = @_;
		
	__genereate_sql_fragment_for_column ($_) foreach @$items;
	
	sql_do ("ALTER TABLE [$options->{table}] ADD " . (join ', ', map {"[$_->{name}] $_->{SQL_DEF}"} @$items));

}

#############################################################################

sub wish_to_actually_recreate_table_columns {

	my ($items, $options) = @_;
	
	foreach my $i (@$items) {

		foreach (
		
			"ALTER TABLE $options->{table} ADD           mssuxx    $i->{SQL_DEF} ", 
			"UPDATE      $options->{table} SET           mssuxx =  $i->{name}",
			"ALTER TABLE $options->{table} DROP COLUMN             $i->{name}",
			"EXEC sp_rename '$options->{table}.mssuxx', '$i->{name}', 'COLUMN'"
			
		) { sql_do ($_) }

	}

}

1;