-- PT. Altrak 1978 - Critical Database Indexes
-- Run this script to add performance-critical indexes

-- ============================================
-- OPERATIONAL RECORDS (PO/PSO) INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_operational_records_customer_name 
ON operational_records(customer_name);

CREATE INDEX IF NOT EXISTS idx_operational_records_pso_date 
ON operational_records(pso_date);

CREATE INDEX IF NOT EXISTS idx_operational_records_is_verified 
ON operational_records(is_verified);

CREATE INDEX IF NOT EXISTS idx_operational_records_record_status 
ON operational_records(record_status);

CREATE INDEX IF NOT EXISTS idx_operational_records_branch_id 
ON operational_records(branch_id);

CREATE INDEX IF NOT EXISTS idx_operational_records_verified_by 
ON operational_records(verified_by);

-- Composite index for common queries
CREATE INDEX IF NOT EXISTS idx_operational_records_status_date 
ON operational_records(record_status, pso_date);

-- ============================================
-- CUSTOMER PROFILES INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_customer_profiles_customer_name 
ON customer_profiles(customer_name);

CREATE INDEX IF NOT EXISTS idx_customer_profiles_status 
ON customer_profiles(status);

CREATE INDEX IF NOT EXISTS idx_customer_profiles_wapu_status 
ON customer_profiles(wapu_status);

CREATE INDEX IF NOT EXISTS idx_customer_profiles_npwp 
ON customer_profiles(npwp);

-- ============================================
-- MEMORANDUMS (IOM) INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_memorandums_date 
ON memorandums(date);

CREATE INDEX IF NOT EXISTS idx_memorandums_ref 
ON memorandums(ref);

CREATE INDEX IF NOT EXISTS idx_memorandums_status 
ON memorandums(status);

-- ============================================
-- SERVICE REQUISITIONS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_service_requisitions_customer 
ON service_requisitions(customer);

CREATE INDEX IF NOT EXISTS idx_service_requisitions_date 
ON service_requisitions(date);

CREATE INDEX IF NOT EXISTS idx_service_requisitions_ref 
ON service_requisitions(ref);

CREATE INDEX IF NOT EXISTS idx_service_requisitions_status 
ON service_requisitions(status);

CREATE INDEX IF NOT EXISTS idx_service_requisitions_created_by 
ON service_requisitions(created_by);

-- ============================================
-- SERVICE AUTHORIZATIONS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_service_authorizations_customer_name 
ON service_authorizations(customer_name);

CREATE INDEX IF NOT EXISTS idx_service_authorizations_ref 
ON service_authorizations(ref);

CREATE INDEX IF NOT EXISTS idx_service_authorizations_status 
ON service_authorizations(status);

CREATE INDEX IF NOT EXISTS idx_service_authorizations_request_type 
ON service_authorizations(request_type);

-- ============================================
-- CREDIT LIMITS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_credit_limits_customer 
ON credit_limits(customer);

CREATE INDEX IF NOT EXISTS idx_credit_limits_ref 
ON credit_limits(ref);

CREATE INDEX IF NOT EXISTS idx_credit_limits_status 
ON credit_limits(status);

-- ============================================
-- ACTIVITY LOGS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_activity_logs_user_id 
ON activity_logs(user_id);

CREATE INDEX IF NOT EXISTS idx_activity_logs_created_at 
ON activity_logs(created_at);

CREATE INDEX IF NOT EXISTS idx_activity_logs_action 
ON activity_logs(action);

-- Composite index for common queries
CREATE INDEX IF NOT EXISTS idx_activity_logs_user_date 
ON activity_logs(user_id, created_at);

-- ============================================
-- USERS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_users_username 
ON users(username);

CREATE INDEX IF NOT EXISTS idx_users_role 
ON users(role);

CREATE INDEX IF NOT EXISTS idx_users_is_active 
ON users(is_active);

CREATE INDEX IF NOT EXISTS idx_users_branch_id 
ON users(branch_id);

-- ============================================
-- REFRESH TOKENS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id 
ON refresh_tokens(user_id);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires_at 
ON refresh_tokens(expires_at);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_revoked 
ON refresh_tokens(revoked);

-- Composite index for token validation
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_expiry 
ON refresh_tokens(user_id, expires_at, revoked);

-- ============================================
-- CUSTOMER DECISION MAKERS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_customer_decision_makers_profile_id 
ON customer_decision_makers(user_profile_id);

-- ============================================
-- CUSTOMER EQUIPMENTS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_customer_equipments_profile_id 
ON customer_equipments(user_profile_id);

-- ============================================
-- CUSTOMER AFFILIATIONS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_customer_affiliations_profile_id 
ON customer_affiliations(user_profile_id);

-- ============================================
-- SERVICE REQUISITION EQUIPMENTS INDEXES
-- ============================================
CREATE INDEX IF NOT EXISTS idx_sr_equipments_sr_id 
ON service_requisition_equipments(service_requisition_id);

-- ============================================
-- VERIFY INDEXES CREATED
-- ============================================
-- Run this query to verify all indexes:
-- SELECT table_name, index_name FROM information_schema.statistics 
-- WHERE table_schema = 'spareparts' ORDER BY table_name, index_name;