DROP TABLE room_utilization_detail;
DROP TABLE room_utilization_header;
DROP TABLE guest;
DROP TABLE guest_history;
DROP TABLE guest_registration_expense_detail;
DROP TABLE guest_registration_header;
DROP TABLE guest_registration_room_detail;
DROP TABLE miscellaneous_expense;
DROP TABLE renovation_detail;
DROP TABLE renovation_header;
DROP TABLE reservation_detail;
DROP TABLE reservation_header;
DROP TABLE room_transaction;
DROP TABLE room_master;
DROP TABLE payment_mode;
DROP TABLE room_type;
DROP TABLE system_audit;
DROP TABLE system_messages;
DROP TABLE users;
DROP TABLE users_group;
DROP TABLE users_role;


CREATE TABLE room_utilization_detail (
   s_room_utilization_form_number CHAR (10) NOT NULL,
   s_room_id CHAR (10) NOT NULL,
   s_remarks CHAR (80),
   s_description CHAR (80) NOT NULL,
 PRIMARY KEY (s_room_utilization_form_number,s_room_id)
);

CREATE TABLE room_utilization_header (
   s_room_utilization_form_number CHAR (10) NOT NULL,
   c_status CHAR (1) NOT NULL,
   dt_from DATETIME,
   dt_to DATETIME,
   c_permanent CHAR (1) NOT NULL,
   s_remarks CHAR (80),
   s_prepared_by CHAR (10) NOT NULL,
   dt_prepared_on DATETIME NOT NULL,
   s_approved_by CHAR (10),
   dt_approved_on DATETIME,
   s_cancelled_by CHAR (10),
   dt_cancelled_on DATETIME,
 PRIMARY KEY (s_room_utilization_form_number)
);

CREATE TABLE guest (
   s_guest_id CHAR (10) NOT NULL,
   s_first_name CHAR (40) NOT NULL,
   s_last_name CHAR (20),
   s_middle_name CHAR (20),
   s_address CHAR (80),
   s_company_name CHAR (40),
   s_nationality CHAR (40) NOT NULL,
   c_sex CHAR (1) NOT NULL,
   s_company_address CHAR (80),
   s_tel_number CHAR (80),
   s_fax_number CHAR (40),
   s_email_address CHAR (40),
   c_deleted CHAR (1) NOT NULL,
 PRIMARY KEY (s_guest_id)
);

CREATE TABLE guest_history (
   s_form_number CHAR (10) NOT NULL,
   s_guest_id CHAR (10) NOT NULL,
   s_description CHAR (80),
   dt_date_from DATETIME NOT NULL,
   dt_date_to DATETIME NOT NULL,
   dt_time_from DATETIME NOT NULL,
   dt_time_to DATETIME NOT NULL,
   c_cancelled CHAR (1) NOT NULL,
 PRIMARY KEY (s_form_number,s_guest_id)
);

CREATE TABLE guest_registration_expense_detail (
   s_guest_registration_form_number CHAR (10) NOT NULL,
   s_miscellaneous_expense_id CHAR (10) NOT NULL,
   d_expense_cost DECIMAL (11,4) NOT NULL,
   s_remarks CHAR (80),
 PRIMARY KEY (s_guest_registration_form_number,s_miscellaneous_expense_id)
);

CREATE TABLE guest_registration_header (
   s_guest_registration_form_number CHAR (10) NOT NULL,
   s_guest_id CHAR (10) NOT NULL,
   s_payment_id CHAR (10) NOT NULL,
   c_no_definite_date CHAR (1) NOT NULL,
   dt_actual_arrival_date DATETIME NOT NULL,
   dt_actual_arrival_time DATETIME NOT NULL,
   dt_actual_departure_date DATETIME,
   dt_actual_departure_time DATETIME,
   dt_tentative_departure_date DATETIME NOT NULL,
   dt_tentative_departure_time DATETIME NOT NULL,
   c_status CHAR (1) NOT NULL,
   d_discount DECIMAL (5,2) NOT NULL,
   s_remarks CHAR (80),
   d_amount_deposited DECIMAL (11,4) NOT NULL,
   d_amount_paid DECIMAL (11,4) NOT NULL,
   d_total_cost DECIMAL (11,4) NOT NULL,
   s_prepared_by CHAR (10) NOT NULL,
   dt_prepared_on DATETIME NOT NULL,
   s_edited_by CHAR (10),
   dt_edited_on DATETIME,
   s_printed_by CHAR (10),
   dt_printed_on DATETIME,
   s_closed_by CHAR (10),
   dt_closed_on DATETIME,
   dt_approved_on DATETIME,
   s_approved_by CHAR (10),
 PRIMARY KEY (s_guest_registration_form_number)
);

