db/migrations/005_create_service_ratings_tables.sql

-- Migration: Create service professional ratings tables
-- Description: Creates trans.spa_service_ratings and trans.spa_rating_responses tables
-- Date: 2024-02-27

-- Ensure trans schema exists
CREATE SCHEMA IF NOT EXISTS trans;

-- Create service_ratings table
CREATE TABLE IF NOT EXISTS trans.spa_service_ratings (
    rating_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL,
    service_partners_id VARCHAR(50) NOT NULL,
    customer_id VARCHAR(50) NOT NULL,
    merchant_id VARCHAR(50),
    service_id VARCHAR(50),
    rating_score NUMERIC(2,1) NOT NULL CHECK (rating_score >= 1.0 AND rating_score <= 5.0),
    review_text TEXT,
    review_title VARCHAR(200),
    is_verified_purchase BOOLEAN DEFAULT true,
    is_anonymous BOOLEAN DEFAULT false,
    helpful_count INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active', -- active | hidden | flagged | deleted
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_rating_order
        FOREIGN KEY(order_id) 
        REFERENCES trans.spa_partner_orders(order_id)
        ON DELETE CASCADE
);

-- Create rating_responses table (for service professional responses to reviews)
CREATE TABLE IF NOT EXISTS trans.spa_rating_responses (
    response_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rating_id UUID NOT NULL,
    responder_id VARCHAR(50) NOT NULL,
    responder_type VARCHAR(20) NOT NULL, -- professional | merchant | admin
    response_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_response_rating
        FOREIGN KEY(rating_id) 
        REFERENCES trans.spa_service_ratings(rating_id)
        ON DELETE CASCADE
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_ratings_service_partners ON trans.spa_service_ratings(service_partners_id);
CREATE INDEX IF NOT EXISTS idx_ratings_customer ON trans.spa_service_ratings(customer_id);
CREATE INDEX IF NOT EXISTS idx_ratings_merchant ON trans.spa_service_ratings(merchant_id);
CREATE INDEX IF NOT EXISTS idx_ratings_service ON trans.spa_service_ratings(service_id);
CREATE INDEX IF NOT EXISTS idx_ratings_order ON trans.spa_service_ratings(order_id);
CREATE INDEX IF NOT EXISTS idx_ratings_score ON trans.spa_service_ratings(rating_score);
CREATE INDEX IF NOT EXISTS idx_ratings_status ON trans.spa_service_ratings(status);
CREATE INDEX IF NOT EXISTS idx_ratings_created_at ON trans.spa_service_ratings(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rating_responses_rating_id ON trans.spa_rating_responses(rating_id);

-- Create trigger to auto-update updated_at timestamp for ratings
CREATE OR REPLACE FUNCTION trans.update_rating_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_rating_updated_at ON trans.spa_service_ratings;
CREATE TRIGGER trigger_update_rating_updated_at
    BEFORE UPDATE ON trans.spa_service_ratings
    FOR EACH ROW
    EXECUTE FUNCTION trans.update_rating_updated_at();

-- Create trigger to auto-update updated_at timestamp for responses
DROP TRIGGER IF EXISTS trigger_update_response_updated_at ON trans.spa_rating_responses;
CREATE TRIGGER trigger_update_response_updated_at
    BEFORE UPDATE ON trans.spa_rating_responses
    FOR EACH ROW
    EXECUTE FUNCTION trans.update_rating_updated_at();

-- Add comments for documentation
COMMENT ON TABLE trans.spa_service_ratings IS 'Customer ratings and reviews for service professionals';
COMMENT ON TABLE trans.spa_rating_responses IS 'Responses to ratings from professionals, merchants, or admins';

COMMENT ON COLUMN trans.spa_service_ratings.rating_score IS 'Rating score from 1.0 to 5.0';
COMMENT ON COLUMN trans.spa_service_ratings.is_verified_purchase IS 'Whether the rating is from a verified order';
COMMENT ON COLUMN trans.spa_service_ratings.is_anonymous IS 'Whether the customer wants to remain anonymous';
COMMENT ON COLUMN trans.spa_service_ratings.helpful_count IS 'Number of users who found this review helpful';
COMMENT ON COLUMN trans.spa_service_ratings.status IS 'Rating status: active | hidden | flagged | deleted';
COMMENT ON COLUMN trans.spa_rating_responses.responder_type IS 'Type of responder: professional | merchant | admin';

-- Grant permissions (adjust as needed for your environment)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON trans.spa_service_ratings TO spa_app_user;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON trans.spa_rating_responses TO spa_app_user;