Sf Soql

/**
 * AGGREGATE QUERY PATTERNS
 *
 * SOQL aggregate functions:
 * - COUNT(), COUNT(field), COUNT_DISTINCT(field)
 * - SUM(field), AVG(field), MIN(field), MAX(field)
 *
 * Use with GROUP BY for grouping results.
 */

// ═══════════════════════════════════════════════════════════════════════════
// BASIC COUNT
// ═══════════════════════════════════════════════════════════════════════════

-- Count all records
SELECT COUNT() FROM Account

-- Count with filter
SELECT COUNT() FROM Contact WHERE Email != null

-- Count with field alias
SELECT COUNT(Id) total FROM Account

-- Count distinct values
SELECT COUNT_DISTINCT(Industry) FROM Account

// ═══════════════════════════════════════════════════════════════════════════
// SUM, AVG, MIN, MAX
// ═══════════════════════════════════════════════════════════════════════════

-- Sum of amounts
SELECT SUM(Amount) totalAmount FROM Opportunity WHERE IsClosed = true

-- Average amount
SELECT AVG(Amount) avgAmount FROM Opportunity WHERE StageName = 'Closed Won'

-- Min and Max
SELECT MIN(Amount) smallest, MAX(Amount) largest
FROM Opportunity
WHERE IsClosed = true

-- Combined aggregates
SELECT
    COUNT(Id) totalOpps,
    SUM(Amount) totalAmount,
    AVG(Amount) avgAmount,
    MIN(Amount) minAmount,
    MAX(Amount) maxAmount
FROM Opportunity
WHERE StageName = 'Closed Won'
AND CALENDAR_YEAR(CloseDate) = 2024

// ═══════════════════════════════════════════════════════════════════════════
// GROUP BY
// ═══════════════════════════════════════════════════════════════════════════

-- Count by single field
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY Industry

-- Count by multiple fields
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP BY Industry, Type

-- Sum by category
SELECT StageName, SUM(Amount)
FROM Opportunity
GROUP BY StageName

-- Average by owner
SELECT OwnerId, AVG(Amount)
FROM Opportunity
GROUP BY OwnerId

-- Aggregate by date part
SELECT CALENDAR_MONTH(CloseDate), SUM(Amount)
FROM Opportunity
WHERE CALENDAR_YEAR(CloseDate) = 2024
GROUP BY CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_MONTH(CloseDate)

-- Year and Month grouping
SELECT CALENDAR_YEAR(CloseDate) yr, CALENDAR_MONTH(CloseDate) mn, SUM(Amount)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)

// ═══════════════════════════════════════════════════════════════════════════
// HAVING CLAUSE
// ═══════════════════════════════════════════════════════════════════════════

-- Filter groups by aggregate result
SELECT Industry, COUNT(Id) cnt
FROM Account
GROUP BY Industry
HAVING COUNT(Id) > 10

-- Multiple HAVING conditions
SELECT OwnerId, SUM(Amount) total, COUNT(Id) cnt
FROM Opportunity
WHERE StageName = 'Closed Won'
GROUP BY OwnerId
HAVING SUM(Amount) > 100000
AND COUNT(Id) >= 5

-- HAVING with different aggregate than SELECT
SELECT LeadSource, COUNT(Id)
FROM Lead
GROUP BY LeadSource
HAVING AVG(NumberOfEmployees) > 100

// ═══════════════════════════════════════════════════════════════════════════
// GROUP BY ROLLUP (Subtotals)
// ═══════════════════════════════════════════════════════════════════════════

-- Single rollup
SELECT Industry, COUNT(Id)
FROM Account
GROUP BY ROLLUP(Industry)
-- Returns: individual industries + grand total (null Industry row)

-- Multiple field rollup
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP BY ROLLUP(Industry, Type)
-- Returns: Industry+Type combos, Industry subtotals, grand total

-- With SUM
SELECT StageName, CALENDAR_MONTH(CloseDate), SUM(Amount)
FROM Opportunity
WHERE CALENDAR_YEAR(CloseDate) = 2024
GROUP BY ROLLUP(StageName, CALENDAR_MONTH(CloseDate))

// ═══════════════════════════════════════════════════════════════════════════
// GROUP BY CUBE (All Combinations)
// ═══════════════════════════════════════════════════════════════════════════

-- All dimension combinations
SELECT Industry, Type, COUNT(Id)
FROM Account
GROUP BY CUBE(Industry, Type)
-- Returns: Industry+Type, Industry only, Type only, grand total

// ═══════════════════════════════════════════════════════════════════════════
// DATE FUNCTIONS IN AGGREGATES
// ═══════════════════════════════════════════════════════════════════════════

-- By calendar year
SELECT CALENDAR_YEAR(CreatedDate) yr, COUNT(Id)
FROM Account
GROUP BY CALENDAR_YEAR(CreatedDate)

-- By calendar quarter
SELECT CALENDAR_YEAR(CloseDate), CALENDAR_QUARTER(CloseDate), SUM(Amount)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CloseDate), CALENDAR_QUARTER(CloseDate)

-- By fiscal year (if fiscal year defined)
SELECT FISCAL_YEAR(CloseDate), SUM(Amount)
FROM Opportunity
GROUP BY FISCAL_YEAR(CloseDate)

-- By week
SELECT WEEK_IN_YEAR(CreatedDate), COUNT(Id)
FROM Lead
WHERE CALENDAR_YEAR(CreatedDate) = 2024
GROUP BY WEEK_IN_YEAR(CreatedDate)

-- By day of week
SELECT DAY_IN_WEEK(CreatedDate), COUNT(Id)
FROM Case
WHERE CreatedDate = THIS_MONTH
GROUP BY DAY_IN_WEEK(CreatedDate)
-- 1=Sunday, 2=Monday, etc.

// ═══════════════════════════════════════════════════════════════════════════
// COMMON AGGREGATE PATTERNS
// ═══════════════════════════════════════════════════════════════════════════

-- Sales by rep this year
SELECT Owner.Name, SUM(Amount) totalSales, COUNT(Id) dealCount
FROM Opportunity
WHERE StageName = 'Closed Won'
AND CloseDate = THIS_YEAR
GROUP BY Owner.Name
ORDER BY SUM(Amount) DESC

-- Lead conversion rate by source
SELECT LeadSource, COUNT(Id) total, SUM(CASE WHEN IsConverted = true THEN 1 ELSE 0 END) converted
FROM Lead
GROUP BY LeadSource
-- Note: CASE not supported in SOQL. Calculate in Apex.

-- Cases by status and priority
SELECT Status, Priority, COUNT(Id)
FROM Case
WHERE CreatedDate = THIS_MONTH
GROUP BY Status, Priority

-- Average deal size by industry
SELECT Account.Industry, AVG(Amount)
FROM Opportunity
WHERE StageName = 'Closed Won'
GROUP BY Account.Industry
HAVING AVG(Amount) > 50000