CREATE TABLE guest_registration_room_detail (
   s_guest_registration_form_number CHAR (10) NOT NULL,
   s_room_id CHAR (10) NOT NULL,
   d_room_cost DECIMAL (11,4) NOT NULL,
   s_remarks CHAR (80),
 PRIMARY KEY (s_guest_registration_form_number,s_room_id)
);

CREATE TABLE miscellaneous_expense (
   s_miscellaneous_expense_id CHAR (10) NOT NULL,
   s_miscellaneous_expense_name CHAR (20) NOT NULL,
   s_description CHAR (80),
   d_estimated_cost DECIMAL NOT NULL,
 PRIMARY KEY (s_miscellaneous_expense_id)
);

CREATE TABLE renovation_detail (
   s_renovation_form_number CHAR (10) NOT NULL,
   s_room_id CHAR (10) NOT NULL,
   s_remarks CHAR (80),
 PRIMARY KEY (s_renovation_form_number,s_room_id)
);

CREATE TABLE renovation_header (
   s_renovation_form_number CHAR (10) NOT NULL,
   c_status CHAR (1) NOT NULL,
   c_no_definite_date CHAR (1) NOT NULL,
   dt_from DATETIME,
   dt_to DATETIME,
   s_remarks CHAR (80),
   s_prepared_by CHAR (10) NOT NULL,
   dt_prepared_on DATETIME NOT NULL,
   s_approved_by CHAR (10),
   dt_approved_on DATETIME,
   s_cancelled_by CHAR (10),
   dt_cancelled_on DATETIME,
 PRIMARY KEY (s_renovation_form_number)
);

CREATE TABLE reservation_detail (
   s_reservation_form_number CHAR (10) NOT NULL,
   s_room_id CHAR (10) NOT NULL,
   d_room_cost DECIMAL (11,4) NOT NULL,
   s_remarks CHAR (80),
 PRIMARY KEY (s_reservation_form_number,s_room_id)
);

CREATE TABLE reservation_header (
   s_reservation_form_number CHAR (10) NOT NULL,
   s_guest_id CHAR (10) NOT NULL,
   dt_date DATETIME NOT NULL,
   c_status CHAR (1) NOT NULL,
   d_discount DECIMAL (5,2) NOT NULL,
   dt_tentative_arrival_date DATETIME NOT NULL,
   dt_tentative_arrival_time DATETIME NOT NULL,
   dt_tentative_departure_date DATETIME NOT NULL,
   dt_tentative_departure_time DATETIME NOT NULL,
   dt_confirmed_arrival_date DATETIME,
   dt_confirmed_arrival_time DATETIME,
   dt_confirmed_departure_date DATETIME,
   dt_confirmed_departure_time DATETIME,
   s_prepared_by CHAR (10) NOT NULL,
   dt_prepared_on DATETIME NOT NULL,
   s_edited_by CHAR (10),
   dt_edited_on DATETIME,
   s_confirmed_by CHAR (10),
   dt_confirmed_on DATETIME,
   s_cancelled_by CHAR (10),
   dt_cancelled_on DATETIME,
   s_closed_by CHAR (10),
   dt_closed_on DATETIME,
   s_remarks CHAR (80),
   d_deposit DECIMAL (11,4),
   s_payment_id CHAR (10) NOT NULL,
 PRIMARY KEY (s_reservation_form_number)
);

CREATE TABLE room_transaction (
   s_room_id CHAR (10) NOT NULL,
   s_form_number CHAR (10) NOT NULL,
   s_description CHAR (80),
   dt_date_from DATETIME NOT NULL,
   dt_date_to DATETIME NOT NULL,
   dt_time_from DATETIME NOT NULL,
   dt_time_to DATETIME NOT NULL,
   c_cancelled CHAR (1) NOT NULL,
 PRIMARY KEY (s_room_id,s_form_number)
);

CREATE TABLE room_master (
   s_room_id CHAR (10) NOT NULL,
   s_room_type_code CHAR (10) NOT NULL,
   c_room_status CHAR (1) NOT NULL,
   d_room_cost DECIMAL (11,4) NOT NULL,
   s_current_form_number CHAR (10),
   s_last_form_number CHAR (10),
 PRIMARY KEY (s_room_id)
);

