SQL_DB_RDBM_2025_09_04.sql

-- DB_MART_SET_2

-- #1
-- Table: Guest (user) account and profile  

DROP TABLE IF EXISTS GuestAccount;
CREATE TABLE IF NOT EXISTS GuestAccount 
(
    guest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    cust_number VARCHAR(20) UNIQUE NOT NULL,

 -- Personal information (selected) 
    first_name VARCHAR(55) NOT NULL,
    last_name VARCHAR(55) NOT NULL,
  	guest_profile_name VARCHAR(125),
    email VARCHAR(125) UNIQUE NOT NULL,
    phone VARCHAR(30) NOT NULL,
    
-- Profile user information	
   guest_profile_data TEXT ,
  
-- Status of important entries:

-- IDENTITY VERIFICATION FLAGS 
-- VRF / PND / NOT  =  VERIFIED / PENDING / NOT VIRIFIED
--  ident_verif_status VARCHAR(3) DEFAULT 'NOT',

  identity_verified BOOL DEFAULT FALSE,   
  
-- AVAILABLE ADDRESSES FLAGS:
-- R/P/B/N  = RESIDENTIAL / POSTAL / BILLING / NONE 
-- 'RPB' CAN BE USED WITHIN THE SAME COLUMN   
   avail_addresses VARCHAR(3) DEFAULT 'N',


-- Account information (metadata):
   is_host BOOL DEFAULT FALSE, 

-- ACCOUNT STATUS FLAGS: 
-- ACT / PND / TRM  = ACTIVE / PENDING / TERMINATED
   account_status VARCHAR(3) DEFAULT 'ACT',  	

   ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
   
   DEFAULT CHARSET=latin1; 

--  CHECKING THE RESULT 
--  MariaDB
DESCRIBE GuestAccount ;
SELECT CURRENT_TIMESTAMP ;

-- #1.2
-- inserting data

INSERT INTO GuestAccount
(cust_number, first_name, last_name,
guest_profile_name, email, phone,   
guest_profile_data, identity_verified, avail_addresses, is_host,
account_status, ts_created, ts_updated)   
VALUES 
('01-USR_IT', 'Paolo', 'F.',
'Paolo', '01@email.it', '+3977701', 
'• I like travelling and hosting • Speaks: English, Italian, Spanish • Lives in: Italy, Anzio',
1, 'RPB', 1, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('02-USR_IT', 'Emilia', 'C.',
'Emy', '02@email.it', '+3977702', 
'• Hello Everyone! • Speaks: English, Italian • Lives in: Italy, Anzio',
1, 'RPB', 1, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('03-USR_IT', 'Vincenzo', 'S.',
'Vincenzo', '03@email.it', '+3977703', 
'• A retired architect • Speaks: English, Italian • Lives in: Italy, Nettuno',
1, 'RPB', 1, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('04-USR-IT', 'Daniela', 'A.',
'Daniela', '04@email.it', '+3977704', 
'• I am also host • Speaks: English, Italian • Lives in: Italy, Nettuno',
1, 'RPB', 1, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('05-USR-IT', 'Massimo', 'Z.',
'Massimo', '05@email.it', '+3977705', 
'• Healthy lifestyle and positive thinking • Speaks: English, Italian, German, Turkish • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ) ,

('06-USR-IT', 'Leon', 'Z.',
'Leon', '06@email.it', '+3977706', 
'• Speaks: English, Italian, German, French • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('07-USR-IT', 'Enrico', 'M.',
'Enrico', '07@email.it', '+3977707', 
'• Speaks: English, Italian • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('08-USR-IT', 'Lucia', 'L.',
'Lucia', '08@email.it', '+3977708', 
'• Speaks: English, Italian • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('09-USR-IT', 'Alessandro', 'F.-P.',
'Alessandro', '09@email.it', '+3977709', 
'• Speaks: English, Italian, Franch, German • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('10-USR-IT', 'Mimmo', 'S.',
'Mimmo', '10@email.it', '+3977710', 
'• Speaks: English, Italian • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('11-USR-IT', 'Laura', 'C.',
'Laura', '11@email.it', '+3977711', 
'• Speaks: English, Italian • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('12-USR-IT', 'Vittorio', 'T.',
'Vittorio', '12@email.it', '+3977712', 
'• Speaks: English, Italian • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('13-USR-IT', 'Julian', 'K.',
'Julian', '13@email.it', '+3977713', 
'• Speaks: English, Italian, Franch, German • Lives in: Italy, Rome',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ), 

('14-USR-IT', 'Isabella', 'G.',
'Isabella', '14@email.it', '+3977714', 
'• Speaks: Spanish, Italian • Lives in: Terni, Italy, ',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('15-USR-IT', 'Francesco', 'M.',
'Francesco', '15@email.it', '+3977715', 
'• Speaks: English, Italian • Lives in: Rieti, Italy, ',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('16-USR-IT', 'Adriano', 'C.',
'Adriano', '16@email.it', '+3977716', 
'• Speaks: English, Italian • Lives in: Tivoli, Italy, ',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('17-USR-IT', 'Fabio', 'F.',
'Fabio', '17@email.it', '+3977717', 
'• Speaks: English, Italian, Russian • Lives in: Milan, Italy, ',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('18-USR-IT', 'Stefania', 'S.',
'Stefania', '18@email.it', '+3977718', 
'• Speaks: Italian • Lives in: Perugia, Italy, ',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('19-USR-IT', 'Andeas', 'D.',
'Andeas ', '19@email.ch', '+4177719', 
'• Speaks: English, German, Feench • Lives in: Basel, Switzeland',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

('20-USR-IT', 'Anton', 'W.',
' Anton', '20@email.de', '+4977720', 
'• Speaks: English, Italian, German, Russian  • Lives in: Frankfurt am Main, Geramny',
1, 'RB', 0, 'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' )

;

SELECT * FROM  GuestAccount; 
SELECT CURRENT_TIMESTAMP ;

-- #2
-- Table: Host account and profile  

DROP TABLE IF EXISTS HostAccount;
CREATE TABLE IF NOT EXISTS HostAccount
(
  host_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , 
  -- PARENT GUEST_ID 
  
  guest_id INT NOT NULL,
  FOREIGN KEY (guest_id) REFERENCES GuestAccount(guest_id),
 
--  host_cust_number VARCHAR(20) UNIQUE NOT NULL,
   host_profile_name VARCHAR(125),  
  
-- ALL PERSONAL / BUSINESS  (MASTER) DATA ARE AVAILABLE VIA PARENT GUEST ACCOUNT

-- Profile host information	
   host_profile_data TEXT ,
 
-- ACCOUNT STATUS FLAGS: 
-- ACT / PND / TRM  = ACTIVE / PENDING / TERMINATED  
  account_status VARCHAR(3) DEFAULT 'ACT' ,
  
  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

DEFAULT CHARSET=latin1;   

DESCRIBE HostAccount ;
SELECT CURRENT_TIMESTAMP ;

-- #2.2
-- inserting data  

INSERT INTO HostAccount
(guest_id, host_profile_name, host_profile_data, 
account_status, ts_created, ts_updated)   
VALUES 
(1, 'Paolo and Sarah, the Hosts', 
'• Speak(s): English, Italian, Spanish, French • Live(s) in: Italy, Anzio',
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

(2, 'Emy', 
 'I am hoting several nice apartments in Nettuno • Speak(s): English, Italian • Live(s) in: Italy, Nettuno',
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55' ),

(3, 'Vincenzo', 
 '• Speak(s): English, Italian • Live(s) in: Italy, Anzio',
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55'),

(4, 'Daniela', 
 '• Speak(s): English, Italian, Spanish • Live(s) in: Italy, Anzio',
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55')
;

SELECT * FROM  HostAccount; 
SELECT CURRENT_TIMESTAMP ;

-- #3
-- Table: Listing 
DROP TABLE IF EXISTS Listing;
CREATE TABLE IF NOT EXISTS Listing 
(
 listing_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , 
  
  -- PARENT HOST_ID 
  host_id INT NOT NULL ,
  FOREIGN KEY (host_id) REFERENCES HostAccount(host_id),
  
 listing_name VARCHAR(125),

-- ACCOMODATION TYPE:
-- APARTM / ROOM = ENTIRE APARTMENTS (HOMES) / ROOMS ETC 
  accomod_type VARCHAR(55) DEFAULT  'APARTM',

  listing_descript TEXT,

-- SPECIFIC FEATURES IMPORTANT FOR ME 

num_2bed_rooms INT  UNSIGNED ,
num_1bed_rooms INT UNSIGNED ,
total_accomm_size_qm DECIMAL (6,2) ,

foot_path_beach_km DECIMAL (4,2),
foot_path_shop_km DECIMAL (4,2),
foot_path_trainst_km DECIMAL (4,2) ,

-- LISTING SATUS FLAGS:
--  ACT / PND / UNL = ACTIVE / PENDING / UNLISTED  
 listing_status VARCHAR(3) DEFAULT 'ACT',
  
  
  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

DEFAULT CHARSET=latin1; 

DESCRIBE Listing ;
SELECT CURRENT_TIMESTAMP ;

-- #3.2
-- Inseting data

INSERT INTO Listing
(host_id, listing_name, 
accomod_type, listing_descript,
num_2bed_rooms, num_1bed_rooms, total_accomm_size_qm,
foot_path_beach_km, foot_path_shop_km, foot_path_trainst_km,
listing_status, ts_created, ts_updated)   
VALUES 
(1, 'B&B Piccolo Rifugio. APP. 1', 'APARTM',
'• Country: Italy • City: Anzio • City area(s): Marechiaro • Geographic area(s): Litorale Sud, Litorale laziale',
1, 1, 48.0, 0.8, 1.6, 0.01,
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55'),

(1, 'B&B Piccolo Rifugio. APP. 2', 'APARTM',
'• Country: Italy • City: Anzio • City area(s): Marechiaro • Geographic area(s): Litorale Sud, Litorale laziale',
1, 0, 20.0, 0.8, 1.6, 0.01,
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55'), 

(1, 'B&B Piccolo Rifugio. R. 1', 'ROOM',
'• Country: Italy • City: Anzio • City area(s): Marechiaro • Geographic area(s): Litorale Sud, Litorale laziale',
1, 0, 14.0, 0.8, 1.6, 0.01,
'PND', '2015-08-19 23:54:55', '2025-08-19 23:54:55'),

(2, 'Modern spacious 3 rooms apartment Centre, via Sangallo', 'APARTM',
'• Country: Italy • City: Nettuno • City area(s): Centre • Geographic area(s): Litorale Sud, Litorale laziale',
2, 0, 92.0, 0.5, 0.3, 0.5,
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55'), 

(2, 'Modern spacious 2 rooms apartment, Centre, via Sangallo', 'APARTM',
'• Country: Italy • City: Nettuno • City area(s): Centre • Geographic area(s): Litorale Sud, Litorale laziale',
1, 0, 62.0, 0.5, 0.3, 0.5,
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55'),

(3, 'Classic 3 rooms 2 bedrooms apartment in historical building and centre (marina/Piazza Pia)', 'APARTM',
'• Country: Italy • City: Anzio • City area(s): Centre • Geographic area(s): Litorale Sud, Litorale laziale',
2, 0, 106, 0.01, 0.1, 0.5,
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55'),

(4, 'Luxary 4 rooms apartment between Anzio and Nettuno, sea frontline, via Gramsci', 'APARTM',
'• Country: Italy • City: Nettuno • City area(s): Villa Borghese • Geographic area(s): Litorale Sud, Litorale laziale',
3, 0, 158, 0.01, 0.5, 1.6,
'ACT', '2015-08-19 23:54:55', '2025-08-19 23:54:55')
;

SELECT * FROM  Listing ; 
SELECT CURRENT_TIMESTAMP ;

-- #4 
 -- Table:  Adresses (Italy). 
 -- Includes customer addresses of all types (residential, postal, billing) and listtings  
 -- shall be address_id > address_it_id

DROP TABLE IF EXISTS AddressIT;
CREATE TABLE IF NOT EXISTS AddressIT
(
	address_id INT NOT NULL  AUTO_INCREMENT PRIMARY KEY,

	street VARCHAR(125) NOT NULL,

-- General desription and numbering of the house/apartment/room 
-- NULL value may correspond to the postbox-only adresses 
	unit_identifier VARCHAR(125),

--	province_code VARCHAR(2) NOT NULL,
	postal_code VARCHAR(6) NOT NULL , 
	
	city VARCHAR(125) NOT NULL,

    ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
	
DEFAULT CHARSET=latin1; 

DESCRIBE AddressIT ;
SELECT CURRENT_TIMESTAMP ;

INSERT INTO AddressIT
(street, unit_identifier, postal_code, city, ts_created, ts_updated )   
VALUES 
( 'Via Stella', '57', '00042', 'Anzio', '2025-08-19 23:54:55', '2025-08-19 23:54:55'  );

SELECT * FROM AddressIT ; 
SELECT CURRENT_TIMESTAMP ;

-- #5  
-- Table:  Bridging : GuestID - AddressID - AddressType

DROP TABLE IF EXISTS GuestID_AddrID_AddrType;
CREATE TABLE IF NOT EXISTS GuestID_AddrID_AddrType
 (
guest_address_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

guest_id INTEGER NOT NULL,
FOREIGN KEY (guest_id) REFERENCES GuestAccount(guest_id) ON DELETE CASCADE,

address_id INTEGER NOT NULL,
FOREIGN KEY (address_id) REFERENCES AddressIT(address_id),  

-- DESCRIPTION OF THE ADDRESS TYPE OF THE SPECIFIC GUEST    
is_resid_address BOOL DEFAULT TRUE ,
is_post_address BOOL DEFAULT FALSE , 
is_bill_address BOOL DEFAULT FALSE ,

ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  )

DEFAULT CHARSET=latin1; 

DESCRIBE GuestID_AddrID_AddrType ;
SELECT CURRENT_TIMESTAMP ;


-- #5.2   
-- Inserting data

INSERT INTO GuestID_AddrID_AddrType
(guest_id, address_id, 
is_resid_address, is_post_address, is_bill_address,
ts_created, ts_updated)   
VALUES 
(1, 1, 1, 1, 1, '2025-08-19 23:54:55', '2025-08-19 23:54:55' );

SELECT * FROM  GuestID_AddrID_AddrType; 
SELECT CURRENT_TIMESTAMP ;

-- #6 
-- Table:  Bridging : ListingID - AddressID 
DROP TABLE IF EXISTS ListingID_AddrID ;
CREATE TABLE IF NOT EXISTS ListingID_AddrID
 (
listing_address_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

listing_id INT NOT NULL,
FOREIGN KEY (listing_id) REFERENCES Listing(listing_id) ON DELETE CASCADE ,

address_id INT NOT NULL,
FOREIGN KEY (address_id) REFERENCES AddressIT(address_id) ON DELETE CASCADE ,  

ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  ) 

DEFAULT CHARSET=latin1; 

DESCRIBE ListingID_AddrID ;
SELECT CURRENT_TIMESTAMP ;

-- #6.2
-- Insert 

INSERT INTO ListingID_AddrID
(listing_id, address_id,
ts_created, ts_updated )   
VALUES 
( 1, 1, '2025-08-19 23:54:55', '2025-08-19 23:54:55' );

SELECT * FROM  ListingID_AddrID ; 
SELECT CURRENT_TIMESTAMP ;


-- #7
-- Table: Booking 

DROP TABLE IF EXISTS Booking ;
CREATE TABLE IF NOT EXISTS Booking
(
booking_id	INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,	

 listing_id INTEGER NOT NULL ,
 FOREIGN KEY (listing_id) REFERENCES Listing(listing_id),
  
 guest_id INTEGER NOT NULL,
 FOREIGN KEY (guest_id) REFERENCES GuestAccount(guest_id),

 booking_confirm_code VARCHAR(30) UNIQUE NOT NULL,

 check_in	DATE ,	
 check_out	DATE ,	
 number_guests INT UNSIGNED , 
 
  total_price DECIMAL(8, 2) ,
  
  currency  VARCHAR (3) , 

-- BOOKING PROCESS STATUS FLAGS
-- PEND / FAIL   / CONF  /  CNCL  = PENDING / FAILED  / CONFIRMED  /  CANCELLED
   booking_status  VARCHAR(4),

-- STAYING COMLPETION FLAGS
-- INDICATE ISSUES CAUSED BY GUEST'S STAYING  
-- P / C / D / S  = PENDING / CANCELLED / DISPUTE / SUCCESS  
-- 'DS' IN ONE COLUMN IS ALLOWED: MEANS SUCCESS AFTER DISPUTE CLOSED  
   stay_success_status VARCHAR (2)  DEFAULT 'P' ,
  
   ts_booked TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
   ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) 
   
DEFAULT CHARSET=latin1; 

DESCRIBE Booking ;
SELECT CURRENT_TIMESTAMP ;

-- #7.2
-- Inserting data
-- #7.2
-- Inserting data

INSERT INTO Booking
(listing_id, guest_id, booking_confirm_code,
check_in, check_out, number_guests, total_price, currency,
booking_status , ts_booked,  stay_success_status,  ts_created, ts_updated )   
VALUES 
(1, 11, '11-USR-IT-BK-001' ,
 '2025-08-25', '2025-08-31', 2, 525.0, 'EUR',
 'CONF' , '2025-08-19 23:54:55' ,  'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),
 
(1, 15, '15-USR-IT-BK-001' ,
 '2025-09-01',	'2025-09-07', 2, 525.0, 'EUR',
 'CONF' , '2025-08-19 23:54:55' , 'P', '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),
 
(1, 7, '07-USR-IT-BK-001' ,
 '2025-09-15' , '2025-09-21' , 2, 455.0, 'EUR',
 'CONF' , '2025-08-19 23:54:55' ,  'P', '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),

( 2, 18, ' 18-USR-IT-BK-001' ,
 '2025-08-25' , '2025-08-31' , 1, 420, 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),

( 2,  16, ' 16-USR-IT-BK-001 ' ,
 '2025-09-01' , '2025-09-07 ' , 2, 420, 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),

( 2, 8 , ' 08-USR-IT-BK-001 ' ,
 '2025-09-15' , '2025-09-21' , 1, 350, 'EUR',
'CONF' , '2025-08-19 23:54:55' ,  'P',   '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ), 
 
( 4, 9 , '09-USR-IT-BK-001  ' ,
 '2025-08-25' , ' 2025-08-31 ' , 4, 875, 'EUR',
'CONF' , '2025-08-19 23:54:55' ,  'P', '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),
( 4, 19 , ' 19-USR-IT-BK-001 ' ,
 '2025-09-01 ' , '2025-09-07 ' , 4, 875, 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ), 

( 4, 13, '13-USR-IT-BK-001 ' ,
 '2025-09-08' , '2025-09-14' , 3 , 770 , 'EUR',
'CNCL' , '2025-08-19 23:54:55' , 'C',  '2025-08-19 23:54:55' , '2025-08-22 20:00:02' ), 

( 5, 14, '14-USR-IT-BK-001 ' ,
 '2025-08-25' , '2025-08-31' , 2, 665 , 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P', '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),

( 5, 12, '12-USR-IT-BK-001 ' ,
 '2025-09-08' , '2025-09-14' , 2, 595 , 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),
( 5, 10, '10-USR-IT-BK-001 ' ,
 '2025-09-15' , '2025-09-21' , 2, 595, 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),

( 6, 17, '17-USR-IT-BK-001' ,
 '2025-09-01' , '2025-09-07' , 4 , 1036, 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P',  '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),
( 6, 20, '20-USR-IT-BK-001' ,
 '2025-09-15' , '2025-09-21' , 4 , 924 , 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P', '2025-08-19 23:54:55' , '2025-08-19 23:54:55' ),

( 7, 5, '05-USR-IT-BK-001' ,
 '2025-09-08' , '2025-09-14' ,  6,  1470 , 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'S', '2025-08-19 23:54:55' , '2025-09-14 12:00:00' ),
( 7, 6, '06-USR-IT-BK-001' ,
 '2025-09-15' , '025-09-21' ,  6,  1428, 'EUR',
'CONF' , '2025-08-19 23:54:55' , 'P', '2025-08-19 23:54:55' , '2025-08-19 23:54:55' );


-- #9.4
-- Reduced DBM
-- Table: calendar of price and availability of listing (Start Date / End Date )

-- DROP TABLE IF EXISTS Availability_And_Price_IT ;

DROP TABLE IF EXISTS Availability_And_Price ;
CREATE TABLE IF NOT EXISTS Availability_And_Price
(
  avail_and_price_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

    -- LISTING_ID 
listing_id INTEGER NOT NULL ,
FOREIGN KEY (listing_id) REFERENCES Listing(listing_id) ON DELETE CASCADE ,
 
start_date DATE, 
end_date DATE, 

  -- STATUS FLAGS: 
-- AVAIL  / BOOK / MAINT / PEND = AVAILABLE  / BOOKED / MAINTAINANCE / PENDING 
avail_status VARCHAR (5) , 
  
base_price_day  DECIMAL(8, 2) , 
  
  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
  
DEFAULT CHARSET=latin1;  

DESCRIBE  Availability_And_Price;
SELECT CURRENT_TIMESTAMP ;

-- #9.4.2
-- Inserting data

-- INSERT INTO Availability_And_Price

INSERT INTO Availability_And_Price
(listing_id, start_date, end_date, avail_status, base_price_day, ts_created, ts_updated)   
VALUES 
(1, '2025-08-25', '2025-08-31',  'BOOK', 75, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(1, '2025-09-01', '2025-09-07',  'BOOK', 75, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(1, '2025-09-08', '2025-09-14',  'AVAIL', 65, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(1, '2025-09-15', '2025-09-21',  'BOOK', 65, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),

(2, '2025-08-25', '2025-08-31',  'BOOK', 60, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(2, '2025-09-01', '2025-09-07',  'BOOK', 60, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(2, '2025-09-08', '2025-09-14',  'AVAIL', 50, '2025-08-19 23:54:55', '2025-08-19 23:54:55'),
(2, '2025-09-15', '2025-09-21',  'BOOK', 50, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),

(3, '2025-08-25', '2025-08-31',  'MAINT', 0, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(3, '2025-09-01', '2025-09-07',  'MAINT', 0, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(3, '2025-09-08', '2025-09-14',  'MAINT', 0, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(3, '2025-09-15', '2025-09-21',  'MAINT', 0, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),

(4, '2025-08-25', '2025-08-31',  'BOOK', 125, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(4, '2025-09-01', '2025-09-07',  'BOOK', 125 , '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(4, '2025-09-08', '2025-09-14',  'AVAIL', 110  , '2025-08-19 23:54:55', '2025-08-19 23:54:55'),
(4, '2025-09-15', '2025-09-21',  'AVAIL', 110, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),

(5, '2025-08-25', '2025-08-31',  'BOOK', 95, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(5, '2025-09-01', '2025-09-07',  'AVAIL', 95, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(5, '2025-09-08', '2025-09-14',  'BOOK', 85, '2025-08-19 23:54:55', '2025-08-19 23:54:55'),
(5, '2025-09-15', '2025-09-21',  'BOOK',  85, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),

(6, '2025-08-25', '2025-08-31',  'AVAIL', 148, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(6, '2025-09-01', '2025-09-07',  'BOOK', 148, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(6, '2025-09-08', '2025-09-14',  'AVAIL', 132, '2025-08-19 23:54:55', '2025-08-19 23:54:55'),
(6, '2025-09-15', '2025-09-21',  'BOOK', 132, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),

(7, '2025-08-25', '2025-08-31',  'AVAIL', 228, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(7, '2025-09-01', '2025-09-07',  'AVAIL', 216, '2025-08-19 23:54:55', '2025-08-19 23:54:55' ),
(7, '2025-09-08', '2025-09-14',  'BOOK', 210 , '2025-08-19 23:54:55', '2025-08-19 23:54:55'),
(7, '2025-09-15', '2025-09-21',  'BOOK', 208, '2025-08-19 23:54:55', '2025-08-19 23:54:55' )
;

SELECT * FROM  Availability_And_Price; 
SELECT CURRENT_TIMESTAMP ;


-- #8 
-- Table: Payment 
DROP TABLE IF EXISTS Payment ;
CREATE TABLE IF NOT EXISTS Payment
(
  payment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,	

  booking_id INTEGER NOT NULL ,
  FOREIGN KEY (booking_id) REFERENCES Booking(booking_id),
  
 receipt_id VARCHAR (30) UNIQUE NOT NULL ,
 amount_paid DECIMAL(8, 2),
 currency  VARCHAR (3) ,
   
  
-- PAYMENT TYPE FLAG: 
-- PAYM / RFND  = PAYMENT / REFUND
-- IN CASE OF REFUND THE SECOND SEPARATE payment_id / receipt_id ARE GENERATED 
   payment_type VARCHAR (4),

-- PAYMENT METHOD FLAG: 
-- CARD / PAYP / BANK = CARDS / PAYPAL / BANK TRANSFER 
   payment_method VARCHAR (4),
  

-- NO NEED FOR AIRBNB  !
-- PAYMENT STATUS  FLAG:
-- PEND / FAIL / SUCC = PENDING / FAILED  / SUCCESSFUL
-- RELEVANT FOR BOOKING CANCELLATIONS / REFUNDS AS BOOKING WITHOUT PRIOR PAYMENT IS NOT POSSIBLE
--   payment_status VARCHAR (4),
  
  
  transaction_id VARCHAR (30) UNIQUE NOT NULL,
  ts_paid TIMESTAMP,
 
  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) 
  
DEFAULT CHARSET=latin1; 

DESCRIBE Payment ;
SELECT CURRENT_TIMESTAMP ;


INSERT INTO Payment
( booking_id, receipt_id, amount_paid, currency,
 payment_type, payment_method, transaction_id, ts_paid,  
 ts_created, ts_updated)  
 VALUES 


( 15, '05-USR-IT-BK-001-PM-001', 1470.0, 'EUR',
 'PAYM' , 'CARD', '05-USR-IT-BK-001-TR-001', '2025-08-19 23:54:55',
 '2025-08-19 23:54:55', '2025-08-19 23:54:55') ,
 
 (9, '13-USR-IT-BK-001-PM-001', 770.0, 'EUR',
 'PAYM' , 'PAYP', '13-USR-IT-BK-001-TR-001', '2025-08-19 23:54:55',
 '2025-08-19 23:54:55', '2025-08-19 23:54:55') ,
 
  (9, '13-USR-IT-BK-001-PM-002', 770.0, 'EUR',
 'RFND' , 'PAYP', '13-USR-IT-BK-001-TR-002', '2025-08-22 20:00:02',
 '2025-08-22 20:00:02', '2025-08-22 20:00:02') ;
 

SELECT * FROM  Payment ; 
SELECT CURRENT_TIMESTAMP ;


-- #10 
-- Table: Review on Host by Guest

DROP TABLE IF EXISTS Review_On_Host ;
CREATE TABLE IF NOT EXISTS Review_On_Host
(
  review_on_host_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,	

   booking_id INTEGER NOT NULL ,
   FOREIGN KEY (booking_id) REFERENCES Booking(booking_id),

  num_stars_host INT , 
  guest_on_host VARCHAR (500) ,

  num_stars_listing INT ,  
  guest_on_listing VARCHAR (500) , 

  ts_reviewed TIMESTAMP ,

  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) 

DEFAULT CHARSET=latin1; 

DESCRIBE Review_On_Host ;
SELECT CURRENT_TIMESTAMP;  

INSERT INTO Review_On_Host
(booking_id, num_stars_host, guest_on_host, 
num_stars_listing, guest_on_listing,
ts_reviewed, ts_created, ts_updated)  
 VALUES 
(15, 
 5, 'Very positive communication experisence',
 5, 'Enjoyed the apartment, building and location',
'2025-09-15 12:00:00', '2025-09-15 12:00:00', '2025-09-15 12:00:00') ;

SELECT * FROM  Review_On_Host  ; 
SELECT CURRENT_TIMESTAMP ;

-- #11
-- Table: Reviews on Guest by Host

DROP TABLE IF EXISTS Review_On_Guest ;
CREATE TABLE IF NOT EXISTS Review_On_Guest
(
  review_on_guest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,	

  booking_id INTEGER NOT NULL ,
  FOREIGN KEY (booking_id) REFERENCES Booking(booking_id),

  host_on_guest VARCHAR (500) ,

  ts_reviewed TIMESTAMP ,

  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) 
  
DEFAULT CHARSET=latin1;   

DESCRIBE Review_On_Guest ;
SELECT CURRENT_TIMESTAMP ;

INSERT INTO Review_On_Guest
(booking_id, host_on_guest, 
ts_reviewed, ts_created, ts_updated)  
 VALUES 
(15, 'Polite, punctual, no issues with staying',
 '2025-09-15 23:54:55', '2025-09-15 23:54:55', '2025-09-15 23:54:55') ;

SELECT * FROM  Review_On_Guest  ; 
SELECT CURRENT_TIMESTAMP ;



-- #12 
--  Media table for listings 

DROP TABLE IF EXISTS Listing_Media ;
CREATE TABLE IF NOT EXISTS Listing_Media
(
 listing_media_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , 
  
 listing_id INTEGER NOT NULL ,
 FOREIGN KEY (listing_id) REFERENCES Listing(listing_id) ON DELETE CASCADE,

   url VARCHAR(250) ,
  
-- MEDIA TYPE: PHOTO / VIDEO  / SOC_MEDIA (SOCIAL MEDIA CONNECTION)s
  media_type VARCHAR(30) ,
  media_descr VARCHAR(500), 

  ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ts_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  )

DEFAULT CHARSET=latin1; 
  
DESCRIBE Listing_Media ;
SELECT CURRENT_TIMESTAMP ;


-- #12.2  
--  Insert 
INSERT INTO Listing_Media
(listing_id, url, media_type, media_descr, ts_created, ts_updated )   
VALUES 
( 1, 
'<a href="https://a0.muscache.com/im/pictures/64921979/3c7c1eb3_original.jpg?sw_cache=trips_images&im_w=960" target="_blank"> LISTING PICS </a>', 
 'JPG', 'MAIN PICTURE', '2025-08-19 23:54:55', '2025-08-19 23:54:55'),

( 1,
 '<a href="https://www.facebook.com/p/Piccolo-Rifugio-100069950574931" target="_blank">B&B Piccolo Rifugio</a>',
 'URL', 'FACEBOOK PAGE', '2025-08-19 23:54:55', '2025-08-19 23:54:55');
 
SELECT * FROM  Listing_Media ; 
SELECT CURRENT_TIMESTAMP ;