-- Top accounts by opportunity count
SELECT AccountId, Account.Name, COUNT(Id) oppCount
FROM Opportunity
GROUP BY AccountId, Account.Name
ORDER BY COUNT(Id) DESC
LIMIT 20

-- Pipeline by stage and month
SELECT StageName, CALENDAR_MONTH(CloseDate) closeMonth, SUM(Amount)
FROM Opportunity
WHERE IsClosed = false
AND CloseDate = THIS_QUARTER
GROUP BY StageName, CALENDAR_MONTH(CloseDate)
ORDER BY CALENDAR_MONTH(CloseDate), StageName

// ═══════════════════════════════════════════════════════════════════════════
// AGGREGATE IN APEX
// ═══════════════════════════════════════════════════════════════════════════

/*
// In Apex, aggregate results are returned as AggregateResult[]

List<AggregateResult> results = [
    SELECT Industry, COUNT(Id) cnt, SUM(AnnualRevenue) total
    FROM Account
    GROUP BY Industry
];

for (AggregateResult ar : results) {
    String industry = (String) ar.get('Industry');
    Integer count = (Integer) ar.get('cnt');
    Decimal total = (Decimal) ar.get('total');
}
*/

/**
 * BASIC SOQL QUERY PATTERNS
 *
 * This file contains common SOQL patterns for everyday queries.
 * Each pattern includes the query and usage notes.
 */

// ═══════════════════════════════════════════════════════════════════════════
// SIMPLE SELECT QUERIES
// ═══════════════════════════════════════════════════════════════════════════

-- Get accounts with specific fields
SELECT Id, Name, Industry, AnnualRevenue, Phone
FROM Account
LIMIT 100

-- Get contacts with email
SELECT Id, FirstName, LastName, Email, Phone
FROM Contact
WHERE Email != null
LIMIT 100

-- Get opportunities by stage
SELECT Id, Name, Amount, StageName, CloseDate
FROM Opportunity
WHERE StageName = 'Prospecting'
ORDER BY Amount DESC

// ═══════════════════════════════════════════════════════════════════════════
// DATE FILTERING
// ═══════════════════════════════════════════════════════════════════════════

-- Records created today
SELECT Id, Name FROM Account
WHERE CreatedDate = TODAY

-- Records created this week
SELECT Id, Name FROM Lead
WHERE CreatedDate = THIS_WEEK

-- Records created in last 30 days
SELECT Id, Name, CreatedDate FROM Contact
WHERE CreatedDate = LAST_N_DAYS:30
ORDER BY CreatedDate DESC

-- Records modified this month
SELECT Id, Name, LastModifiedDate FROM Account
WHERE LastModifiedDate = THIS_MONTH

-- Opportunities closing this quarter
SELECT Id, Name, Amount, CloseDate
FROM Opportunity
WHERE CloseDate = THIS_QUARTER
AND IsClosed = false

-- Records created in specific date range
SELECT Id, Name, CreatedDate FROM Account
WHERE CreatedDate >= 2024-01-01T00:00:00Z
AND CreatedDate < 2024-04-01T00:00:00Z

// ═══════════════════════════════════════════════════════════════════════════
// TEXT FILTERING
// ═══════════════════════════════════════════════════════════════════════════

-- Exact match
SELECT Id, Name FROM Account
WHERE Name = 'Acme Corporation'

-- Case-insensitive exact match (default behavior)
SELECT Id, Name FROM Account
WHERE Name = 'acme corporation'

-- Starts with pattern
SELECT Id, Name FROM Account
WHERE Name LIKE 'Acme%'

-- Contains pattern
SELECT Id, Name FROM Account
WHERE Name LIKE '%Corp%'

-- Ends with pattern (NOT indexed - use sparingly)
SELECT Id, Name FROM Account
WHERE Name LIKE '%Inc.'

-- Multiple values
SELECT Id, Name, Industry FROM Account
WHERE Industry IN ('Technology', 'Finance', 'Healthcare')

-- Exclude values
SELECT Id, Name, Industry FROM Account
WHERE Industry NOT IN ('Other', 'Unknown')

// ═══════════════════════════════════════════════════════════════════════════
// NUMERIC FILTERING
// ═══════════════════════════════════════════════════════════════════════════

-- Greater than
SELECT Id, Name, AnnualRevenue FROM Account
WHERE AnnualRevenue > 1000000

-- Range
SELECT Id, Name, Amount FROM Opportunity
WHERE Amount >= 10000 AND Amount <= 100000

-- Not equal
SELECT Id, Name, Amount FROM Opportunity
WHERE Amount != 0

// ═══════════════════════════════════════════════════════════════════════════
// NULL HANDLING
// ═══════════════════════════════════════════════════════════════════════════

-- Find records with null value
SELECT Id, Name FROM Contact
WHERE Email = null

-- Find records with non-null value
SELECT Id, Name, Email FROM Contact
WHERE Email != null

-- Find accounts without opportunities (using NOT IN)
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

// ═══════════════════════════════════════════════════════════════════════════
// SORTING
// ═══════════════════════════════════════════════════════════════════════════

-- Sort ascending (default)
SELECT Id, Name FROM Account
ORDER BY Name ASC

-- Sort descending
SELECT Id, Name, AnnualRevenue FROM Account
ORDER BY AnnualRevenue DESC

-- Multiple sort fields
SELECT Id, Name, Industry, AnnualRevenue FROM Account
ORDER BY Industry ASC, AnnualRevenue DESC

-- Nulls last
SELECT Id, Name, AnnualRevenue FROM Account
ORDER BY AnnualRevenue DESC NULLS LAST

// ═══════════════════════════════════════════════════════════════════════════
// PAGINATION
// ═══════════════════════════════════════════════════════════════════════════

-- First page (records 1-100)
SELECT Id, Name FROM Account
ORDER BY Name
LIMIT 100

-- Second page (records 101-200)
SELECT Id, Name FROM Account
ORDER BY Name
LIMIT 100
OFFSET 100

-- Note: OFFSET max is 2000. For larger datasets, use query locator in Apex.

// ═══════════════════════════════════════════════════════════════════════════
// COMBINED PATTERNS
// ═══════════════════════════════════════════════════════════════════════════

-- Active technology accounts with high revenue created this year
SELECT Id, Name, Industry, AnnualRevenue, CreatedDate
FROM Account
WHERE Industry = 'Technology'
AND AnnualRevenue > 500000
AND IsActive__c = true
AND CreatedDate = THIS_YEAR
ORDER BY AnnualRevenue DESC
LIMIT 50

-- Contacts with Gmail addresses at accounts in California
SELECT Id, FirstName, LastName, Email, Account.Name
FROM Contact
WHERE Email LIKE '%@gmail.com'
AND Account.BillingState = 'CA'
ORDER BY LastName

-- Open cases older than 7 days
SELECT Id, CaseNumber, Subject, Status, CreatedDate, Owner.Name
FROM Case
WHERE IsClosed = false
AND CreatedDate < LAST_N_DAYS:7
ORDER BY CreatedDate ASC

