ALTER TABLE `user` DROP `created_at_del` ,
DROP `updated_at_del` ;

CREATE TABLE IF NOT EXISTS `country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `nationality` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT into country (code, name, nationality) VALUES ('AD', 'Andorra', 'Andorran');
INSERT into country (code, name, nationality) VALUES ('AE', 'United Arab Emirates', 'Emirian');
INSERT into country (code, name, nationality) VALUES ('AF', 'Afghanistan', 'Afghan');
INSERT into country (code, name, nationality) VALUES ('AG', 'Antigua and Barbuda', 'Antiguans, Barbudans');
INSERT into country (code, name, nationality) VALUES ('AL', 'Albania', 'Albanian');
INSERT into country (code, name, nationality) VALUES ('AM', 'Armenia', 'Armenian');
INSERT into country (code, name, nationality) VALUES ('AO', 'Angola', 'Angolan');
INSERT into country (code, name, nationality) VALUES ('AR', 'Argentina', 'Argentinean');
INSERT into country (code, name, nationality) VALUES ('AT', 'Austria', 'Austrian');
INSERT into country (code, name, nationality) VALUES ('AU', 'Australia', 'Australian');
INSERT into country (code, name, nationality) VALUES ('AZ', 'Azerbaijan', 'Azerbaijani');
INSERT into country (code, name, nationality) VALUES ('BA', 'Bosnia and Herzegovina', 'Bosnian, Herzegovinian');
INSERT into country (code, name, nationality) VALUES ('BB', 'Barbados', 'Barbadian');
INSERT into country (code, name, nationality) VALUES ('BD', 'Bangladesh', 'Bangladeshi');
INSERT into country (code, name, nationality) VALUES ('BE', 'Belgium', 'Belgian');
INSERT into country (code, name, nationality) VALUES ('BF', 'Burkina Faso', 'Burkinabe');
INSERT into country (code, name, nationality) VALUES ('BG', 'Bulgaria', 'Bulgarian');
INSERT into country (code, name, nationality) VALUES ('BH', 'Bahrain', 'Bahraini');
INSERT into country (code, name, nationality) VALUES ('BI', 'Burundi', 'Burundian');
INSERT into country (code, name, nationality) VALUES ('BJ', 'Benin', 'Beninese');
INSERT into country (code, name, nationality) VALUES ('BN', 'Brunei', 'Bruneian');
INSERT into country (code, name, nationality) VALUES ('BO', 'Bolivia', 'Bolivian');
INSERT into country (code, name, nationality) VALUES ('BR', 'Brazil', 'Brazilian');
INSERT into country (code, name, nationality) VALUES ('BS', 'The Bahamas', 'Bahamian');
INSERT into country (code, name, nationality) VALUES ('BT', 'Bhutan', 'Bhutanese');
INSERT into country (code, name, nationality) VALUES ('BW', 'Botswana', 'Motswana ('',singular), Batswana ('',plural)');
INSERT into country (code, name, nationality) VALUES ('BY', 'Belarus', 'Belarusian');
INSERT into country (code, name, nationality) VALUES ('BZ', 'Belize', 'Belizean');
INSERT into country (code, name, nationality) VALUES ('CA', 'Canada', 'Canadian');
INSERT into country (code, name, nationality) VALUES ('CD', 'Congo, Republic of the', 'Congolese');
INSERT into country (code, name, nationality) VALUES ('CG', 'Congo, Democratic Republic of the', 'Congolese');
INSERT into country (code, name, nationality) VALUES ('CH', 'Switzerland', 'Swiss');
INSERT into country (code, name, nationality) VALUES ('CI', 'Cote d''Ivoire', 'Ivorian');
INSERT into country (code, name, nationality) VALUES ('CM', 'Cameroon', 'Cameroonian');
INSERT into country (code, name, nationality) VALUES ('CN', 'China', 'Chinese');
INSERT into country (code, name, nationality) VALUES ('CO', 'Colombia', 'Colombian');
INSERT into country (code, name, nationality) VALUES ('CR', 'Costa Rica', 'Costa Rican');
INSERT into country (code, name, nationality) VALUES ('CU', 'Cuba', 'Cuban');
INSERT into country (code, name, nationality) VALUES ('CV', 'Cape Verde', 'Cape Verdian');
INSERT into country (code, name, nationality) VALUES ('CY', 'Cyprus', 'Cypriot');
INSERT into country (code, name, nationality) VALUES ('CZ', 'Czech Republic', 'Czech');
INSERT into country (code, name, nationality) VALUES ('DE', 'Germany', 'German');
INSERT into country (code, name, nationality) VALUES ('DJ', 'Djibouti', 'Djibouti');
INSERT into country (code, name, nationality) VALUES ('DK', 'Denmark', 'Danish');
INSERT into country (code, name, nationality) VALUES ('DM', 'Dominica', 'Dominican');
INSERT into country (code, name, nationality) VALUES ('DO', 'Dominican Republic', 'Dominican');
INSERT into country (code, name, nationality) VALUES ('DZ', 'Algeria', 'Algerian');
INSERT into country (code, name, nationality) VALUES ('EC', 'Ecuador', 'Ecuadorean');
INSERT into country (code, name, nationality) VALUES ('EE', 'Estonia', 'Estonian');
INSERT into country (code, name, nationality) VALUES ('EG', 'Egypt', 'Egyptian');
INSERT into country (code, name, nationality) VALUES ('ER', 'Eritrea', 'Eritrean');
INSERT into country (code, name, nationality) VALUES ('ES', 'Spain', 'Spanish');
INSERT into country (code, name, nationality) VALUES ('ET', 'Ethiopia', 'Ethiopian');
INSERT into country (code, name, nationality) VALUES ('FI', 'Finland', 'Finnish');
INSERT into country (code, name, nationality) VALUES ('FJ', 'Fiji', 'Fijian');
INSERT into country (code, name, nationality) VALUES ('FR', 'France', 'French');
INSERT into country (code, name, nationality) VALUES ('GA', 'Gabon', 'Gabonese');
INSERT into country (code, name, nationality) VALUES ('GD', 'Grenada', 'Grenadian');
INSERT into country (code, name, nationality) VALUES ('GE', 'Georgia', 'Georgian');
INSERT into country (code, name, nationality) VALUES ('GH', 'Ghana', 'Ghanaian');
INSERT into country (code, name, nationality) VALUES ('GM', 'The Gambia', 'Gambian');
INSERT into country (code, name, nationality) VALUES ('GN', 'Guinea', 'Guinean');
INSERT into country (code, name, nationality) VALUES ('GQ', 'Equatorial Guinea', 'Equatorial Guinean');
INSERT into country (code, name, nationality) VALUES ('GT', 'Guatemala', 'Guatemalan');
INSERT into country (code, name, nationality) VALUES ('GW', 'Guinea-Bissau', 'Guinea-Bissauan');
INSERT into country (code, name, nationality) VALUES ('GY', 'Guyana', 'Guyanese');
INSERT into country (code, name, nationality) VALUES ('HK', 'Hong Kong', 'Hong Kong');
INSERT into country (code, name, nationality) VALUES ('HN', 'Honduras', 'Honduran');
INSERT into country (code, name, nationality) VALUES ('HR', 'Croatia', 'Croatian');
INSERT into country (code, name, nationality) VALUES ('HT', 'Haiti', 'Haitian');
INSERT into country (code, name, nationality) VALUES ('HU', 'Hungary', 'Hungarian');
INSERT into country (code, name, nationality) VALUES ('ID', 'Indonesia', 'Indonesian');
INSERT into country (code, name, nationality) VALUES ('IE', 'Ireland', 'Irish');
INSERT into country (code, name, nationality) VALUES ('IL', 'Israel', 'Israeli');
INSERT into country (code, name, nationality) VALUES ('IN', 'India', 'Indian');
INSERT into country (code, name, nationality) VALUES ('IQ', 'Iraq', 'Iraqi');
INSERT into country (code, name, nationality) VALUES ('IR', 'Iran', 'Iranian');
INSERT into country (code, name, nationality) VALUES ('IS', 'Iceland', 'Icelander');
INSERT into country (code, name, nationality) VALUES ('IT', 'Italy', 'Italian');
INSERT into country (code, name, nationality) VALUES ('JM', 'Jamaica', 'Jamaican');
INSERT into country (code, name, nationality) VALUES ('JO', 'Jordan', 'Jordanian');
INSERT into country (code, name, nationality) VALUES ('JP', 'Japan', 'Japanese');
INSERT into country (code, name, nationality) VALUES ('KE', 'Kenya', 'Kenyan');
INSERT into country (code, name, nationality) VALUES ('KG', 'Kyrgyz Republic', 'Kirghiz');
INSERT into country (code, name, nationality) VALUES ('KH', 'Cambodia', 'Cambodian');
INSERT into country (code, name, nationality) VALUES ('KI', 'Kiribati', 'I-Kiribati');
INSERT into country (code, name, nationality) VALUES ('KM', 'Comoros', 'Comoran');
INSERT into country (code, name, nationality) VALUES ('KN', 'Saint Kitts and Nevis', 'Kittian and Nevisian');
INSERT into country (code, name, nationality) VALUES ('KP', 'Korea, North', 'North Korean');
INSERT into country (code, name, nationality) VALUES ('KR', 'Korea, South', 'South Korean');
INSERT into country (code, name, nationality) VALUES ('KW', 'Kuwait', 'Kuwaiti');
INSERT into country (code, name, nationality) VALUES ('KY', 'Central African Republic', 'Central African');
INSERT into country (code, name, nationality) VALUES ('KZ', 'Kazakhstan', 'Kazakhstani');
INSERT into country (code, name, nationality) VALUES ('LA', 'Laos', 'Laotian');
INSERT into country (code, name, nationality) VALUES ('LB', 'Lebanon', 'Lebanese');
INSERT into country (code, name, nationality) VALUES ('LC', 'Saint Lucia', 'Saint Lucian');
INSERT into country (code, name, nationality) VALUES ('LI', 'Liechtenstein', 'Liechtensteiner');
INSERT into country (code, name, nationality) VALUES ('LK', 'Sri Lanka', 'Sri Lankan');
INSERT into country (code, name, nationality) VALUES ('LR', 'Liberia', 'Liberian');
INSERT into country (code, name, nationality) VALUES ('LS', 'Lesotho', 'Mosotho');
INSERT into country (code, name, nationality) VALUES ('LT', 'Lithuania', 'Lithuanian');
INSERT into country (code, name, nationality) VALUES ('LU', 'Luxembourg', 'Luxembourger');
INSERT into country (code, name, nationality) VALUES ('LV', 'Latvia', 'Latvian');
INSERT into country (code, name, nationality) VALUES ('LY', 'Libya', 'Libyan');
INSERT into country (code, name, nationality) VALUES ('MA', 'Morocco', 'Moroccan');
INSERT into country (code, name, nationality) VALUES ('MC', 'Monaco', 'Monegasque');
INSERT into country (code, name, nationality) VALUES ('MD', 'Moldova', 'Moldovan');
INSERT into country (code, name, nationality) VALUES ('MG', 'Madagascar', 'Malagasy');
INSERT into country (code, name, nationality) VALUES ('MH', 'Marshall Islands', 'Marshallese');
INSERT into country (code, name, nationality) VALUES ('MI', 'Federated States of Micronesia', 'Micronesian');
INSERT into country (code, name, nationality) VALUES ('MK', 'Macedonia', 'Macedonian');
INSERT into country (code, name, nationality) VALUES ('ML', 'Mali', 'Malian');
INSERT into country (code, name, nationality) VALUES ('MM', 'Myanmar ('',Burma)', 'Burmese');
INSERT into country (code, name, nationality) VALUES ('MR', 'Mauritania', 'Mauritanian');
INSERT into country (code, name, nationality) VALUES ('MT', 'Malta', 'Maltese');
INSERT into country (code, name, nationality) VALUES ('MU', 'Mauritius', 'Mauritian');
INSERT into country (code, name, nationality) VALUES ('MV', 'Maldives', 'Maldivan');
INSERT into country (code, name, nationality) VALUES ('MW', 'Malawi', 'Malawian');
INSERT into country (code, name, nationality) VALUES ('MX', 'Mexico', 'Mexican');
INSERT into country (code, name, nationality) VALUES ('MY', 'Malaysia', 'Malaysian');
INSERT into country (code, name, nationality) VALUES ('MZ', 'Mozambique', 'Mozambican');
INSERT into country (code, name, nationality) VALUES ('NA', 'Namibia', 'Namibian');
INSERT into country (code, name, nationality) VALUES ('NE', 'Niger', 'Nigerien');
INSERT into country (code, name, nationality) VALUES ('NG', 'Nigeria', 'Nigerian');
INSERT into country (code, name, nationality) VALUES ('NI', 'Nicaragua', 'Nicaraguan');
INSERT into country (code, name, nationality) VALUES ('NL', 'Netherlands', 'Dutch');
INSERT into country (code, name, nationality) VALUES ('NO', 'Norway', 'Norwegian');
INSERT into country (code, name, nationality) VALUES ('NP', 'Nepal', 'Nepalese');
INSERT into country (code, name, nationality) VALUES ('NR', 'Nauru', 'Nauruan');
INSERT into country (code, name, nationality) VALUES ('NZ', 'New Zealand', 'New Zealander');
INSERT into country (code, name, nationality) VALUES ('OM', 'Oman', 'Omani');
INSERT into country (code, name, nationality) VALUES ('PA', 'Panama', 'Panamanian');
INSERT into country (code, name, nationality) VALUES ('PE', 'Peru', 'Peruvian');
INSERT into country (code, name, nationality) VALUES ('PG', 'Papua New Guinea', 'Papua New Guinean');
INSERT into country (code, name, nationality) VALUES ('PH', 'Philippines', 'Filipino');
INSERT into country (code, name, nationality) VALUES ('PK', 'Pakistan', 'Pakistani');
INSERT into country (code, name, nationality) VALUES ('PL', 'Poland', 'Polish');
INSERT into country (code, name, nationality) VALUES ('PT', 'Portugal', 'Portuguese');
INSERT into country (code, name, nationality) VALUES ('PW', 'Palau', 'Palauan');
INSERT into country (code, name, nationality) VALUES ('PY', 'Paraguay', 'Paraguayan');
INSERT into country (code, name, nationality) VALUES ('QA', 'Qatar', 'Qatari');
INSERT into country (code, name, nationality) VALUES ('RO', 'Romania', 'Romanian');
INSERT into country (code, name, nationality) VALUES ('RS', 'Serbia and Montenegro', 'Serbian');
INSERT into country (code, name, nationality) VALUES ('RU', 'Russia', 'Russian');
INSERT into country (code, name, nationality) VALUES ('RW', 'Rwanda', 'Rwandan');
INSERT into country (code, name, nationality) VALUES ('SA', 'Saudi Arabia', 'Saudi Arabian');
INSERT into country (code, name, nationality) VALUES ('SB', 'Solomon Islands', 'Solomon Islander');
INSERT into country (code, name, nationality) VALUES ('SC', 'Seychelles', 'Seychellois');
INSERT into country (code, name, nationality) VALUES ('SD', 'Sudan', 'Sudanese');
INSERT into country (code, name, nationality) VALUES ('SE', 'Sweden', 'Swedish');
INSERT into country (code, name, nationality) VALUES ('SG', 'Singapore', 'Singaporean');
INSERT into country (code, name, nationality) VALUES ('SI', 'Slovenia', 'Slovene');
INSERT into country (code, name, nationality) VALUES ('SK', 'Slovakia', 'Slovak');
INSERT into country (code, name, nationality) VALUES ('SL', 'Sierra Leone', 'Sierra Leonean');
INSERT into country (code, name, nationality) VALUES ('SM', 'San Marino', 'Sammarinese');
INSERT into country (code, name, nationality) VALUES ('SN', 'Senegal', 'Senegalese');
INSERT into country (code, name, nationality) VALUES ('SO', 'Somalia', 'Somali');
INSERT into country (code, name, nationality) VALUES ('SR', 'Suriname', 'Surinamer');
INSERT into country (code, name, nationality) VALUES ('ST', 'Sao Tome and Principe', 'Sao Tomean');
INSERT into country (code, name, nationality) VALUES ('SV', 'El Salvador', 'Salvadoran');
INSERT into country (code, name, nationality) VALUES ('SY', 'Syria', 'Syrian');
INSERT into country (code, name, nationality) VALUES ('SZ', 'Swaziland', 'Swazi');
INSERT into country (code, name, nationality) VALUES ('TD', 'Chad', 'Chadian');
INSERT into country (code, name, nationality) VALUES ('TG', 'Togo', 'Togolese');
INSERT into country (code, name, nationality) VALUES ('TH', 'Thailand', 'Thai');
INSERT into country (code, name, nationality) VALUES ('TJ', 'Tajikistan', 'Tadzhik');
INSERT into country (code, name, nationality) VALUES ('TL', 'East Timor', 'East Timorese');
INSERT into country (code, name, nationality) VALUES ('TM', 'Turkmenistan', 'Turkmen');
INSERT into country (code, name, nationality) VALUES ('TN', 'Tunisia', 'Tunisian');
INSERT into country (code, name, nationality) VALUES ('TO', 'Tonga', 'Tongan');
INSERT into country (code, name, nationality) VALUES ('TR', 'Turkey', 'Turkish');
INSERT into country (code, name, nationality) VALUES ('TT', 'Trinidad and Tobago', 'Trinidadian');
INSERT into country (code, name, nationality) VALUES ('TV', 'Tuvalu', 'Tuvaluan');
INSERT into country (code, name, nationality) VALUES ('TW', 'Taiwan', 'Taiwanese');
INSERT into country (code, name, nationality) VALUES ('TZ', 'Tanzania', 'Tanzanian');
INSERT into country (code, name, nationality) VALUES ('UA', 'Ukraine', 'Ukrainian');
INSERT into country (code, name, nationality) VALUES ('UG', 'Uganda', 'Ugandan');
INSERT into country (code, name, nationality) VALUES ('UK', 'United Kingdom', 'British');
INSERT into country (code, name, nationality) VALUES ('US', 'United States', 'American');
INSERT into country (code, name, nationality) VALUES ('UY', 'Uruguay', 'Uruguayan');
INSERT into country (code, name, nationality) VALUES ('UZ', 'Uzbekistan', 'Uzbekistani');
INSERT into country (code, name, nationality) VALUES ('VA', 'Vatican City ('',Holy See)', 'none');
INSERT into country (code, name, nationality) VALUES ('VE', 'Venezuela', 'Venezuelan');
INSERT into country (code, name, nationality) VALUES ('VH', 'Chile', 'Chilean');
INSERT into country (code, name, nationality) VALUES ('VN', 'Vietnam', 'Vietnamese');
INSERT into country (code, name, nationality) VALUES ('VU', 'Vanuatu', 'Ni-Vanuatu');
INSERT into country (code, name, nationality) VALUES ('WS', 'Samoa', 'Samoan');
INSERT into country (code, name, nationality) VALUES ('YE', 'Yemen', 'Yemeni');
INSERT into country (code, name, nationality) VALUES ('ZA', 'South Africa', 'South African');
INSERT into country (code, name, nationality) VALUES ('ZM', 'Zambia', 'Zambian');
INSERT into country (code, name, nationality) VALUES ('ZW', 'Zimbabwe', 'Zimbabwean');

ALTER TABLE `country` ADD `status` VARCHAR( 1 ) NOT NULL DEFAULT 'A';

CREATE TABLE IF NOT EXISTS `salutation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `salutation` varchar(50) NOT NULL,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

INSERT INTO `residenz`.`salutation` (
`id` ,
`salutation` ,
`status`
)
VALUES (
NULL , 'Mr.', 'A'
), (
NULL , 'Mrs.', 'A'
), (
NULL , 'Ms.', 'A'
), (
NULL , 'Miss', 'A'
), (
NULL , 'Dr.', 'A'
), (
NULL , 'Prof.', 'A'
), (
NULL , 'Other', 'A'
);

ALTER TABLE `country` ADD `created_datetime` DATETIME NOT NULL ,
ADD `updated_datetime` DATETIME NOT NULL ,
ADD `created_by` INT NOT NULL ,
ADD `updated_by` INT NOT NULL ;

ALTER TABLE `salutation` ADD `created_datetime` DATETIME NOT NULL ,
ADD `updated_datetime` DATETIME NOT NULL ,
ADD `created_by` INT NOT NULL ,
ADD `updated_by` INT NOT NULL ;

ALTER TABLE `user_profile` DROP `salutation` ;

ALTER TABLE `user_profile` ADD `salutation_id` INT NULL AFTER `photo` ;