CREATE TABLE payment_mode (
   s_payment_id CHAR (10) NOT NULL,
   s_payment_name CHAR (40) NOT NULL,
   s_description CHAR (80),
   s_address CHAR (80),
 PRIMARY KEY (s_payment_id)
);

CREATE TABLE room_type (
   s_room_type_code CHAR (10) NOT NULL,
   s_room_type_name CHAR (40) NOT NULL,
   d_room_type_cost DECIMAL (14,4) NOT NULL,
   s_description CHAR (80),
 PRIMARY KEY (s_room_type_code)
);

CREATE TABLE system_audit (
   dt_date DATETIME NOT NULL,
   s_form_number CHAR (10) NOT NULL,
   s_users_id CHAR (10) NOT NULL,
   dt_time DATETIME NOT NULL,
   s_transaction_description CHAR (256),
 PRIMARY KEY (dt_date,s_form_number,s_users_id)
);

CREATE TABLE system_messages (
   s_message_id CHAR (10) NOT NULL,
   s_message CHAR (256),
   s_icon_type CHAR (1),
   s_file CHAR (256),
 PRIMARY KEY (s_message_id)
);

CREATE TABLE users (
   s_users_id CHAR (10) NOT NULL,
   s_first_name CHAR (40),
   s_last_name CHAR (20),
   s_middle_name CHAR (20),
   s_password CHAR (10),
   s_user_group_id CHAR (10) NOT NULL,
   s_users_role_id CHAR (10) NOT NULL,
 PRIMARY KEY (s_users_id)
);

CREATE TABLE users_group (
   s_user_group_id CHAR (10) NOT NULL,
   s_group_description CHAR (20) NOT NULL,
 PRIMARY KEY (s_user_group_id)
);

CREATE TABLE users_role (
   s_users_role_id CHAR (10) NOT NULL,
   s_users_role_description CHAR (20) NOT NULL,
 PRIMARY KEY (s_users_role_id)
);


ALTER TABLE users ADD FOREIGN KEY (s_user_group_id)
   REFERENCES users_group;
ALTER TABLE users ADD FOREIGN KEY (s_users_role_id)
   REFERENCES users_role;
ALTER TABLE room_master ADD FOREIGN KEY (s_room_type_code)
   REFERENCES room_type;
ALTER TABLE guest_registration_header ADD FOREIGN KEY (s_payment_id)
   REFERENCES payment_mode;
ALTER TABLE system_audit ADD FOREIGN KEY (s_users_id)
   REFERENCES users;
ALTER TABLE room_transaction ADD FOREIGN KEY (s_room_id)
   REFERENCES room_master;
ALTER TABLE guest_registration_header ADD FOREIGN KEY (s_guest_id)
   REFERENCES guest;
ALTER TABLE reservation_detail ADD FOREIGN KEY (s_reservation_form_number)
   REFERENCES reservation_header;
ALTER TABLE reservation_detail ADD FOREIGN KEY (s_room_id)
   REFERENCES room_master;
ALTER TABLE guest_history ADD FOREIGN KEY (s_guest_id)
   REFERENCES guest;
ALTER TABLE renovation_detail ADD FOREIGN KEY (s_renovation_form_number)
   REFERENCES renovation_header;
ALTER TABLE renovation_detail ADD FOREIGN KEY (s_room_id)
   REFERENCES room_master;
ALTER TABLE guest_registration_room_detail ADD FOREIGN KEY (s_room_id)
   REFERENCES room_master;
ALTER TABLE room_utilization_detail ADD FOREIGN KEY (s_room_id)
   REFERENCES room_master;
ALTER TABLE reservation_header ADD FOREIGN KEY (s_guest_id)
   REFERENCES guest;
ALTER TABLE guest_registration_room_detail ADD FOREIGN KEY (s_guest_registration_form_number)
   REFERENCES guest_registration_header;
ALTER TABLE room_utilization_detail ADD FOREIGN KEY (s_room_utilization_form_number)
   REFERENCES room_utilization_header;
ALTER TABLE guest_registration_expense_detail ADD FOREIGN KEY (s_guest_registration_form_number)
   REFERENCES guest_registration_header;
ALTER TABLE guest_registration_expense_detail ADD FOREIGN KEY (s_miscellaneous_expense_id)
   REFERENCES miscellaneous_expense;
ALTER TABLE reservation_header ADD FOREIGN KEY (s_payment_id)
   REFERENCES payment_mode;