## Table Diagram Code

Table users {
  id VARCHAR(255) [pk]
  name_ VARCHAR(100)
  password varchar(255)
}

Table calendar {
  listing_id VARCHAR(30) [pk]
  date_ DATE [pk]
  available VARCHAR(1)
  price FLOAT
  adjusted_price FLOAT
  minimum_nights INT
  maximum_nights INT
}

Table listings_location {
  id VARCHAR(30) [pk, ref: >listings_review_score.id] 
  name_ VARCHAR(255)
  host_id VARCHAR(50)
  neighbourhood_group VARCHAR(50)
  neighbourhood VARCHAR(50)
  latitude FLOAT
  longitude FLOAT
}

Table listings_detail {
  id VARCHAR(30) [pk]
  name_ VARCHAR(50)
  property_type VARCHAR(50)
  room_type VARCHAR(50)
  accommodates INT
  bedrooms INT
  beds INT
  amenities VARCHAR(2500)
}

Table review_detail {
  listing_id VARCHAR(30) 
  id VARCHAR(30) [pk]
  date_ DATE
  reviewer_id VARCHAR(255)
  reviewer_name VARCHAR(255)
  comments VARCHAR(2500)
}

Table host_detail {
  host_id VARCHAR(50) [pk]
  host_name VARCHAR(50)
  host_about VARCHAR(7500)
  host_response_time VARCHAR(50)
  host_response_rate FLOAT
  host_identity_verified VARCHAR(1)
  host_is_superhost VARCHAR(1)
}

Table listings_review_score {
  id VARCHAR(30) [pk]
  number_of_reviews INT
  review_scores_rating FLOAT
  review_scores_accuracy FLOAT
  review_scores_cleanliness FLOAT
  review_scores_checkin FLOAT
  review_scores_communication FLOAT
  review_scores_location FLOAT
  review_scores_value FLOAT
}
Ref: users.id > review_detail.reviewer_id
Ref: listings_location.id > review_detail.listing_id
Ref: listings_location.id > calendar.listing_id
Ref: listings_location.host_id > host_detail.host_id
Ref: listings_location.id > listings_detail.id
Ref: review_detail.listing_id > listings_review_score.id

## FlowChart

總流程：

flowchart TD
pg_login{1.Login/Signup Page}
pg_login --> |不管有沒有帳號，輸入帳密|pg_main
pg_main{2.Main Page}
pg_general_review_main{3.Modify Review:顯示過往評論紀錄頁面}
    pg_edit{4.修改評論頁面}
    pg_general_review_main --> 點選特定留言 --> |我要修改|pg_edit
    點選特定留言 --> |刪除留言|點選特定留言
    
    
pg_search_main{5.Search Hotel：查住宿}
    pg_search_district{6.Hotel Page: price、rating}
    id_search_detailed_info{7.AirBnB詳細資訊}
    id_addcomment{8.留評論}
    id_search_place([城區])
    id_search_time([居住時間])
    id_search_room([房型])
    id_search_people([人數])
    id_search_click([點擊特定AirBnB])
    pg_search_main --> |輸入|id_search_place --> pg_search_district --> |若對某AirBnB有興趣|id_search_click --> id_search_detailed_info --> id_addcomment
    pg_search_main --> |輸入|id_search_time --> pg_search_district
    pg_search_main --> |輸入|id_search_room --> pg_search_district
    pg_search_main --> |輸入|id_search_people --> pg_search_district
    
    pg_main --> pg_search_main
    pg_main --> pg_general_review_main

## Loading Data into Table FAQ

大致Review Column 骨架：

CREATE TABLE hotel_reviews (
    listing_id INT,
    id INT,
    date DATE,
    reviewer_id INT,
    reviewer_name VARCHAR(255),
    comments TEXT
);

1. 逗號問題：如何規避評論區裡面的 ","，以防創造Table時發生錯誤？

LOAD DATA INFILE '/path/to/your/csvfile.csv' 
INTO TABLE hotel_reviews
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(listing_id, id, date, reviewer_id, reviewer_name, comments);

:::info

:::

2. ```</br>``` 問題，如何過濾Html的換行字元？

A. 換成SQL的換行字元

