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


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) NULL,
   s_description CHAR (80) NOT NULL
)
go

CREATE UNIQUE INDEX IPK_room_utilization_detail ON room_utilization_detail (s_room_utilization_form_number ASC, s_room_id ASC)
go
ALTER TABLE room_utilization_detail ADD PRIMARY KEY (s_room_utilization_form_number,s_room_id)
go

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

CREATE UNIQUE INDEX IPK_room_utilization_header ON room_utilization_header (s_room_utilization_form_number ASC)
go
ALTER TABLE room_utilization_header ADD PRIMARY KEY (s_room_utilization_form_number)
go

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

CREATE UNIQUE INDEX IPK_guest ON guest (s_guest_id ASC)
go
ALTER TABLE guest ADD PRIMARY KEY (s_guest_id)
go

CREATE TABLE guest_history (
   s_form_number CHAR (10) NOT NULL,
   s_guest_id CHAR (10) NOT NULL,
   s_description CHAR (80) NULL,
   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
)
go

CREATE UNIQUE INDEX IPK_guest_history ON guest_history (s_form_number ASC, s_guest_id ASC)
go
ALTER TABLE guest_history ADD PRIMARY KEY (s_form_number,s_guest_id)
go

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) NULL
)
go

CREATE UNIQUE INDEX IPK_guest_registration_expense ON guest_registration_expense_detail (s_guest_registration_form_number ASC, s_miscellaneous_expense_id ASC)
go
ALTER TABLE guest_registration_expense_detail ADD PRIMARY KEY (s_guest_registration_form_number,s_miscellaneous_expense_id)
go

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 NULL,
   dt_actual_departure_time DATETIME NULL,
   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) NULL,
   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) NULL,
   dt_edited_on DATETIME NULL,
   s_printed_by CHAR (10) NULL,
   dt_printed_on DATETIME NULL,
   s_closed_by CHAR (10) NULL,
   dt_closed_on DATETIME NULL,
   dt_approved_on DATETIME NULL,
   s_approved_by CHAR (10) NULL
)
go

CREATE UNIQUE INDEX IPK_guest_registration_header ON guest_registration_header (s_guest_registration_form_number ASC)
go
ALTER TABLE guest_registration_header ADD PRIMARY KEY (s_guest_registration_form_number)
go

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) NULL
)
go

CREATE UNIQUE INDEX IPK_guest_registration_room_de ON guest_registration_room_detail (s_guest_registration_form_number ASC, s_room_id ASC)
go
ALTER TABLE guest_registration_room_detail ADD PRIMARY KEY (s_guest_registration_form_number,s_room_id)
go

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

CREATE UNIQUE INDEX IPK_miscellaneous_expense ON miscellaneous_expense (s_miscellaneous_expense_id ASC)
go
ALTER TABLE miscellaneous_expense ADD PRIMARY KEY (s_miscellaneous_expense_id)
go

CREATE TABLE renovation_detail (
   s_renovation_form_number CHAR (10) NOT NULL,
   s_room_id CHAR (10) NOT NULL,
   s_remarks CHAR (80) NULL
)
go

CREATE UNIQUE INDEX IPK_renovation_detail ON renovation_detail (s_renovation_form_number ASC, s_room_id ASC)
go
ALTER TABLE renovation_detail ADD PRIMARY KEY (s_renovation_form_number,s_room_id)
go

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 NULL,
   dt_to DATETIME NULL,
   s_remarks CHAR (80) NULL,
   s_prepared_by CHAR (10) NOT NULL,
   dt_prepared_on DATETIME NOT NULL,
   s_approved_by CHAR (10) NULL,
   dt_approved_on DATETIME NULL,
   s_cancelled_by CHAR (10) NULL,
   dt_cancelled_on DATETIME NULL
)
go

CREATE UNIQUE INDEX IPK_renovation_header ON renovation_header (s_renovation_form_number ASC)
go
ALTER TABLE renovation_header ADD PRIMARY KEY (s_renovation_form_number)
go

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) NULL
)
go

CREATE UNIQUE INDEX IPK_reservation_detail ON reservation_detail (s_reservation_form_number ASC, s_room_id ASC)
go
ALTER TABLE reservation_detail ADD PRIMARY KEY (s_reservation_form_number,s_room_id)
go

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 NULL,
   dt_confirmed_arrival_time DATETIME NULL,
   dt_confirmed_departure_date DATETIME NULL,
   dt_confirmed_departure_time DATETIME NULL,
   s_prepared_by CHAR (10) NOT NULL,
   dt_prepared_on DATETIME NOT NULL,
   s_edited_by CHAR (10) NULL,
   dt_edited_on DATETIME NULL,
   s_confirmed_by CHAR (10) NULL,
   dt_confirmed_on DATETIME NULL,
   s_cancelled_by CHAR (10) NULL,
   dt_cancelled_on DATETIME NULL,
   s_closed_by CHAR (10) NULL,
   dt_closed_on DATETIME NULL,
   s_remarks CHAR (80) NULL,
   d_deposit DECIMAL (11,4) NULL,
   s_payment_id CHAR (10) NOT NULL
)
go

CREATE UNIQUE INDEX IPK_reservation_header ON reservation_header (s_reservation_form_number ASC)
go
ALTER TABLE reservation_header ADD PRIMARY KEY (s_reservation_form_number)
go

CREATE TABLE room_transaction (
   s_room_id CHAR (10) NOT NULL,
   s_form_number CHAR (10) NOT NULL,
   s_description CHAR (80) NULL,
   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
)
go