/**
 * Bulkified Query Patterns
 *
 * This file demonstrates the correct patterns for querying related data
 * in bulk operations (triggers, batches, etc.) without hitting governor limits.
 *
 * Key Pattern: Collect IDs first, query once, use Map for O(1) lookups.
 *
 * @see https://www.apexhours.com/bulkification-of-apex-triggers/
 * @see https://medium.com/@saurabh.samirs/salesforce-apex-triggers-5-bulkification-patterns-to-avoid-soql-dml-limits-f4e9c8bbfb3a
 */

// ═══════════════════════════════════════════════════════════════════════════
// PATTERN 1: Simple Map Lookup (Parent Record Access)
// ═══════════════════════════════════════════════════════════════════════════

/**
 * ❌ ANTI-PATTERN: Query inside loop
 * This code will fail with "Too many SOQL queries: 101" for 101+ records
 */
public class AntiPatternExample {
    public void processContacts_WRONG(List<Contact> contacts) {
        for (Contact c : contacts) {
            // ❌ QUERY IN LOOP - will hit governor limit!
            Account a = [SELECT Name FROM Account WHERE Id = :c.AccountId];
            c.Description = 'Account: ' + a.Name;
        }
    }
}

/**
 * ✅ CORRECT PATTERN: Bulk query with Map lookup
 * Uses 1 query regardless of record count
 */
public class BulkPatternExample {
    public void processContacts_CORRECT(List<Contact> contacts) {
        // Step 1: Collect all parent IDs
        Set<Id> accountIds = new Set<Id>();
        for (Contact c : contacts) {
            if (c.AccountId != null) {
                accountIds.add(c.AccountId);
            }
        }

        // Step 2: Single bulk query
        Map<Id, Account> accountMap = new Map<Id, Account>([
            SELECT Id, Name
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ]);

        // Step 3: O(1) Map lookups
        for (Contact c : contacts) {
            Account a = accountMap.get(c.AccountId);
            if (a != null) {
                c.Description = 'Account: ' + a.Name;
            }
        }
    }
}

// ═══════════════════════════════════════════════════════════════════════════
// PATTERN 2: Grouped Child Records (One-to-Many)
// ═══════════════════════════════════════════════════════════════════════════

/**
 * Get child records grouped by parent ID
 * Useful when you need all children for each parent
 */
public class GroupedChildPattern {

    /**
     * Returns Contacts grouped by their AccountId
     */
    public static Map<Id, List<Contact>> getContactsByAccountId(Set<Id> accountIds) {
        Map<Id, List<Contact>> contactsByAccount = new Map<Id, List<Contact>>();

        // Initialize empty lists for each account
        for (Id accountId : accountIds) {
            contactsByAccount.put(accountId, new List<Contact>());
        }

        // Single query for all contacts
        for (Contact c : [
            SELECT Id, FirstName, LastName, Email, AccountId
            FROM Contact
            WHERE AccountId IN :accountIds
            WITH SECURITY_ENFORCED
        ]) {
            contactsByAccount.get(c.AccountId).add(c);
        }

        return contactsByAccount;
    }

    /**
     * Usage example in a trigger
     */
    public void onAccountUpdate(List<Account> accounts) {
        Set<Id> accountIds = new Set<Id>();
        for (Account a : accounts) {
            accountIds.add(a.Id);
        }

        // Get all contacts for all accounts in ONE query
        Map<Id, List<Contact>> contactsByAccount = getContactsByAccountId(accountIds);

        for (Account a : accounts) {
            List<Contact> accountContacts = contactsByAccount.get(a.Id);
            System.debug('Account ' + a.Name + ' has ' + accountContacts.size() + ' contacts');
        }
    }
}

// ═══════════════════════════════════════════════════════════════════════════
// PATTERN 3: Multi-Level Relationship Lookup
// ═══════════════════════════════════════════════════════════════════════════

/**
 * When you need to traverse multiple relationship levels
 * Example: Contact -> Account -> Owner
 */
public class MultiLevelLookupPattern {

    public void enrichContactsWithOwnerInfo(List<Contact> contacts) {
        // Step 1: Get Account IDs
        Set<Id> accountIds = new Set<Id>();
        for (Contact c : contacts) {
            if (c.AccountId != null) {
                accountIds.add(c.AccountId);
            }
        }

        // Step 2: Query Accounts with Owner info (single query)
        Map<Id, Account> accountMap = new Map<Id, Account>([
            SELECT Id, Name, OwnerId, Owner.Name, Owner.Email
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ]);

        // Step 3: Use the data
        for (Contact c : contacts) {
            Account a = accountMap.get(c.AccountId);
            if (a != null && a.Owner != null) {
                c.Description = 'Account Owner: ' + a.Owner.Name;
            }
        }
    }
}

// ═══════════════════════════════════════════════════════════════════════════
// PATTERN 4: Conditional Query Based on Field Values
// ═══════════════════════════════════════════════════════════════════════════

/**
 * When you only need to query for certain records based on field values
 */
public class ConditionalQueryPattern {

    public void updateHighValueOpportunities(List<Opportunity> opportunities) {
        // Step 1: Filter to high-value opportunities only
        Set<Id> highValueAccountIds = new Set<Id>();
        for (Opportunity opp : opportunities) {
            if (opp.Amount != null && opp.Amount > 100000 && opp.AccountId != null) {
                highValueAccountIds.add(opp.AccountId);
            }
        }

        // Step 2: Only query if we have high-value records
        if (highValueAccountIds.isEmpty()) {
            return; // No query needed!
        }

        Map<Id, Account> accountMap = new Map<Id, Account>([
            SELECT Id, Name, AnnualRevenue, Industry
            FROM Account
            WHERE Id IN :highValueAccountIds
            WITH SECURITY_ENFORCED
        ]);

        // Step 3: Process only the high-value opportunities
        for (Opportunity opp : opportunities) {
            if (opp.Amount != null && opp.Amount > 100000) {
                Account a = accountMap.get(opp.AccountId);
                if (a != null) {
                    opp.Description = 'High-value opp for ' + a.Industry + ' account';
                }
            }
        }
    }
}

// ═══════════════════════════════════════════════════════════════════════════
// PATTERN 5: Avoiding Duplicate Queries in Complex Logic
// ═══════════════════════════════════════════════════════════════════════════

/**
 * When the same data is needed in multiple methods,
 * query once and pass the Map around
 */
public class SharedQueryPattern {

    private Map<Id, Account> accountCache;

    /**
     * Initialize the cache once at the start of processing
     */
    public void initializeCache(Set<Id> accountIds) {
        this.accountCache = new Map<Id, Account>([
            SELECT Id, Name, Industry, AnnualRevenue, OwnerId
            FROM Account
            WHERE Id IN :accountIds
            WITH SECURITY_ENFORCED
        ]);
    }

    /**
     * Multiple methods can use the cached data
     */
    public String getAccountName(Id accountId) {
        Account a = accountCache.get(accountId);
        return a != null ? a.Name : null;
    }

