#
# Patch for 0.54,0.55 - 0.62
#

# cleanup
DELETE FROM s_attribute_type WHERE s_attribute_type IN ('S_TITLE', 'S_STATUS', 'S_STATCMNT');

#
# Moving s_field_type to new table
#
ALTER TABLE s_attribute_type ADD s_field_type varchar(10) AFTER display_type;

# Item Title s_field_type attribute
INSERT INTO s_attribute_type (s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type) VALUES ( 'S_TITLE', 'Item Title', 'Title', 'text(50,255)', 'hidden', 'TITLE', NULL );
INSERT INTO s_attribute_type (s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type) VALUES ( 'S_STATUS', 'System Status Type', 'Status Type', NULL, 'hidden', 'STATUSTYPE', NULL );
INSERT INTO s_attribute_type (s_attribute_type, description, prompt, input_type, display_type, s_field_type, site_type) VALUES ( 'S_STATCMNT', 'System Status Comment', 'Status Comment', 'textarea(50,5)', 'hidden', 'STATUSCMNT', NULL );

#
# It is not compulsory to set the duration, especially for Wishlist, where the input field will
# not even be supplied.
#
UPDATE s_item_attribute_type SET compulsory_ind = 'N' WHERE s_attribute_type = 'S_DURATION';

#
# Inclusion of S_STATCMNT attribute, at 255, necessitates this update.
#
UPDATE s_item_attribute_type SET order_no = '254' WHERE s_attribute_type = 'S_STATUS' and order_no = '255';

#
# These attributes are mapped to item and item_instance columns, and should have no instances in item_attribute table.
#
DELETE FROM item_attribute WHERE s_attribute_type IN('S_STATUS','S_DURATION','S_STATCMNT','S_TITLE');

#
# UPDATE s_item_attribute_type AND item_attribute for IMAGEURL, so the input field's appear after
# the TITLE, instead of before, as they currently do!
#
UPDATE s_item_attribute_type SET order_no = '2' WHERE s_attribute_type = 'IMAGEURL' and order_no = '0';
UPDATE item_attribute SET order_no = '2' WHERE s_attribute_type = 'IMAGEURL' and order_no = '0';

#
# Fix the duration lookup
#
UPDATE s_attribute_type_lookup SET value = '' WHERE s_attribute_type = 'S_DURATION' AND value = '0';
UPDATE item_instance SET borrow_duration = NULL, update_on = update_on WHERE borrow_duration = '0';

#
# Due to update of format_mins widget, we want to get rid of all english words from the
# format mask.
# 
UPDATE s_attribute_type SET display_type = 'format_mins(%h %H %m %M)' WHERE s_attribute_type = 'RUN_TIME';

#
# AUDIO Lang support patch
#

# dts.gif image is smaller than the jpeg one.
UPDATE s_attribute_type_lookup SET img = 'dts.gif' WHERE s_attribute_type = 'AUDIO_LANG' AND value = 'ENGLISH_DTS';
UPDATE s_attribute_type_lookup SET img = 'dolby.gif' WHERE s_attribute_type = 'AUDIO_LANG' AND value = 'ENGLISH_5.0';

#
# New Audio Language options
#
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_THX', 'English(THX)', 'thx.gif', NULL);
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_6.1_DTS_ES', 'English (6.1 DTS ES)', 'dts.gif', NULL);
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_6.1_EX', 'English(6.1 EX)', 'dolby.gif', NULL);
INSERT INTO s_attribute_type_lookup (s_attribute_type, order_no, value, display, img, checked_ind) VALUES ( 'AUDIO_LANG', NULL, 'ENGLISH_6.1', 'English(6.1)', 'dolby.gif', NULL);

#
# Deprecated check_boxes in favour of checkbox_grid
#
UPDATE s_attribute_type SET input_type = 'checkbox_grid(%value% - %display%, 1)' WHERE s_attribute_type = 'DVD_REGION';
UPDATE s_attribute_type SET input_type = 'checkbox_grid(%value%, *)' WHERE s_attribute_type = 'RATIO';

#
# Deprecated radio_group in favour of radio_grid
#
UPDATE s_attribute_type SET input_type = 'radio_grid(%img% %display%, 1)' WHERE s_attribute_type = 'AGE_RATING';
UPDATE s_attribute_type SET input_type = 'radio_grid(%display%, *)' WHERE s_attribute_type = 'VIDQUALITY';

#
# Status Type support
#
ALTER TABLE item_instance CHANGE active_ind s_status_type varchar(1) NOT NULL default 'Y';
ALTER TABLE item_instance ADD status_comment varchar(255) AFTER s_status_type;

#
# Update s_status_type to new 'A' - Active type.
#
UPDATE item_instance SET s_status_type = 'A', update_on = update_on WHERE s_status_type = 'Y';