CREATE UNIQUE INDEX IPK_room_transaction ON room_transaction (s_room_id ASC, s_form_number ASC)
go
ALTER TABLE room_transaction ADD PRIMARY KEY (s_room_id,s_form_number)
go

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) NULL,
   s_last_form_number CHAR (10) NULL
)
go

CREATE UNIQUE INDEX IPK_room_master ON room_master (s_room_id ASC)
go
ALTER TABLE room_master ADD PRIMARY KEY (s_room_id)
go

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

CREATE UNIQUE INDEX IPK_payment_mode ON payment_mode (s_payment_id ASC)
go
ALTER TABLE payment_mode ADD PRIMARY KEY (s_payment_id)
go

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) NULL
)
go

CREATE UNIQUE INDEX IPK_room_type ON room_type (s_room_type_code ASC)
go
ALTER TABLE room_type ADD PRIMARY KEY (s_room_type_code)
go

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) NULL
)
go

CREATE UNIQUE INDEX IPK_system_audit ON system_audit (dt_date ASC, s_form_number ASC, s_users_id ASC)
go
ALTER TABLE system_audit ADD PRIMARY KEY (dt_date,s_form_number,s_users_id)
go

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

CREATE UNIQUE INDEX IPK_system_messages ON system_messages (s_message_id ASC)
go
ALTER TABLE system_messages ADD PRIMARY KEY (s_message_id)
go

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

CREATE UNIQUE INDEX IPK_users ON users (s_users_id ASC)
go
ALTER TABLE users ADD PRIMARY KEY (s_users_id)
go

CREATE TABLE users_group (
   s_user_group_id CHAR (10) NOT NULL,
   s_group_description CHAR (20) NOT NULL
)
go

CREATE UNIQUE INDEX IPK_users_group ON users_group (s_user_group_id ASC)
go
ALTER TABLE users_group ADD PRIMARY KEY (s_user_group_id)
go

CREATE TABLE users_role (
   s_users_role_id CHAR (10) NOT NULL,
   s_users_role_description CHAR (20) NOT NULL
)
go

CREATE UNIQUE INDEX IPK_users_role ON users_role (s_users_role_id ASC)
go
ALTER TABLE users_role ADD PRIMARY KEY (s_users_role_id)
go


ALTER TABLE users ADD CONSTRAINT REL_1 FOREIGN KEY (s_user_group_id)
   REFERENCES users_group
go
ALTER TABLE users ADD CONSTRAINT REL_2 FOREIGN KEY (s_users_role_id)
   REFERENCES users_role
go
ALTER TABLE room_master ADD CONSTRAINT REL_3 FOREIGN KEY (s_room_type_code)
   REFERENCES room_type
go
ALTER TABLE guest_registration_header ADD CONSTRAINT REL_28 FOREIGN KEY (s_payment_id)
   REFERENCES payment_mode
go
ALTER TABLE system_audit ADD CONSTRAINT REL_7 FOREIGN KEY (s_users_id)
   REFERENCES users
go
ALTER TABLE room_transaction ADD CONSTRAINT REL_8 FOREIGN KEY (s_room_id)
   REFERENCES room_master
go
ALTER TABLE guest_registration_header ADD CONSTRAINT REL_20 FOREIGN KEY (s_guest_id)
   REFERENCES guest
go
ALTER TABLE reservation_detail ADD CONSTRAINT REL_11 FOREIGN KEY (s_reservation_form_number)
   REFERENCES reservation_header
go
ALTER TABLE reservation_detail ADD CONSTRAINT REL_12 FOREIGN KEY (s_room_id)
   REFERENCES room_master
go
ALTER TABLE guest_history ADD CONSTRAINT REL_14 FOREIGN KEY (s_guest_id)
   REFERENCES guest
go
ALTER TABLE renovation_detail ADD CONSTRAINT REL_15 FOREIGN KEY (s_renovation_form_number)
   REFERENCES renovation_header
go
ALTER TABLE renovation_detail ADD CONSTRAINT REL_16 FOREIGN KEY (s_room_id)
   REFERENCES room_master
go
ALTER TABLE guest_registration_room_detail ADD CONSTRAINT REL_23 FOREIGN KEY (s_room_id)
   REFERENCES room_master
go
ALTER TABLE room_utilization_detail ADD CONSTRAINT REL_18 FOREIGN KEY (s_room_id)
   REFERENCES room_master
go
ALTER TABLE reservation_header ADD CONSTRAINT REL_21 FOREIGN KEY (s_guest_id)
   REFERENCES guest
go
ALTER TABLE guest_registration_room_detail ADD CONSTRAINT REL_22 FOREIGN KEY (s_guest_registration_form_number)
   REFERENCES guest_registration_header
go
ALTER TABLE room_utilization_detail ADD CONSTRAINT REL_24 FOREIGN KEY (s_room_utilization_form_number)
   REFERENCES room_utilization_header
go
ALTER TABLE guest_registration_expense_detail ADD CONSTRAINT REL_25 FOREIGN KEY (s_guest_registration_form_number)
   REFERENCES guest_registration_header
go
ALTER TABLE guest_registration_expense_detail ADD CONSTRAINT REL_26 FOREIGN KEY (s_miscellaneous_expense_id)
   REFERENCES miscellaneous_expense
go
ALTER TABLE reservation_header ADD CONSTRAINT REL_27 FOREIGN KEY (s_payment_id)
   REFERENCES payment_mode
go