    public String getAccountIndustry(Id accountId) {
        Account a = accountCache.get(accountId);
        return a != null ? a.Industry : null;
    }

    public Decimal getAccountRevenue(Id accountId) {
        Account a = accountCache.get(accountId);
        return a != null ? a.AnnualRevenue : 0;
    }
}

// ═══════════════════════════════════════════════════════════════════════════
// PATTERN 6: SOQL For Loop (Large Dataset Processing)
// ═══════════════════════════════════════════════════════════════════════════

/**
 * For processing very large datasets without hitting heap limits
 * The SOQL for loop fetches 200 records at a time
 */
public class LargeDatasetPattern {

    public void processAllAccounts() {
        // This fetches 200 records at a time, not all 50,000
        for (Account a : [
            SELECT Id, Name, Industry
            FROM Account
            WHERE CreatedDate = THIS_YEAR
        ]) {
            // Process one record at a time
            // Heap stays small because only 200 records in memory
            processAccount(a);
        }
    }

    public void processAllAccountsInBatches() {
        // Alternative: explicit batch processing (200 at a time)
        for (List<Account> accountBatch : [
            SELECT Id, Name, Industry
            FROM Account
            WHERE CreatedDate = THIS_YEAR
        ]) {
            // Process 200 records at a time
            processBatch(accountBatch);
        }
    }

    private void processAccount(Account a) {
        // Individual record processing
    }

    private void processBatch(List<Account> accounts) {
        // Batch processing
    }
}

/**
 * SOQL OPTIMIZATION PATTERNS
 *
 * These patterns demonstrate how to write efficient, selective queries
 * that perform well within Salesforce governor limits.
 */

// ═══════════════════════════════════════════════════════════════════════════
// SELECTIVE vs NON-SELECTIVE QUERIES
// ═══════════════════════════════════════════════════════════════════════════

-- ❌ NON-SELECTIVE: Status alone is not indexed
SELECT Id, Name FROM Lead
WHERE Status = 'Open'
-- Scans ALL Lead records

-- ✅ SELECTIVE: Add indexed field filter
SELECT Id, Name FROM Lead
WHERE Status = 'Open'
AND CreatedDate = LAST_N_DAYS:30
-- CreatedDate is indexed, reduces scan

-- ❌ NON-SELECTIVE: Leading wildcard prevents index use
SELECT Id, Name FROM Account
WHERE Name LIKE '%corporation'

-- ✅ SELECTIVE: Trailing wildcard uses index
SELECT Id, Name FROM Account
WHERE Name LIKE 'Acme%'

-- ❌ NON-SELECTIVE: OR can prevent index optimization
SELECT Id FROM Contact
WHERE Email = 'test@test.com'
OR Phone = '555-1234'

-- ✅ SELECTIVE: Use UNION-style in Apex or multiple queries
-- Query 1: SELECT Id FROM Contact WHERE Email = 'test@test.com'
-- Query 2: SELECT Id FROM Contact WHERE Phone = '555-1234'
-- Combine results in Apex

// ═══════════════════════════════════════════════════════════════════════════
// INDEXED FIELDS
// ═══════════════════════════════════════════════════════════════════════════

-- Always indexed fields (use these in WHERE)
-- • Id (primary key)
-- • Name (standard Name field)
-- • OwnerId
-- • CreatedDate
-- • LastModifiedDate
-- • RecordTypeId
-- • External ID fields (custom)
-- • Master-Detail relationship fields
-- • Lookup fields (selective when <100k parent records)

-- ✅ GOOD: Uses indexed OwnerId
SELECT Id, Name FROM Account
WHERE OwnerId = '005xx000000XXXX'

-- ✅ GOOD: Uses indexed CreatedDate
SELECT Id, Name FROM Contact
WHERE CreatedDate >= 2024-01-01T00:00:00Z

-- ✅ GOOD: Uses indexed External ID
SELECT Id, Name FROM Account
WHERE External_Id__c = 'EXT-12345'

// ═══════════════════════════════════════════════════════════════════════════
// FIELD SELECTION OPTIMIZATION
// ═══════════════════════════════════════════════════════════════════════════

-- ❌ BAD: Selecting unnecessary fields
SELECT Id, Name, Description, BillingStreet, BillingCity, BillingState,
       BillingPostalCode, BillingCountry, ShippingStreet, ShippingCity,
       ShippingState, ShippingPostalCode, ShippingCountry, Phone, Fax,
       AccountNumber, Website, Industry, AnnualRevenue, NumberOfEmployees,
       Ownership, TickerSymbol, Description, Rating, Site, AccountSource
FROM Account

-- ✅ GOOD: Select only needed fields
SELECT Id, Name, Industry, AnnualRevenue
FROM Account

-- For display only: Use specific fields
SELECT Id, Name FROM Account

-- For processing: Query fields you'll update
SELECT Id, Status__c, Last_Processed__c FROM Account

// ═══════════════════════════════════════════════════════════════════════════
// LIMIT USAGE
// ═══════════════════════════════════════════════════════════════════════════

-- ❌ BAD: No limit (could return 50,000 rows)
SELECT Id, Name FROM Account

-- ✅ GOOD: Appropriate limit
SELECT Id, Name FROM Account LIMIT 200

-- For pagination
SELECT Id, Name FROM Account ORDER BY Name LIMIT 100 OFFSET 0

-- For single record lookup
SELECT Id, Name FROM Account WHERE Name = 'Acme Corp' LIMIT 1

-- For existence check
SELECT Id FROM Account WHERE Name = 'Acme Corp' LIMIT 1
-- In Apex: if (!results.isEmpty()) { /* exists */ }

// ═══════════════════════════════════════════════════════════════════════════
// RELATIONSHIP QUERY OPTIMIZATION
// ═══════════════════════════════════════════════════════════════════════════

-- ❌ BAD: Deep nesting (5 levels is max, avoid if possible)
SELECT Id, Account.Owner.Manager.Department.Name FROM Contact

-- ✅ GOOD: Flatten when possible
SELECT Id, Account.Name, Account.OwnerId FROM Contact
-- Then query Owner separately if needed

-- ❌ BAD: Too many child subqueries
SELECT Id, Name,
       (SELECT Id FROM Contacts),
       (SELECT Id FROM Opportunities),
       (SELECT Id FROM Cases),
       (SELECT Id FROM Tasks),
       (SELECT Id FROM Events),
       (SELECT Id FROM Notes)
FROM Account

-- ✅ GOOD: Query what you need
SELECT Id, Name,
       (SELECT Id, Name FROM Contacts LIMIT 5)
FROM Account

-- ❌ BAD: Unfiltered subquery
SELECT Id, (SELECT Id FROM Opportunities) FROM Account

-- ✅ GOOD: Filtered subquery
SELECT Id, (SELECT Id FROM Opportunities WHERE StageName != 'Closed Lost' LIMIT 10)
FROM Account

