-- Create view for current (newest) field extraction per document
CREATE VIEW currentFieldExtractions AS
SELECT DISTINCT ON (dfe.documentId)
    dfe.id,
    dfe.documentId,
    dfe.fileName,
    dfe.contractTitle,
    dfe.aareteDerivedAmendmentNum,
    dfe.clientName,
    dfe.payerName,
    dfe.payerState,
    dfe.providerState,
    dfe.filenameTin,
    dfe.provGroupTin,
    dfe.provGroupNpi,
    dfe.provGroupNameFull,
    dfe.provOtherTin,
    dfe.provOtherNpi,
    dfe.provOtherNameFull,
    dfe.aareteDerivedEffectiveDt,
    dfe.aareteDerivedTerminationDt,
    dfe.autoRenewalInd,
    dfe.autoRenewalTerm,
    dfev.version,
    dfev.createdBy,
    dfev.createdAt
FROM documentFieldExtractions dfe
JOIN documentFieldExtractionVersions dfev
    ON dfev.fieldExtractionId = dfe.id
ORDER BY dfe.documentId, dfev.id DESC;

-- Create extended view with array field count
CREATE VIEW currentFieldExtractionsWithArrayCount AS
SELECT
    cfe.*,
    COALESCE(array_counts.arraySize, 0) as arraySize
FROM currentFieldExtractions cfe
LEFT JOIN (
    SELECT
        fieldExtractionId,
        COUNT(*) as arraySize
    FROM documentFieldExtractionArrayFields
    GROUP BY fieldExtractionId
) array_counts ON array_counts.fieldExtractionId = cfe.id;