-- Rollback migration to restore text extraction functionality
-- NOTE: This recreates text extraction tables and views

-- First drop the simplified fullActiveCollectors view
DROP VIEW IF EXISTS fullActiveCollectors;

-- Recreate documentTextExtractions table
CREATE TABLE documentTextExtractions (
    id uuid primary key DEFAULT uuid_generate_v7(),
    cleanId uuid not null,
    bucket text not null,
    key text not null,
    hash text not null,
    createdAt timestamp not null,
    part unsignedsmallint not null,
    foreign key (cleanId) references documentCleans(id)
);

-- Recreate documentTextExtractionEntries table
CREATE TABLE documentTextExtractionEntries (
    id uuid primary key DEFAULT uuid_generate_v7(),
    textId uuid not null,
    version bigint not null,
    foreign key (textId) references documentTextExtractions(id)
);

-- Recreate collectorMinTextVersions table
CREATE TABLE collectorMinTextVersions (
    id uuid primary key DEFAULT uuid_generate_v7(),
    clientId varchar(255) not null,
    versionId bigint not null,
    addedVersion int not null,
    removedVersion int,
    foreign key (clientId, addedVersion) references collectorVersions(clientId, id),
    foreign key (clientId, removedVersion) references collectorVersions(clientId, id),
    foreign key (clientId) references clients(clientId)
);

-- Recreate removeMinTextVersion function
CREATE OR REPLACE FUNCTION removeMinTextVersion()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE collectorMinTextVersions
        SET removedVersion = NEW.addedVersion
        WHERE clientId = NEW.clientId and removedVersion is null;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Recreate trigger for collectorMinTextVersions
CREATE TRIGGER removeMinTextVersionTrigger
BEFORE INSERT ON collectorMinTextVersions
FOR EACH ROW
EXECUTE FUNCTION removeMinTextVersion();

-- Recreate currentCollectorMinTextVersions view
CREATE VIEW currentCollectorMinTextVersions as
SELECT DISTINCT
        av.clientId,
        coalesce(ctv.versionId, 0) as minTextVersion
    FROM collectorCurrentActiveVersions as av
    LEFT JOIN collectorMinTextVersions AS ctv ON av.clientId = ctv.clientId
        and isInVersion(av.activeVersion, ctv.addedVersion, ctv.removedVersion);

-- Recreate fullActiveCollectors view with minTextVersion
CREATE VIEW fullActiveCollectors AS
    SELECT DISTINCT av.clientId,
    ccv.minCleanVersion, ctv.minTextVersion,
    av.activeVersion, lv.latestVersion
    FROM collectorCurrentActiveVersions as av
    JOIN collectorLatestVersions as lv on lv.clientId = av.clientId
    JOIN currentCollectorMinCleanVersions AS ccv ON av.clientId = ccv.clientId
    JOIN currentCollectorMinTextVersions AS ctv ON av.clientId = ctv.clientId;

-- Recreate currentTextEntries view
CREATE VIEW currentTextEntries as
WITH RankedExtractions AS (
    SELECT
        extract.id,
        cc.documentId,
        extract.bucket,
        extract.key,
        extract.hash,
        extract.cleanId,
        dtee.version as extractionVersion,
        ROW_NUMBER() OVER (PARTITION BY cc.documentId ORDER BY dtee.id DESC) as row_num
    FROM documents d
    JOIN currentCleanEntries cc on cc.documentId = d.id
    JOIN currentCollectorMinTextVersions ctv ON ctv.clientId = d.clientId
    JOIN documentTextExtractions extract ON extract.cleanId = cc.id
    JOIN documentTextExtractionEntries dtee on dtee.textId = extract.id
        AND dtee.version >= ctv.minTextVersion
)
SELECT
    id,
    documentId,
    bucket,
    key,
    hash,
    cleanId,
    extractionVersion
FROM RankedExtractions
WHERE row_num = 1;