// ═══════════════════════════════════════════════════════════════════════════
// BULK QUERY PATTERNS
// ═══════════════════════════════════════════════════════════════════════════

-- ❌ BAD: Query in loop
/*
for (Contact c : contacts) {
    Account a = [SELECT Name FROM Account WHERE Id = :c.AccountId];
}
*/

-- ✅ GOOD: Bulk query with Map
/*
Set<Id> accountIds = new Set<Id>();
for (Contact c : contacts) {
    accountIds.add(c.AccountId);
}

Map<Id, Account> accountMap = new Map<Id, Account>(
    [SELECT Id, Name FROM Account WHERE Id IN :accountIds]
);

for (Contact c : contacts) {
    Account a = accountMap.get(c.AccountId);
}
*/

-- Use IN clause for bulk operations
SELECT Id, Name FROM Account WHERE Id IN :accountIdSet

-- Bind variable with Set (most efficient)
SELECT Id, Name FROM Contact WHERE AccountId IN :accountIds

// ═══════════════════════════════════════════════════════════════════════════
// FOR UPDATE (Record Locking)
// ═══════════════════════════════════════════════════════════════════════════

-- Lock records for update (prevents concurrent modification)
SELECT Id, Status__c FROM Account WHERE Id = :accountId FOR UPDATE

-- Note: Only use FOR UPDATE when you need to prevent race conditions
-- Adds overhead and can cause lock contention

// ═══════════════════════════════════════════════════════════════════════════
// SECURITY ENFORCEMENT
// ═══════════════════════════════════════════════════════════════════════════

-- ✅ Recommended: WITH SECURITY_ENFORCED
SELECT Id, Name, Phone FROM Account
WITH SECURITY_ENFORCED
-- Throws exception if user lacks field access

-- Alternative: USER_MODE (API 54.0+)
SELECT Id, Name FROM Account
WITH USER_MODE
-- Respects sharing rules and FLS

-- For admin operations: SYSTEM_MODE
SELECT Id, Name FROM Account
WITH SYSTEM_MODE
-- Bypasses sharing (use with caution)

// ═══════════════════════════════════════════════════════════════════════════
// QUERY PLAN ANALYSIS
// ═══════════════════════════════════════════════════════════════════════════

/*
Use Developer Console or CLI to analyze query plans:

1. Developer Console:
   - Query Editor > Check "Use Tooling API"
   - Click "Query Plan" button

2. CLI (uses REST API explain endpoint):
   sf api request rest '/query/?explain=SELECT+Id+FROM+Account+WHERE+Name='\''Test'\''' --target-org my-org --json

Plan Output:
- Cardinality: Estimated rows returned
- Fields: Index fields used
- LeadingOperationType:
  - "Index" = Good (using index)
  - "TableScan" = Bad (scanning all records)
- Cost: Relative cost (lower is better)
- sObjectCardinality: Total records in object
*/

// ═══════════════════════════════════════════════════════════════════════════
// LARGE DATA VOLUME PATTERNS
// ═══════════════════════════════════════════════════════════════════════════

-- For tables with millions of records:

-- 1. Always use indexed fields
SELECT Id FROM Account
WHERE External_Id__c = 'EXT-123'

-- 2. Use date range with limit
SELECT Id, Name FROM Contact
WHERE CreatedDate >= 2024-01-01T00:00:00Z
AND CreatedDate < 2024-02-01T00:00:00Z
LIMIT 10000

-- 3. Use skinny tables (configured by Salesforce Support)
-- Query fields included in skinny table for best performance

-- 4. Consider archiving old records
SELECT Id FROM Account
WHERE LastActivityDate < LAST_N_YEARS:2
AND CreatedDate < LAST_N_YEARS:3

-- 5. Use Database.query() with query locator for batch
/*
Database.QueryLocator ql = Database.getQueryLocator(
    'SELECT Id, Name FROM Account WHERE CreatedDate = THIS_YEAR'
);
// Can process up to 50 million records in batch
*/

/**
 * RELATIONSHIP QUERY PATTERNS
 *
 * SOQL supports two types of relationships:
 * 1. Child-to-Parent (dot notation) - Traverse up
 * 2. Parent-to-Child (subquery) - Traverse down
 */

// ═══════════════════════════════════════════════════════════════════════════
// CHILD-TO-PARENT (DOT NOTATION)
// ═══════════════════════════════════════════════════════════════════════════

-- Basic parent field access
SELECT Id, Name, Account.Name, Account.Industry
FROM Contact

-- Multiple levels up (max 5 levels)
SELECT Id, Name,
       Account.Name,
       Account.Owner.Name,
       Account.Owner.Manager.Name
FROM Contact

-- Filter by parent field
SELECT Id, Name, Email
FROM Contact
WHERE Account.Industry = 'Technology'
AND Account.AnnualRevenue > 1000000

-- Custom object parent relationship (__r suffix)
SELECT Id, Name, Custom_Parent__r.Name, Custom_Parent__r.Status__c
FROM Custom_Child__c

-- Standard lookup relationships
SELECT Id, Subject, WhatId, What.Name
FROM Task
WHERE What.Type = 'Account'

SELECT Id, CaseNumber, Account.Name, Contact.Name
FROM Case
WHERE Account.Industry = 'Healthcare'

// ═══════════════════════════════════════════════════════════════════════════
// PARENT-TO-CHILD (SUBQUERIES)
// ═══════════════════════════════════════════════════════════════════════════

-- Basic child records
SELECT Id, Name,
       (SELECT Id, FirstName, LastName, Email FROM Contacts)
FROM Account
LIMIT 100

-- Multiple child relationships
SELECT Id, Name,
       (SELECT Id, Name, Email FROM Contacts),
       (SELECT Id, Name, Amount, StageName FROM Opportunities),
       (SELECT Id, CaseNumber, Subject FROM Cases)
FROM Account
WHERE Industry = 'Technology'
LIMIT 50

-- Filtered child records
SELECT Id, Name,
       (SELECT Id, Name, Amount
        FROM Opportunities
        WHERE StageName = 'Closed Won'
        AND Amount > 10000
        ORDER BY Amount DESC
        LIMIT 5)
FROM Account

-- Custom child relationship (__r suffix, plural)
SELECT Id, Name,
       (SELECT Id, Name, Status__c FROM Custom_Children__r)
FROM Custom_Parent__c

-- Order in subquery
SELECT Id, Name,
       (SELECT Id, FirstName, LastName, CreatedDate
        FROM Contacts
        ORDER BY CreatedDate DESC
        LIMIT 10)
FROM Account

// ═══════════════════════════════════════════════════════════════════════════
// COMMON RELATIONSHIP NAMES
// ═══════════════════════════════════════════════════════════════════════════

-- Account relationships
SELECT Id, Name,
       (SELECT Id FROM Contacts),           -- Standard
       (SELECT Id FROM Opportunities),      -- Standard
       (SELECT Id FROM Cases),              -- Standard
       (SELECT Id FROM Notes),              -- Standard
       (SELECT Id FROM Attachments),        -- Standard
       (SELECT Id FROM Tasks),              -- Standard
       (SELECT Id FROM Events)              -- Standard
