270 lines
13 KiB
SQL
270 lines
13 KiB
SQL
-- ============================================================
|
|
-- Production database initialization for fuelprices_prod
|
|
--
|
|
-- Run in two steps:
|
|
-- 1. psql -h 192.168.1.204 -U postgres -c "CREATE DATABASE fuelprices_prod;"
|
|
-- 2. psql -h 192.168.1.204 -U postgres -d fuelprices_prod -f init_fuelprices_prod.sql
|
|
-- ============================================================
|
|
|
|
-- ---- Tables ------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
password TEXT NOT NULL,
|
|
created TIMESTAMPTZ,
|
|
email VARCHAR(255),
|
|
last_login TIMESTAMPTZ,
|
|
owner INTEGER
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_categories (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
clicks_total INTEGER DEFAULT 0,
|
|
total_companies INTEGER DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS company (
|
|
id SERIAL PRIMARY KEY,
|
|
active BOOLEAN DEFAULT true,
|
|
created DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
name VARCHAR(255) NOT NULL,
|
|
address VARCHAR(255),
|
|
town VARCHAR(255),
|
|
state VARCHAR(2),
|
|
phone VARCHAR(20),
|
|
owner_name VARCHAR(255),
|
|
owner_phone_number VARCHAR(20),
|
|
email VARCHAR(255),
|
|
user_id INTEGER
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS county (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
state VARCHAR(2) NOT NULL,
|
|
UNIQUE(name, state)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS listings (
|
|
id SERIAL PRIMARY KEY,
|
|
company_name VARCHAR(255) NOT NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
price_per_gallon DOUBLE PRECISION NOT NULL,
|
|
price_per_gallon_cash DOUBLE PRECISION,
|
|
note TEXT,
|
|
minimum_order INTEGER,
|
|
service BOOLEAN DEFAULT false,
|
|
bio_percent INTEGER NOT NULL,
|
|
phone VARCHAR(20),
|
|
online_ordering VARCHAR(20) NOT NULL DEFAULT 'none',
|
|
county_id INTEGER NOT NULL,
|
|
town VARCHAR(100),
|
|
url VARCHAR(255),
|
|
logo_url VARCHAR(255),
|
|
banner_url VARCHAR(255),
|
|
facebook_url VARCHAR(255),
|
|
instagram_url VARCHAR(255),
|
|
google_business_url VARCHAR(255),
|
|
user_id INTEGER NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
last_edited TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS oil_prices (
|
|
id SERIAL PRIMARY KEY,
|
|
state VARCHAR(100),
|
|
zone INTEGER,
|
|
name VARCHAR(255),
|
|
price DOUBLE PRECISION,
|
|
date VARCHAR(20),
|
|
scrapetimestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
company_id INTEGER,
|
|
county_id INTEGER,
|
|
phone VARCHAR(20),
|
|
url VARCHAR(500)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS stats_prices (
|
|
id SERIAL PRIMARY KEY,
|
|
state VARCHAR(2) NOT NULL,
|
|
price DOUBLE PRECISION NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_listings (
|
|
id SERIAL PRIMARY KEY,
|
|
company_name VARCHAR(255) NOT NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
twenty_four_hour BOOLEAN DEFAULT false,
|
|
emergency_service BOOLEAN DEFAULT false,
|
|
town VARCHAR(100),
|
|
county_id INTEGER NOT NULL,
|
|
phone VARCHAR(20),
|
|
website VARCHAR(255),
|
|
email VARCHAR(255),
|
|
description TEXT,
|
|
licensed_insured BOOLEAN DEFAULT false,
|
|
service_area VARCHAR(255),
|
|
years_experience INTEGER,
|
|
logo_url VARCHAR(255),
|
|
banner_url VARCHAR(255),
|
|
facebook_url VARCHAR(255),
|
|
instagram_url VARCHAR(255),
|
|
google_business_url VARCHAR(255),
|
|
user_id INTEGER NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
last_edited TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS subscriptions (
|
|
id SERIAL PRIMARY KEY,
|
|
company_id INTEGER NOT NULL UNIQUE,
|
|
trial_start DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
trial_end DATE NOT NULL DEFAULT (CURRENT_DATE + INTERVAL '1 year'),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'trial',
|
|
plan VARCHAR(50),
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS banners (
|
|
id SERIAL PRIMARY KEY,
|
|
message TEXT NOT NULL,
|
|
is_active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS listing_towns (
|
|
id SERIAL PRIMARY KEY,
|
|
listing_id INTEGER NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
|
|
town VARCHAR(100) NOT NULL,
|
|
UNIQUE(listing_id, town)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS service_listing_towns (
|
|
id SERIAL PRIMARY KEY,
|
|
service_listing_id INTEGER NOT NULL REFERENCES service_listings(id) ON DELETE CASCADE,
|
|
town VARCHAR(100) NOT NULL,
|
|
UNIQUE(service_listing_id, town)
|
|
);
|
|
|
|
-- ---- County seed data (all 6 New England states) -----------
|
|
|
|
INSERT INTO county (name, state) VALUES
|
|
-- Connecticut
|
|
('Fairfield', 'CT'),
|
|
('Hartford', 'CT'),
|
|
('Litchfield', 'CT'),
|
|
('Middlesex', 'CT'),
|
|
('New Haven', 'CT'),
|
|
('New London', 'CT'),
|
|
('Tolland', 'CT'),
|
|
('Windham', 'CT'),
|
|
-- Maine
|
|
('Androscoggin','ME'),
|
|
('Aroostook', 'ME'),
|
|
('Cumberland', 'ME'),
|
|
('Franklin', 'ME'),
|
|
('Hancock', 'ME'),
|
|
('Kennebec', 'ME'),
|
|
('Knox', 'ME'),
|
|
('Lincoln', 'ME'),
|
|
('Oxford', 'ME'),
|
|
('Penobscot', 'ME'),
|
|
('Piscataquis', 'ME'),
|
|
('Sagadahoc', 'ME'),
|
|
('Somerset', 'ME'),
|
|
('Waldo', 'ME'),
|
|
('Washington', 'ME'),
|
|
('York', 'ME'),
|
|
-- Massachusetts
|
|
('Barnstable', 'MA'),
|
|
('Berkshire', 'MA'),
|
|
('Bristol', 'MA'),
|
|
('Dukes', 'MA'),
|
|
('Essex', 'MA'),
|
|
('Franklin', 'MA'),
|
|
('Hampden', 'MA'),
|
|
('Hampshire', 'MA'),
|
|
('Middlesex', 'MA'),
|
|
('Nantucket', 'MA'),
|
|
('Norfolk', 'MA'),
|
|
('Plymouth', 'MA'),
|
|
('Suffolk', 'MA'),
|
|
('Worcester', 'MA'),
|
|
-- New Hampshire
|
|
('Belknap', 'NH'),
|
|
('Carroll', 'NH'),
|
|
('Cheshire', 'NH'),
|
|
('Coos', 'NH'),
|
|
('Grafton', 'NH'),
|
|
('Hillsborough','NH'),
|
|
('Merrimack', 'NH'),
|
|
('Rockingham', 'NH'),
|
|
('Strafford', 'NH'),
|
|
('Sullivan', 'NH'),
|
|
-- Rhode Island
|
|
('Bristol', 'RI'),
|
|
('Kent', 'RI'),
|
|
('Newport', 'RI'),
|
|
('Providence', 'RI'),
|
|
('Washington', 'RI'),
|
|
-- Vermont
|
|
('Addison', 'VT'),
|
|
('Bennington', 'VT'),
|
|
('Caledonia', 'VT'),
|
|
('Chittenden', 'VT'),
|
|
('Essex', 'VT'),
|
|
('Franklin', 'VT'),
|
|
('Grand Isle', 'VT'),
|
|
('Lamoille', 'VT'),
|
|
('Orange', 'VT'),
|
|
('Orleans', 'VT'),
|
|
('Rutland', 'VT'),
|
|
('Washington', 'VT'),
|
|
('Windham', 'VT'),
|
|
('Windsor', 'VT')
|
|
ON CONFLICT (name, state) DO NOTHING;
|
|
|
|
-- ---- Service categories seed data --------------------------
|
|
|
|
INSERT INTO service_categories (name, description, clicks_total, total_companies) VALUES
|
|
('Landscaping - Lawn care, tree trimming, and garden design', 'Professional landscaping services including lawn maintenance, tree trimming, garden design, and outdoor space enhancement.', 0, 0),
|
|
('Snowplowing - Snow removal from driveways and walkways', 'Reliable snow removal services to clear driveways, walkways, and parking areas during winter storms.', 0, 0),
|
|
('Roofing - Roof repairs, replacements, and inspections', 'Comprehensive roofing services including repairs, complete replacements, and routine inspections to maintain your roof.', 0, 0),
|
|
('Plumbing - Fixing leaks, installing fixtures, and unclogging drains', 'Expert plumbing services for leak repairs, fixture installation, drain cleaning, and all plumbing needs.', 0, 0),
|
|
('HVAC - Heating, ventilation, and air conditioning maintenance and repair', 'Complete HVAC services including heating repair, air conditioning service, ventilation system maintenance, and energy efficiency upgrades.', 0, 0),
|
|
('Electrical - Wiring, lighting installation, and electrical repairs', 'Professional electrical services for wiring, lighting installation, outlet repairs, and electrical safety inspections.', 0, 0),
|
|
('Pest Control - Extermination of insects, rodents, and other pests', 'Effective pest control services for eliminating insects, rodents, and other unwanted pests from your property.', 0, 0),
|
|
('House Cleaning - Regular or deep cleaning services', 'Thorough house cleaning services including regular maintenance cleaning and deep cleaning for a fresh, organized home.', 0, 0),
|
|
('Window Cleaning - Exterior and interior window washing', 'Professional window cleaning for both exterior and interior surfaces, ensuring crystal clear views and streak-free glass.', 0, 0),
|
|
('Gutter Cleaning - Removing debris from gutters to prevent water damage', 'Gutter cleaning and maintenance to remove leaves, debris, and prevent water damage and foundation issues.', 0, 0),
|
|
('Painting - Interior and exterior painting for aesthetic and protection', 'Quality painting services for interior rooms and exterior surfaces, enhancing both appearance and protection.', 0, 0),
|
|
('Carpentry - Building or repairing decks, fences, and furniture', 'Skilled carpentry work including deck construction, fence repair, furniture building, and custom woodwork.', 0, 0),
|
|
('Masonry - Brickwork, stonework, and chimney repairs', 'Expert masonry services for brickwork, stone installations, chimney repairs, and stone structure maintenance.', 0, 0),
|
|
('Siding Installation/Repair - Maintaining or replacing exterior siding', 'Siding services including installation, repair, and replacement to protect and enhance your home exterior.', 0, 0),
|
|
('Pressure Washing - Cleaning driveways, decks, and home exteriors', 'High-pressure cleaning services for driveways, decks, siding, and other exterior surfaces to restore appearance.', 0, 0),
|
|
('Tree Services - Tree removal, pruning, and stump grinding', 'Professional tree care including removal of hazardous trees, pruning, trimming, and stump grinding services.', 0, 0),
|
|
('Septic System Services - Pumping, maintenance, and repairs for septic tanks', 'Septic system maintenance including pumping, inspections, repairs, and regular servicing to prevent system failure.', 0, 0),
|
|
('Well Water Services - Maintenance and testing for private wells', 'Well water services including maintenance, testing, filtration, and repair for private water well systems.', 0, 0),
|
|
('Home Security Installation - Alarm systems, cameras, and smart locks', 'Home security installation including alarm systems, surveillance cameras, smart locks, and security monitoring.', 0, 0),
|
|
('Locksmith Services - Lock repairs, replacements, and rekeying', 'Professional locksmith services for lock repair, replacement, rekeying, and emergency lockout assistance.', 0, 0),
|
|
('Appliance Repair - Fixing refrigerators, washers, dryers, and more', 'Appliance repair services for all major household appliances including refrigerators, washers, dryers, and ovens.', 0, 0),
|
|
('Garage Door Services - Installation, repair, and maintenance of garage doors', 'Garage door services including installation, repair, maintenance, and opener replacement for residential and commercial doors.', 0, 0),
|
|
('Foundation Repair - Addressing cracks or structural issues in foundations', 'Foundation repair services to address cracks, settling, and structural issues in home and building foundations.', 0, 0),
|
|
('Waterproofing - Basement or crawlspace waterproofing to prevent leaks', 'Waterproofing services for basements and crawlspaces to prevent water intrusion and moisture damage.', 0, 0),
|
|
('Mold Remediation - Removing mold and addressing moisture issues', 'Professional mold remediation services including identification, removal, and moisture control to protect your health.', 0, 0),
|
|
('Insulation Services - Installing or upgrading insulation for energy efficiency', 'Insulation installation and upgrading services to improve energy efficiency, comfort, and reduce heating/cooling costs.', 0, 0),
|
|
('Drywall Installation/Repair - Fixing holes or installing new drywall', 'Drywall services including installation, repair of holes and damage, and finishing for smooth, professional walls.', 0, 0),
|
|
('Flooring Services - Installing or repairing hardwood, tile, or carpet', 'Flooring installation and repair services for hardwood, tile, carpeting, laminate, and other flooring types.', 0, 0),
|
|
('Carpet Cleaning - Deep cleaning or stain removal for carpets', 'Professional carpet cleaning services including deep cleaning, stain removal, and maintenance to extend carpet life.', 0, 0),
|
|
('Chimney Sweep - Cleaning chimneys to ensure safe fireplace use', 'Chimney sweeping and inspection services to clean creosote buildup and ensure safe, efficient fireplace operation.', 0, 0),
|
|
('Pool Maintenance - Cleaning, repairs, and chemical balancing for pools', 'Complete pool maintenance services including cleaning, repairs, chemical balancing, and seasonal maintenance.', 0, 0),
|
|
('Fence Installation/Repair - Building or fixing fences for privacy or security', 'Fence installation and repair services including new fence construction and fixing damaged sections for privacy and security.', 0, 0),
|
|
('Home Inspection - Pre-purchase or routine home condition assessments', 'Comprehensive home inspection services for pre-purchase evaluations or routine condition assessments.', 0, 0),
|
|
('Window Replacement - Installing energy-efficient windows or repairing frames', 'Window replacement and repair services including energy-efficient window installation and frame repairs.', 0, 0),
|
|
('Junk Removal - Hauling away unwanted items or debris', 'Junk removal services for clearing unwanted items, debris, and clutter from homes, offices, and construction sites.', 0, 0)
|
|
ON CONFLICT DO NOTHING;
|