#
# Update empty type's to be correct
#
UPDATE user SET type = 'N' WHERE type = '';

#
# Make category allow 100 characters instead of 50
#
ALTER TABLE item CHANGE category category varchar(100);

#
# Fix borrowed item indexes
# 
# Drop old index.
ALTER TABLE borrowed_item DROP INDEX borrowed_item_idx;
# drop and replace new indexes
ALTER TABLE borrowed_item DROP INDEX borrower_idx;
ALTER TABLE borrowed_item ADD INDEX borrower_idx ( borrower_id );
ALTER TABLE borrowed_item DROP INDEX item_instance_idx;
ALTER TABLE borrowed_item ADD INDEX item_instance_idx ( item_id, instance_no );

#
# Fix review table indexes
# 
# Drop old index.
ALTER TABLE review DROP INDEX author_item;
# drop and replace new indexes
ALTER TABLE review DROP INDEX author_idx;
ALTER TABLE review ADD INDEX author_idx ( author_id );
ALTER TABLE review DROP INDEX item_idx;
ALTER TABLE review ADD INDEX item_idx ( item_id );

#
# Fix item table indexes
#
# Drop old index.
ALTER TABLE item DROP INDEX title;
# drop and replace new indexes
ALTER TABLE item DROP INDEX title_idx;
ALTER TABLE item ADD INDEX title_idx ( title );

ALTER TABLE item DROP INDEX s_item_type_idx;
ALTER TABLE item ADD INDEX s_item_type_idx ( s_item_type );

#
# Fix item_instance table indexes
#
# Drop old index.
ALTER TABLE item_instance DROP INDEX owner_id;
# drop and replace new indexes
ALTER TABLE item_instance DROP INDEX owner_id_idx;
ALTER TABLE item_instance ADD INDEX owner_id_idx ( owner_id );
ALTER TABLE item_instance DROP INDEX s_status_type_idx;
ALTER TABLE item_instance ADD INDEX s_status_type_idx ( s_status_type );

#
# System Item Status table
#
CREATE table s_status_type (
	s_status_type				varchar(1) NOT NULL default 'Y',
	description					varchar(30) NOT NULL,
    img							varchar(255),
	insert_ind					varchar(1) NOT NULL default 'Y',
	update_ind					varchar(1) NOT NULL default 'Y',
	delete_ind					varchar(1) NOT NULL default 'Y',
    min_display_user_type		varchar(1),
	min_create_user_type		varchar(1),
	new_owner_instance_ind		varchar(1) NOT NULL default 'Y',
	new_not_owner_instance_ind	varchar(1) NOT NULL default 'Y',
	borrow_ind					varchar(1) NOT NULL default 'Y',
	status_comment_ind			varchar(1) NOT NULL default 'N',
	default_ind					varchar(1),
	closed_ind					varchar(1) NOT NULL default 'N',
	PRIMARY KEY ( s_status_type )
) TYPE=MyISAM COMMENT='System Item Status table';

INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('A', 'Available', 'avail.gif', 'Y', 'Y', 'Y', 'G', 'N', 'Y', 'Y', 'Y', 'N', 'Y', 'N');

INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('H', 'Hidden', 'hidden.gif', 'Y', 'Y', 'Y', 'A', 'N', 'Y', 'Y', 'Y',  'N', '', 'N');

INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('N', 'Inactive', 'inactive.gif', 'Y', 'Y', 'Y', 'G', 'N', 'Y', 'Y', 'N',  'Y', '', 'N');

INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('O', 'Ordered', 'ordered.gif', 'Y', 'Y', 'Y', 'G', 'N', 'N', 'Y', 'X',  'Y', '', 'N');

INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('W', 'Wishlist', 'wishlist.gif', 'Y', 'Y', 'Y', 'G', 'N', 'N', 'Y', 'X',  'Y', '', 'N');

INSERT INTO s_status_type ( s_status_type, description, img, insert_ind, update_ind, delete_ind, min_display_user_type, min_create_user_type, new_owner_instance_ind, new_not_owner_instance_ind, borrow_ind, status_comment_ind, default_ind, closed_ind )
VALUES ('X', 'External', 'external.gif', 'N', 'Y', 'N', 'G', 'N', 'Y', 'Y', 'B',  'H', '', 'N');

#
# A bug in System Admin Tool for 0.60 releases 
#
UPDATE s_attribute_type SET s_field_type = UPPER(s_field_type) WHERE s_field_type IS NOT NULL;

#
# Need to increase size of language and theme columns in database
# as there are already themes which have exceeded the previous
# 10 character limit.
#
ALTER TABLE user CHANGE language language varchar(20);
ALTER TABLE user CHANGE theme theme varchar(20);