FROM Account
LIMIT 10

-- Contact relationships
SELECT Id, Name,
       (SELECT Id FROM Cases),
       (SELECT Id FROM Tasks),
       (SELECT Id FROM Events),
       (SELECT Id FROM OpportunityContactRoles)
FROM Contact
LIMIT 10

-- Opportunity relationships
SELECT Id, Name,
       (SELECT Id FROM OpportunityLineItems),
       (SELECT Id FROM OpportunityContactRoles),
       (SELECT Id FROM Tasks)
FROM Opportunity
LIMIT 10

// ═══════════════════════════════════════════════════════════════════════════
// SEMI-JOINS AND ANTI-JOINS
// ═══════════════════════════════════════════════════════════════════════════

-- Semi-join: Accounts WITH contacts
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)

-- Semi-join: Accounts WITH opportunities over $100k
SELECT Id, Name FROM Account
WHERE Id IN (
    SELECT AccountId FROM Opportunity
    WHERE Amount > 100000
    AND StageName = 'Closed Won'
)

-- Anti-join: Accounts WITHOUT contacts
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Contact WHERE AccountId != null)

-- Anti-join: Accounts WITHOUT opportunities
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE AccountId != null)

-- Anti-join: Leads not converted
SELECT Id, Name, Email FROM Lead
WHERE IsConverted = false
AND Id NOT IN (SELECT LeadId FROM Contact WHERE LeadId != null)

-- Multiple semi-joins
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE Email != null)
AND Id IN (SELECT AccountId FROM Opportunity WHERE Amount > 50000)

// ═══════════════════════════════════════════════════════════════════════════
// POLYMORPHIC RELATIONSHIPS (TYPEOF)
// ═══════════════════════════════════════════════════════════════════════════

-- Basic polymorphic query
SELECT Id, Subject, Who.Name, What.Name
FROM Task
WHERE Who.Type = 'Contact'
AND What.Type = 'Opportunity'

-- TYPEOF for conditional fields
SELECT
    Id,
    Subject,
    TYPEOF What
        WHEN Account THEN Name, Industry, AnnualRevenue
        WHEN Opportunity THEN Name, Amount, StageName
        WHEN Case THEN CaseNumber, Subject, Status
    END
FROM Task
WHERE What.Type IN ('Account', 'Opportunity', 'Case')

-- TYPEOF with ELSE clause
SELECT
    Id,
    TYPEOF Owner
        WHEN User THEN FirstName, LastName, Email
        WHEN Group THEN Name
        ELSE Name
    END
FROM Account

// ═══════════════════════════════════════════════════════════════════════════
// ADVANCED PATTERNS
// ═══════════════════════════════════════════════════════════════════════════

-- Get accounts with contact count > 5 (requires Apex filtering)
-- Note: SOQL can't filter by aggregate of subquery
SELECT Id, Name, (SELECT Id FROM Contacts)
FROM Account
-- Then filter in Apex: WHERE Contacts.size() > 5

-- Get hierarchical data (self-reference)
SELECT Id, Name, ParentId, Parent.Name, Parent.Parent.Name
FROM Account
WHERE ParentId != null

-- Get users with role hierarchy
SELECT Id, Name, Manager.Name, Manager.Manager.Name
FROM User
WHERE IsActive = true
AND ManagerId != null

/**
 * ${OBJECT_NAME}Selector - Centralized SOQL queries for ${OBJECT_NAME}
 *
 * This selector class follows the Selector Layer pattern to:
 * - Centralize all ${OBJECT_NAME} queries in one place
 * - Enforce Field-Level Security (FLS) by default
 * - Support query mocking in unit tests
 * - Provide consistent field selection across the codebase
 *
 * @see https://blog.beyondthecloud.dev/blog/why-do-you-need-selector-layer
 * @see https://www.jamessimone.net/blog/joys-of-apex/repository-pattern/
 */