UPDATE hotel_reviews 
SET comments = REPLACE(comments, '</br>', '\n');

B. 全部拿掉

UPDATE hotel_reviews 
SET comments = REPLACE(REPLACE(comments, '</br>', ''), '<br/>', '');

3. 選擇性的從母表中建立子表

LOAD DATA INFILE '/path/to/your/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, @dummy, email, @dummy);

## Loading Data Record
==.csv直接使用AirBNB New York City's file==
==建表卡很久是正常現象==

1. calendar
3. listings_location
5. listings_detail
4. review_detail
==想將名字修正成 review_detail==

5. host_detail
==varchar(1) 因為內部為t/f==

6. listings_review_score
7. users

## Create Table Command

CREATE TABLE users (
  id varchar(255), 
  name_ varchar(100),
  password varchar(255),
  PRIMARY KEY (id)
);

CREATE TABLE calendar (
    listing_id varchar(30),
    date_ date,
    available varchar(1),
    price float,
    adjusted_price float,
    minimum_nights int,
    maximum_nights int,
    PRIMARY KEY (listing_id, date_)
);
LOAD DATA LOCAL INFILE '<your-path-here>/calendar.csv'  
INTO TABLE calendar
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES 
(listing_id, date_, available, @price, @adjusted_price, minimum_nights, maximum_nights)
SET
    date_ = date_,
    available = available,
    price = REPLACE(REPLACE(@price, '$', ''), ',', '') + 0,  
    adjusted_price = REPLACE(REPLACE(@adjusted_price, '$', ''), ',', '') + 0,
    minimum_nights = minimum_nights, 
    maximum_nights = maximum_nights;    


CREATE TABLE listings_location (
    id varchar(30),
    name_ varchar(255),
    host_id varchar(50),
    neighbourhood_group varchar(50),
    neighbourhood varchar(50),
    latitude float,
    longitude float,
    PRIMARY KEY(id)
);
LOAD DATA LOCAL INFILE './listings.csv'
INTO TABLE listings_location
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name_, host_id, @dummy, neighbourhood_group, neighbourhood, latitude, longitude, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy);

CREATE TABLE listings_detail (
    id varchar(30),
    name_ varchar(50),
    property_type varchar(50),
    room_type varchar(50),
    accommodates int,
    bedrooms int,
    beds int,
    amenities varchar(2500),
    PRIMARY KEY (id)
);
LOAD DATA LOCAL INFILE './listings_detailed.csv'
INTO TABLE listings_detail
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, @dummy, @dummy,@dummy,@dummy,name_,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,property_type,room_type,accommodates,@dummy,@dummy,bedrooms,beds,amenities,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);
CREATE TABLE review_detail (
listing_id varchar(30),
id varchar(30),
date_ date,
reviewer_id varchar(30),
reviewer_name varchar(255),
comments varchar(2500),
PRIMARY KEY(listing_id, id)
);
LOAD DATA LOCAL INFILE './reviews_detailed.csv'
INTO TABLE review_detail
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(listing_id, id, date_, reviewer_id, reviewer_name, comments);
UPDATE review_detail
SET comments = REPLACE(REPLACE(comments, '</br>', ''), '<br/>', '');

CREATE TABLE host_detail (
host_id varchar(50),
host_name varchar(50),
host_about varchar(7500),
host_response_time varchar(50),
host_response_rate float,
host_is_superhost varchar(1),
host_identity_verified varchar(1),
PRIMARY KEY (host_id)
);
LOAD DATA LOCAL INFILE '/var/lib/mysql-files/listings_detailed.csv'
INTO TABLE host_detail
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, host_id, @dummy, host_name, @dummy, @dummy, host_about, host_response_time, host_response_rate, @dummy, host_is_superhost, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, host_identity_verified, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy);


CREATE TABLE listings_review_score (
    id varchar(30),
    number_of_reviews int,
    review_scores_rating float,
    review_scores_accuracy float,
    review_scores_cleanliness float,
    review_scores_checkin float,
    review_scores_communication float,
    review_scores_location float,
    review_scores_value float,
    PRIMARY KEY(id)
    );

    LOAD DATA LOCAL INFILE '/var/lib/mysql-files/listings_detailed.csv'
    INTO TABLE listings_review_score
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (id, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, number_of_reviews, @dummy, @dummy, @dummy, @dummy, review_scores_rating, review_scores_accuracy, review_scores_cleanliness, review_scores_checkin, review_scores_communication, review_scores_location, review_scores_value, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy, @dummy);

