49 lines
1.4 KiB
SQL
49 lines
1.4 KiB
SQL
-- Raw reviews storage
|
|
CREATE TABLE IF NOT EXISTS reviews (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
location_id VARCHAR(100),
|
|
site VARCHAR(50),
|
|
review_id VARCHAR(255),
|
|
reviewer_name VARCHAR(255),
|
|
rating DECIMAL(2,1),
|
|
review_text TEXT,
|
|
review_date DATE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_review (site, review_id)
|
|
);
|
|
|
|
-- Daily aggregates for fast dashboard queries
|
|
CREATE TABLE IF NOT EXISTS review_aggregates (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
location_id VARCHAR(100),
|
|
site VARCHAR(50),
|
|
date DATE,
|
|
total_reviews INT,
|
|
avg_rating DECIMAL(3,2),
|
|
five_star INT DEFAULT 0,
|
|
four_star INT DEFAULT 0,
|
|
three_star INT DEFAULT 0,
|
|
two_star INT DEFAULT 0,
|
|
one_star INT DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_aggregate (location_id, site, date)
|
|
);
|
|
|
|
-- Latest snapshot for quick dashboard display
|
|
CREATE TABLE IF NOT EXISTS review_snapshot (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
location_id VARCHAR(100),
|
|
total_reviews INT,
|
|
avg_rating DECIMAL(3,2),
|
|
reviews_this_week INT,
|
|
reviews_this_month INT,
|
|
google_reviews INT,
|
|
google_avg DECIMAL(3,2),
|
|
yelp_reviews INT,
|
|
yelp_avg DECIMAL(3,2),
|
|
facebook_reviews INT,
|
|
facebook_avg DECIMAL(3,2),
|
|
last_synced TIMESTAMP,
|
|
UNIQUE KEY unique_snapshot (location_id)
|
|
);
|