public inherited sharing class ${OBJECT_NAME}Selector {

    // ═══════════════════════════════════════════════════════════════════
    // FIELD DEFINITIONS
    // Centralize field lists to make updates easy
    // ═══════════════════════════════════════════════════════════════════

    /**
     * Standard fields queried in most operations
     */
    private static final List<SObjectField> STANDARD_FIELDS = new List<SObjectField>{
        ${OBJECT_NAME}.Id,
        ${OBJECT_NAME}.Name,
        ${OBJECT_NAME}.OwnerId,
        ${OBJECT_NAME}.CreatedDate,
        ${OBJECT_NAME}.LastModifiedDate
        // Add commonly needed fields here
    };

    /**
     * Extended fields for detail views
     */
    private static final List<SObjectField> DETAIL_FIELDS = new List<SObjectField>{
        ${OBJECT_NAME}.Id,
        ${OBJECT_NAME}.Name,
        ${OBJECT_NAME}.OwnerId,
        ${OBJECT_NAME}.CreatedDate,
        ${OBJECT_NAME}.LastModifiedDate
        // Add detail-specific fields here
    };

    // ═══════════════════════════════════════════════════════════════════
    // MOCKING SUPPORT (for unit tests)
    // ═══════════════════════════════════════════════════════════════════

    @TestVisible
    private static List<${OBJECT_NAME}> mockResults;

    @TestVisible
    private static void setMockResults(List<${OBJECT_NAME}> records) {
        mockResults = records;
    }

    @TestVisible
    private static void clearMockResults() {
        mockResults = null;
    }

    private static Boolean useMock() {
        return Test.isRunningTest() && mockResults != null;
    }

    // ═══════════════════════════════════════════════════════════════════
    // QUERY METHODS
    // ═══════════════════════════════════════════════════════════════════

    /**
     * Query records by their IDs
     *
     * @param recordIds Set of record IDs to query
     * @return List of matching records with standard fields
     */
    public static List<${OBJECT_NAME}> byIds(Set<Id> recordIds) {
        if (recordIds == null || recordIds.isEmpty()) {
            return new List<${OBJECT_NAME}>();
        }
        if (useMock()) {
            return mockResults;
        }
        return [
            SELECT Id, Name, OwnerId, CreatedDate, LastModifiedDate
            FROM ${OBJECT_NAME}
            WHERE Id IN :recordIds
            WITH SECURITY_ENFORCED
        ];
    }

    /**
     * Query records by their IDs, returning a Map for O(1) lookups
     *
     * @param recordIds Set of record IDs to query
     * @return Map of Id to record
     */
    public static Map<Id, ${OBJECT_NAME}> byIdsAsMap(Set<Id> recordIds) {
        return new Map<Id, ${OBJECT_NAME}>(byIds(recordIds));
    }

    /**
     * Query records by Owner
     *
     * @param ownerId The owner's User Id
     * @return List of records owned by the specified user
     */
    public static List<${OBJECT_NAME}> byOwnerId(Id ownerId) {
        if (ownerId == null) {
            return new List<${OBJECT_NAME}>();
        }
        if (useMock()) {
            return mockResults;
        }
        return [
            SELECT Id, Name, OwnerId, CreatedDate, LastModifiedDate
            FROM ${OBJECT_NAME}
            WHERE OwnerId = :ownerId
            WITH SECURITY_ENFORCED
            ORDER BY LastModifiedDate DESC
            LIMIT 1000
        ];
    }

    /**
     * Query records created within a date range
     *
     * @param startDate Start of date range (inclusive)
     * @param endDate End of date range (inclusive)
     * @return List of records created within the range
     */
    public static List<${OBJECT_NAME}> byCreatedDateRange(Date startDate, Date endDate) {
        if (startDate == null || endDate == null) {
            return new List<${OBJECT_NAME}>();
        }
        if (useMock()) {
            return mockResults;
        }
        return [
            SELECT Id, Name, OwnerId, CreatedDate, LastModifiedDate
            FROM ${OBJECT_NAME}
            WHERE CreatedDate >= :startDate
            AND CreatedDate <= :endDate
            WITH SECURITY_ENFORCED
            ORDER BY CreatedDate DESC
            LIMIT 10000
        ];
    }

    // ═══════════════════════════════════════════════════════════════════
    // RELATIONSHIP QUERIES
    // ═══════════════════════════════════════════════════════════════════

    /**
     * Query records with related child records
     * Customize the subquery for your object relationships
     *
     * @param recordIds Set of parent record IDs
     * @return List of parent records with child records populated
     */
    public static List<${OBJECT_NAME}> withChildRecordsByIds(Set<Id> recordIds) {
        if (recordIds == null || recordIds.isEmpty()) {
            return new List<${OBJECT_NAME}>();
        }
        if (useMock()) {
            return mockResults;
        }
        return [
            SELECT Id, Name, OwnerId,
                   (SELECT Id, Name
                    FROM ChildRelationshipName__r  // Replace with actual relationship
                    WHERE IsActive__c = true
                    LIMIT 50)
            FROM ${OBJECT_NAME}
            WHERE Id IN :recordIds
            WITH SECURITY_ENFORCED
        ];
    }

    // ═══════════════════════════════════════════════════════════════════
    // AGGREGATE QUERIES
    // ═══════════════════════════════════════════════════════════════════

    /**
     * Count records by a grouping field
     *
     * @return List of AggregateResult with grouping and count
     */
    public static List<AggregateResult> countByOwner() {
        return [
            SELECT OwnerId, COUNT(Id) recordCount
            FROM ${OBJECT_NAME}
            GROUP BY OwnerId
            HAVING COUNT(Id) > 0
        ];
    }

    // ═══════════════════════════════════════════════════════════════════
    // EXISTENCE CHECK
    // ═══════════════════════════════════════════════════════════════════

    /**
     * Check if a record exists by ID (efficient single record lookup)
     *
     * @param recordId The record ID to check
     * @return true if the record exists
     */
    public static Boolean existsById(Id recordId) {
        if (recordId == null) {
            return false;
        }
        List<${OBJECT_NAME}> results = [
            SELECT Id
            FROM ${OBJECT_NAME}
            WHERE Id = :recordId
            WITH SECURITY_ENFORCED
            LIMIT 1
        ];
        return !results.isEmpty();
    }
}

#!/usr/bin/env python3
"""
Post-Tool Validation Hook for sf-soql plugin.

This hook runs AFTER Write or Edit tool completes and provides validation
feedback for SOQL files (*.soql).

Integrates:
1. Static SOQL validation (syntax, best practices)
2. Live Query Plan Analysis (if org connected)

Hook Input (stdin): JSON with tool_input and tool_response
Hook Output (stdout): JSON with optional output message

This hook is ADVISORY - it provides feedback but does not block operations.
"""

import sys
import os
import json

# Add script directory to path for imports
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.insert(0, SCRIPT_DIR)

# Find shared modules (../../shared relative to sf-soql)
PLUGIN_ROOT = os.path.dirname(os.path.dirname(SCRIPT_DIR))  # sf-soql/
SKILLS_ROOT = os.path.dirname(PLUGIN_ROOT)  # sf-skills/
SHARED_DIR = os.path.join(SKILLS_ROOT, "shared")
sys.path.insert(0, SHARED_DIR)


def validate_soql_file(file_path: str) -> dict:
    """
    Validate a .soql file with static analysis and live query plan.

    Args:
        file_path: Path to .soql file

    Returns:
        dict with validation results and output message
    """
    output_parts = []
    file_name = os.path.basename(file_path)
    issues = []
    recommendations = []

    try:
        # Read file content
        with open(file_path, 'r') as f:
            content = f.read()

        if not content.strip():
            return {"continue": True}

        # ═══════════════════════════════════════════════════════════════════
        # PHASE 1: Static SOQL Validation
        # ═══════════════════════════════════════════════════════════════════
        static_result = validate_soql_static(content)
        issues.extend(static_result.get('issues', []))
        recommendations.extend(static_result.get('recommendations', []))

        # ═══════════════════════════════════════════════════════════════════
        # PHASE 2: Live Query Plan Analysis (if org connected)
        # ═══════════════════════════════════════════════════════════════════
        live_result = None
        org_name = None

        try:
            from code_analyzer.live_query_plan import LiveQueryPlanAnalyzer

            analyzer = LiveQueryPlanAnalyzer()
            if analyzer.is_org_available():
                org_name = analyzer.get_target_org()
                live_result = analyzer.analyze(content)

                if live_result.success:
                    # Add live plan insights as issues/recommendations
                    if not live_result.is_selective:
                        issues.append({
                            'severity': 'WARNING',
                            'message': f'Non-selective query (cost: {live_result.relative_cost:.1f})',
                            'source': 'LivePlan'
                        })

                    # Add notes as recommendations
                    for note in live_result.notes:
                        recommendations.append(str(note))

                    # Get optimization suggestions
                    suggestions = analyzer.get_optimization_suggestions(live_result)
                    recommendations.extend(suggestions)

        except ImportError:
            pass  # Live analysis not available
        except Exception as e:
            pass  # Don't fail on live analysis errors

        # ═══════════════════════════════════════════════════════════════════
        # PHASE 3: Format Output
        # ═══════════════════════════════════════════════════════════════════
        output_parts.append("")
        output_parts.append(f"🔍 SOQL Validation: {file_name}")
        output_parts.append("═" * 55)

        # Static analysis summary
        if static_result.get('has_where_clause'):
            output_parts.append("✅ Has WHERE clause")
        else:
            output_parts.append("⚠️ Missing WHERE clause")

        if static_result.get('has_limit'):
            output_parts.append("✅ Has LIMIT clause")
        else:
            output_parts.append("⚠️ Missing LIMIT clause")

        if static_result.get('has_hardcoded_ids'):
            output_parts.append("⚠️ Contains hardcoded IDs")

        # Live Query Plan section
        output_parts.append("")
        if live_result and live_result.success:
            output_parts.append(f"🌐 Live Query Plan Analysis")
            output_parts.append(f"   Org: {org_name}")
            output_parts.append(f"   {live_result.icon} Selective: {live_result.is_selective}")
            output_parts.append(f"   📊 Relative Cost: {live_result.relative_cost:.2f} ({live_result.selectivity_rating})")
            output_parts.append(f"   📈 Operation: {live_result.leading_operation}")

            if live_result.cardinality > 0:
                output_parts.append(f"   📋 Cardinality: {live_result.cardinality:,} / {live_result.sobject_cardinality:,}")

            if live_result.notes:
                output_parts.append("")
                output_parts.append("   📝 Query Plan Notes:")
                for note in live_result.notes[:3]:
                    output_parts.append(f"      • {str(note)[:70]}")
        elif org_name is None:
            output_parts.append("🌐 Live Query Plan: No org connected")
            output_parts.append("   Run 'sf org login web' to enable live analysis")
        elif live_result and not live_result.success:
            output_parts.append(f"🌐 Live Query Plan: Error")
            output_parts.append(f"   {live_result.error[:60]}")

        # Issues
        if issues:
            output_parts.append("")
            output_parts.append(f"⚠️ Issues ({len(issues)}):")
            severity_icons = {
                'CRITICAL': '🔴', 'HIGH': '🟠', 'MODERATE': '🟡',
                'WARNING': '⚠️', 'LOW': '🔵', 'INFO': 'ℹ️'
            }
            for issue in issues[:5]:
                icon = severity_icons.get(issue.get('severity', 'INFO'), 'ℹ️')
                source = f"[{issue.get('source', '')}]" if issue.get('source') else ""
                output_parts.append(f"   {icon} {source} {issue.get('message', '')[:60]}")

        # Recommendations
        unique_recs = list(dict.fromkeys(recommendations))  # Remove duplicates
        if unique_recs:
            output_parts.append("")
            output_parts.append("💡 Recommendations:")
            for rec in unique_recs[:5]:
                output_parts.append(f"   • {rec[:65]}")

        output_parts.append("═" * 55)

        return {
            "continue": True,
            "output": "\n".join(output_parts)
        }

    except Exception as e:
        return {
            "continue": True,
            "output": f"⚠️ SOQL validation error: {e}"
        }


