-- Database schema for Geezer Guide
-- This script creates the necessary tables and indexes

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    role ENUM('family', 'facility_owner', 'admin', 'super_admin') NOT NULL DEFAULT 'family',
    status ENUM('active', 'pending', 'suspended') NOT NULL DEFAULT 'pending',
    email_verified BOOLEAN DEFAULT FALSE,
    phone_verified BOOLEAN DEFAULT FALSE,
    profile JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status)
);

-- Facilities table
CREATE TABLE IF NOT EXISTS facilities (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type ENUM('Independent Living', 'Assisted Living', 'Memory Care', 'Skilled Nursing', 'Continuing Care') NOT NULL,
    description TEXT,
    full_description TEXT,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zip_code VARCHAR(10) NOT NULL,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255),
    website VARCHAR(255),
    price_min DECIMAL(10, 2),
    price_max DECIMAL(10, 2),
    capacity_total INT,
    capacity_available INT,
    license_number VARCHAR(100) NOT NULL,
    hhs_id VARCHAR(100),
    rating DECIMAL(3, 2) DEFAULT 0,
    review_count INT DEFAULT 0,
    verified BOOLEAN DEFAULT FALSE,
    featured BOOLEAN DEFAULT FALSE,
    status ENUM('active', 'pending', 'suspended', 'closed') NOT NULL DEFAULT 'pending',
    has_availability BOOLEAN DEFAULT TRUE,
    waiting_list BOOLEAN DEFAULT FALSE,
    images JSON,
    amenities JSON,
    services JSON,
    care_types JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_location (city, state),
    INDEX idx_status (status),
    INDEX idx_rating (rating),
    INDEX idx_license (license_number),
    FULLTEXT idx_search (name, description, city)
);

-- Reviews table
CREATE TABLE IF NOT EXISTS reviews (
    id VARCHAR(36) PRIMARY KEY,
    facility_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    photos JSON,
    relationship VARCHAR(50),
    stay_duration VARCHAR(50),
    helpful INT DEFAULT 0,
    unhelpful INT DEFAULT 0,
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    flagged BOOLEAN DEFAULT FALSE,
    flag_reason TEXT,
    verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (facility_id) REFERENCES facilities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_facility (facility_id),
    INDEX idx_user (user_id),
    INDEX idx_rating (rating),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
);

-- Data uploads table
CREATE TABLE IF NOT EXISTS data_uploads (
    id VARCHAR(36) PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    file_size BIGINT NOT NULL,
    uploaded_by VARCHAR(36) NOT NULL,
    status ENUM('processing', 'completed', 'error') NOT NULL DEFAULT 'processing',
    records_processed INT DEFAULT 0,
    records_added INT DEFAULT 0,
    records_updated INT DEFAULT 0,
    records_skipped INT DEFAULT 0,
    errors JSON,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(id),
    INDEX idx_status (status),
    INDEX idx_uploaded_by (uploaded_by),
    INDEX idx_started (started_at)
);

-- User favorites table
CREATE TABLE IF NOT EXISTS user_favorites (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    facility_id VARCHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (facility_id) REFERENCES facilities(id) ON DELETE CASCADE,
    UNIQUE KEY unique_favorite (user_id, facility_id),
    INDEX idx_user (user_id),
    INDEX idx_facility (facility_id)
);

-- Review votes table
CREATE TABLE IF NOT EXISTS review_votes (
    id VARCHAR(36) PRIMARY KEY,
    review_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    vote_type ENUM('helpful', 'unhelpful') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_vote (review_id, user_id),
    INDEX idx_review (review_id),
    INDEX idx_user (user_id)
);

-- Search logs table for analytics
CREATE TABLE IF NOT EXISTS search_logs (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36),
    search_query VARCHAR(500),
    filters JSON,
    results_count INT,
    location VARCHAR(255),
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_created (created_at),
    INDEX idx_location (location)
);

-- Create triggers to update facility ratings
DELIMITER //
CREATE TRIGGER update_facility_rating_after_review_insert
    AFTER INSERT ON reviews
    FOR EACH ROW
BEGIN
    UPDATE facilities 
    SET rating = (
        SELECT AVG(rating) 
        FROM reviews 
        WHERE facility_id = NEW.facility_id AND status = 'approved'
    ),
    review_count = (
        SELECT COUNT(*) 
        FROM reviews 
        WHERE facility_id = NEW.facility_id AND status = 'approved'
    )
    WHERE id = NEW.facility_id;
END//

CREATE TRIGGER update_facility_rating_after_review_update
    AFTER UPDATE ON reviews
    FOR EACH ROW
BEGIN
    UPDATE facilities 
    SET rating = (
        SELECT AVG(rating) 
        FROM reviews 
        WHERE facility_id = NEW.facility_id AND status = 'approved'
    ),
    review_count = (
        SELECT COUNT(*) 
        FROM reviews 
        WHERE facility_id = NEW.facility_id AND status = 'approved'
    )
    WHERE id = NEW.facility_id;
END//
DELIMITER ;