-- Portal Index Optimization Migration
-- Generated: 2024-12-23
-- Purpose: Add tables and indexes to optimize portal and service routes queries
--
-- This migration:
--   1. Creates portal tables if they don't exist
--   2. Adds indexes for tables frequently queried in:
--      - routes/portal/announcements.js
--      - routes/portal/auth.js
--      - routes/portal/history.js
--      - routes/portal/requests.js
--      - routes/service/portal.js
--
-- Run this script on existing Uniti installations to improve query performance.
-- All statements use IF NOT EXISTS for safe re-runs.

BEGIN;


-- ============================================================================
-- CREATE TABLES (if not exist)
-- Order matters for foreign key dependencies:
--   1. job_portal_access (no portal table dependencies)
--   2. job_portal_client_announcements (no portal table dependencies)
--   3. job_portal_announcement_read_receipts (depends on job_portal_client_announcements)
--   4. job_portal_service_requests (no portal table dependencies)
-- ============================================================================

-- Job portal access

CREATE TABLE IF NOT EXISTS job_portal_access (
    contact_id      UUID NOT NULL,
    customer_id     UUID NOT NULL,
    client_notes    TEXT,
    service_history BOOLEAN DEFAULT true,
    service_request BOOLEAN DEFAULT true,

    -- Primary Key: one access record per (contact_id, customer_id)
    CONSTRAINT job_portal_access_pkey 
        PRIMARY KEY (contact_id, customer_id),

    -- Foreign Keys
    CONSTRAINT job_portal_access_contact_fkey 
        FOREIGN KEY (contact_id) REFERENCES contacts(contact_id) ON DELETE CASCADE,

    CONSTRAINT job_portal_access_customer_fkey 
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);


-- Job portal, client announcements
-- Note: This table must be created BEFORE job_portal_announcement_read_receipts (FK dependency)

CREATE TABLE IF NOT EXISTS job_portal_client_announcements (
    announcement        TEXT NOT NULL,
    announcement_group  UUID NOT NULL,
    announcement_id     UUID NOT NULL,
    created             BIGINT NOT NULL,
    customer_id         UUID NOT NULL,
    deletable           BOOLEAN DEFAULT TRUE,

    -- Primary Key
    CONSTRAINT job_portal_client_announcements_pkey PRIMARY KEY (announcement_id),

    -- Foreign Keys
    CONSTRAINT job_portal_client_announcements_customer_fkey FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);


-- Job portal announcement read receipts

CREATE TABLE IF NOT EXISTS job_portal_announcement_read_receipts (
    announcement_id     UUID NOT NULL,
    contact_id          UUID NOT NULL,
    customer_id         UUID NOT NULL,
    read_time           BIGINT NOT NULL,

    -- Primary Key
    CONSTRAINT job_portal_announcement_read_receipts_pkey PRIMARY KEY (announcement_id, contact_id, customer_id),
    
    -- Foreign Keys
    CONSTRAINT job_portal_announcement_read_receipts_announcement_fkey FOREIGN KEY (announcement_id) REFERENCES job_portal_client_announcements(announcement_id) ON DELETE CASCADE,
    CONSTRAINT job_portal_announcement_read_receipts_contact_fkey FOREIGN KEY (contact_id) REFERENCES contacts(contact_id) ON DELETE CASCADE,
    CONSTRAINT job_portal_announcement_read_receipts_customer_fkey FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);


-- Job portal, service requests

CREATE TABLE IF NOT EXISTS job_portal_service_requests (
    created             BIGINT NOT NULL,
    contact_id          UUID NOT NULL,
    customer_id         UUID NOT NULL,
    job_id              UUID,
    location_id         UUID,
    open                BOOLEAN DEFAULT true,
    request             TEXT NOT NULL,
    request_id          UUID NOT NULL,
    response            TEXT,
    updated             BIGINT,
    updated_by          TEXT,

    -- Primary Key
    CONSTRAINT job_portal_service_requests_pkey PRIMARY KEY (request_id),

    -- Foreign Keys
    CONSTRAINT job_portal_service_requests_contact_fkey FOREIGN KEY (contact_id) REFERENCES contacts(contact_id) ON DELETE CASCADE,
    CONSTRAINT job_portal_service_requests_customer_fkey FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
    CONSTRAINT job_portal_service_requests_job_fkey FOREIGN KEY (job_id) REFERENCES job_info(job_id) ON DELETE CASCADE
);

-- ============================================================================
-- JOB PORTAL ACCESS
-- Used in: portal auth, announcement delivery, customers-with-access queries
-- ============================================================================

-- For lookups by customer (portal announcements, getting contacts with access)
CREATE INDEX IF NOT EXISTS idx_job_portal_access_customer_id 
    ON job_portal_access(customer_id);

-- For lookups by contact (auth validation, permission checks)
CREATE INDEX IF NOT EXISTS idx_job_portal_access_contact_id 
    ON job_portal_access(contact_id);


-- ============================================================================
-- JOB PORTAL CLIENT ANNOUNCEMENTS
-- Used in: announcements/all, announcements/delete, announcements/update
-- ============================================================================

-- For listing announcements by customer ordered by creation time (most common query)
CREATE INDEX IF NOT EXISTS idx_job_portal_announcements_customer_created 
    ON job_portal_client_announcements(customer_id, created DESC);

-- For deleting/updating announcements by group (global announcement recall)
CREATE INDEX IF NOT EXISTS idx_job_portal_announcements_group 
    ON job_portal_client_announcements(announcement_group);