def validate_soql_static(content: str) -> dict:
    """
    Perform static validation on SOQL content.

    Args:
        content: SOQL query string

    Returns:
        dict with validation flags and issues
    """
    import re

    result = {
        'is_valid': True,
        'has_where_clause': False,
        'has_limit': False,
        'has_order_by': False,
        'has_hardcoded_ids': False,
        'uses_indexed_fields': False,
        'issues': [],
        'recommendations': []
    }

    # Remove comments
    clean = re.sub(r'--.*$', '', content, flags=re.MULTILINE)
    clean = re.sub(r'//.*$', '', clean, flags=re.MULTILINE)
    clean = re.sub(r'/\*[\s\S]*?\*/', '', clean)

    # Check for WHERE clause
    result['has_where_clause'] = bool(re.search(r'\bWHERE\b', clean, re.IGNORECASE))

    # Check for LIMIT
    result['has_limit'] = bool(re.search(r'\bLIMIT\s+\d+', clean, re.IGNORECASE))

    # Check for ORDER BY
    result['has_order_by'] = bool(re.search(r'\bORDER\s+BY\b', clean, re.IGNORECASE))

    # Check for hardcoded IDs (15 or 18 char alphanumeric in quotes)
    result['has_hardcoded_ids'] = bool(
        re.search(r"'[a-zA-Z0-9]{15}'", clean) or
        re.search(r"'[a-zA-Z0-9]{18}'", clean)
    )

    # Check for indexed fields in WHERE
    indexed_fields = ['Id', 'Name', 'OwnerId', 'CreatedDate', 'LastModifiedDate', 'RecordTypeId']
    where_match = re.search(r'\bWHERE\b(.*?)(?:\bORDER\b|\bGROUP\b|\bLIMIT\b|$)', clean, re.IGNORECASE | re.DOTALL)
    if where_match:
        where_clause = where_match.group(1)
        for field in indexed_fields:
            if re.search(rf'\b{field}\b', where_clause, re.IGNORECASE):
                result['uses_indexed_fields'] = True
                break

    # Syntax validation
    # Check for SELECT without FROM
    if re.search(r'\bSELECT\b', clean, re.IGNORECASE):
        if not re.search(r'\bFROM\b', clean, re.IGNORECASE):
            result['issues'].append({
                'severity': 'HIGH',
                'message': 'SELECT statement missing FROM clause'
            })
            result['is_valid'] = False

    # Check for SELECT *
    if re.search(r'\bSELECT\s+\*', clean, re.IGNORECASE):
        result['issues'].append({
            'severity': 'HIGH',
            'message': 'SELECT * is not valid in SOQL - specify field names'
        })
        result['is_valid'] = False

    # Check for == instead of =
    if re.search(r'==', clean):
        result['issues'].append({
            'severity': 'HIGH',
            'message': 'Invalid operator "==" - use "=" in SOQL'
        })

    # Check for unbalanced parentheses
    if clean.count('(') != clean.count(')'):
        result['issues'].append({
            'severity': 'HIGH',
            'message': 'Unbalanced parentheses'
        })

    # Add recommendations
    if not result['has_where_clause']:
        result['recommendations'].append('Add WHERE clause for better query selectivity')

    if not result['has_limit']:
        result['recommendations'].append('Add LIMIT clause to prevent large result sets')

    if result['has_hardcoded_ids']:
        result['recommendations'].append('Avoid hardcoded IDs - use bind variables instead')

    if result['has_where_clause'] and not result['uses_indexed_fields']:
        result['recommendations'].append('Add an indexed field (Id, Name, CreatedDate) to WHERE for better performance')

    return result


def main():
    """
    Main hook entry point.

    Reads hook input from stdin, validates SOQL files.
    """
    try:
        # Read hook input from stdin
        hook_input = json.load(sys.stdin)

        # Extract file path from tool input
        tool_input = hook_input.get("tool_input", {})
        file_path = tool_input.get("file_path", "")

        # Check if operation was successful
        tool_response = hook_input.get("tool_response", {})
        if not tool_response.get("success", True):
            print(json.dumps({"continue": True}))
            return 0

        # Only validate .soql files
        result = {"continue": True}

        if file_path.lower().endswith(".soql"):
            result = validate_soql_file(file_path)

        # Output result
        print(json.dumps(result))
        return 0

    except json.JSONDecodeError:
        print(json.dumps({"continue": True}))
        return 0
    except Exception as e:
        print(json.dumps({
            "continue": True,
            "output": f"⚠️ Hook error: {e}"
        }))
        return 0


if __name__ == "__main__":
    sys.exit(main())