### Python Code to clean the data

# Import necessary library
import pandas as pd

# Define file paths
reviews_detailed_path = '/usr/local/var/mysql/Final_Project/reviews_detailed.csv'
listings_path = '/usr/local/var/mysql/Final_Project/listings.csv'
listings_detailed_path = '/usr/local/var/mysql/Final_Project/listings_detailed.csv'
calendar_detailed_path = '/usr/local/var/mysql/Final_Project/calendar.csv'
# Define a cleaning function
def clean_dataframe(df):
# Strip spaces from string columns and fill missing values
df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x).fillna('Unknown')

# Remove non-alphanumeric characters in string columns
for col in df.select_dtypes(include=['object']).columns:
df[col] = df[col].str.replace(r'[^a-zA-Z0-9 .,]', '', regex=True)

# Drop duplicates
return df.drop_duplicates()

# Read and clean each file
cleaned_reviews_detailed = clean_dataframe(pd.read_csv(reviews_detailed_path))
cleaned_listings = clean_dataframe(pd.read_csv(listings_path))
cleaned_listings_detailed = clean_dataframe(pd.read_csv(listings_detailed_path))
cleaned_calendar = clean_dataframe(pd.read_csv(calendar_detailed_path))

# Save cleaned files
cleaned_reviews_detailed_path = '/usr/local/var/mysql/Final_Project/cleaned_reviews_detailed.csv'
cleaned_listings_path = '/usr/local/var/mysql/Final_Project/cleaned_listings.csv'
cleaned_listings_detailed_path = '/usr/local/var/mysql/Final_Project/cleaned_listings_detailed.csv'
cleaned_calendar_detailed_path = '/usr/local/var/mysql/Final_Project/cleaned_calendar.csv'
cleaned_reviews_detailed.to_csv(cleaned_reviews_detailed_path, index=False)
cleaned_listings.to_csv(cleaned_listings_path, index=False)
cleaned_listings_detailed.to_csv(cleaned_listings_detailed_path, index=False)
cleaned_calendar.to_csv(cleaned_calendar_detailed_path, index =False)
(cleaned_reviews_detailed_path, cleaned_listings_path, cleaned_listings_detailed_path)

:::warning
**Create Table FAQs** 

1. 沒有載入資料的權限
* 修改mySQL 的 Config檔案，加上

[mysqld]
local-infile=1

* 登入時，改用這行指令：

mysql --local-infile=1 -u <username> -p

* code

SET global local_infile = true;
SHOW global variables LIKE'local_infile';
QUIT
sudo mysql --local_infile=1 -u root
drop database final_project;
create database final_project;
use final_project;
source create_table.sql;

2. 載入資料時，MySQL找不到位置
修改Create Table的Code，將```./```改成載入檔案的```path```
:::
    
## Webpage
    
* main_page

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Main Page</title>
<style>
body {
font-family: Arial, sans-serif;
text-align: center;
margin: 0;
padding: 20px;
background-color: #f0f0f0;
}
.container {
display: flex;
justify-content: space-around;
margin-top: 20px;
}
.box {
border: 1px solid #000;
padding: 20px;
width: 40%;
background-color: white;
box-shadow: 0 2px 10px rgba(0, 0, 0, 0.1);
}
button {
padding: 10px 20px;
font-size: 16px;
cursor: pointer;
}
</style>
</head>
<body>
<h1>Start Page</h1>
<p>Welcome! Please choose which function you want to use.</p>
<div class="container">
<div class="box">
<h2>Find accommodation in New York City</h2>
<button>start 🔍</button>
</div>
<div class="box">
<h2>Comments</h2>
<ul>
<li>Add comments</li>
<li>Update comments</li>
<li>Delete comments</li>
</ul>
<button>start 🔍</button>
</div>
</div>
</body>
</html>