-- ============================================================================
-- JOB PORTAL ANNOUNCEMENT READ RECEIPTS
-- Used in: announcements/all, mark-read queries with LEFT JOIN
-- ============================================================================

-- For efficient LEFT JOINs on read status lookups (announcement + contact)
CREATE INDEX IF NOT EXISTS idx_job_portal_read_receipts_announcement_contact 
    ON job_portal_announcement_read_receipts(announcement_id, contact_id);


-- ============================================================================
-- JOB PORTAL SERVICE REQUESTS
-- Used in: requests/all, requests/get, requests/update, requests/delete
-- ============================================================================

-- For listing service requests by customer ordered by creation time
CREATE INDEX IF NOT EXISTS idx_job_portal_requests_customer_created 
    ON job_portal_service_requests(customer_id, created DESC);

-- For filtering requests by customer and open status (common admin query)
CREATE INDEX IF NOT EXISTS idx_job_portal_requests_customer_open 
    ON job_portal_service_requests(customer_id, open);

-- For lookups by contact
CREATE INDEX IF NOT EXISTS idx_job_portal_requests_contact_id 
    ON job_portal_service_requests(contact_id);

-- For linking service requests to jobs (partial index - only where job_id exists)
CREATE INDEX IF NOT EXISTS idx_job_portal_requests_job_id 
    ON job_portal_service_requests(job_id) WHERE job_id IS NOT NULL;


-- ============================================================================
-- JOB INFO
-- Used in: portal/history, service request to job linking
-- ============================================================================

-- For looking up jobs by customer (portal service history, job lists)
CREATE INDEX IF NOT EXISTS idx_job_info_customer_id 
    ON job_info(customer_id);

-- For looking up jobs by company
CREATE INDEX IF NOT EXISTS idx_job_info_company_id 
    ON job_info(company_id);

-- For filtering active jobs by customer
CREATE INDEX IF NOT EXISTS idx_job_info_customer_active 
    ON job_info(customer_id, active);

-- For looking up jobs by assigned user (partial index)
CREATE INDEX IF NOT EXISTS idx_job_info_assigned_user 
    ON job_info(assigned_user) WHERE assigned_user IS NOT NULL;


-- ============================================================================
-- JOB TICKETS
-- Used in: portal/history/all, date range queries for service history
-- ============================================================================

-- For looking up tickets by job ordered by booking time (portal service history)
CREATE INDEX IF NOT EXISTS idx_job_tickets_job_booked 
    ON job_tickets(job_id, booked);

-- For date range queries on tickets
CREATE INDEX IF NOT EXISTS idx_job_tickets_booked 
    ON job_tickets(booked);

-- For filtering non-suppressed tickets by job (partial index)
CREATE INDEX IF NOT EXISTS idx_job_tickets_job_unsuppressed 
    ON job_tickets(job_id, booked) WHERE COALESCE(suppressed, false) = false;

-- For looking up tickets by assigned user
CREATE INDEX IF NOT EXISTS idx_job_tickets_assigned_user 
    ON job_tickets(assigned_user);


-- ============================================================================
-- CONTACTS
-- Used in: portal auth, notifications, email delivery
-- ============================================================================

-- For filtering active contacts (portal, notifications) - partial index
CREATE INDEX IF NOT EXISTS idx_contacts_active 
    ON contacts(active) WHERE active = true;

-- For email lookups (notifications, portal auth) - partial index
CREATE INDEX IF NOT EXISTS idx_contacts_email 
    ON contacts(email) WHERE email IS NOT NULL AND email != '';


-- ============================================================================
-- CUSTOMERS
-- Used in: portal customers-with-access, announcements join, requests join
-- ============================================================================

-- For filtering active customers (portal customers-with-access query) - partial index
CREATE INDEX IF NOT EXISTS idx_customers_active 
    ON customers(active) WHERE active = true;

-- For looking up customers by company
CREATE INDEX IF NOT EXISTS idx_customers_company_id 
    ON customers(company_id);

-- For sorting by customer name (common in listings)
CREATE INDEX IF NOT EXISTS idx_customers_name 
    ON customers(customer_name);


-- ============================================================================
-- CUSTOMER LOCATIONS
-- Used in: service requests location lookups
-- ============================================================================

-- For looking up locations by customer (service requests, job creation)
CREATE INDEX IF NOT EXISTS idx_customer_locations_customer_id 
    ON customer_locations(customer_id);


COMMIT;

-- ============================================================================
-- VERIFICATION QUERIES
-- Run these after migration to verify tables and indexes were created:
-- ============================================================================

/*
-- Verify portal tables exist:

SELECT 
    table_name,
    (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = t.table_name) AS column_count
FROM 
    information_schema.tables t
WHERE 
    table_schema = 'public'
    AND table_name IN (
        'job_portal_access',
        'job_portal_client_announcements',
        'job_portal_announcement_read_receipts',
        'job_portal_service_requests'
    )
ORDER BY 
    table_name;


-- Verify all indexes were created:

SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM 
    pg_indexes
WHERE 
    indexname LIKE 'idx_job_portal%'
    OR indexname LIKE 'idx_job_info%'
    OR indexname LIKE 'idx_job_tickets%'
    OR indexname LIKE 'idx_contacts_%'
    OR indexname LIKE 'idx_customers_%'
    OR indexname LIKE 'idx_customer_locations%'
ORDER BY 
    tablename, indexname;
*/

