-- ============================================================
-- 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;