-- Create file_versions table to store version history
CREATE TABLE IF NOT EXISTS file_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    version INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    summary TEXT,
    body JSONB NOT NULL DEFAULT '[]'::jsonb,
    source VARCHAR(32) NOT NULL DEFAULT 'user', -- 'user', 'llm', or 'system'
    change_description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Each file can only have one record per version number
    UNIQUE(file_id, version)
);

-- Index for efficient version lookups
CREATE INDEX idx_file_versions_file_id ON file_versions(file_id);
CREATE INDEX idx_file_versions_file_version ON file_versions(file_id, version DESC);

-- Function to save a version snapshot before file updates
CREATE OR REPLACE FUNCTION save_file_version()
RETURNS TRIGGER AS $$
BEGIN
    -- Save the current state as a version before the update
    INSERT INTO file_versions (file_id, version, name, description, summary, body, source, change_description, created_at)
    VALUES (
        OLD.id,
        OLD.version,
        OLD.name,
        OLD.description,
        OLD.summary,
        OLD.body,
        COALESCE(current_setting('app.version_source', true), 'user'),
        current_setting('app.change_description', true),
        OLD.updated_at
    )
    ON CONFLICT (file_id, version) DO NOTHING;

    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to automatically save versions on update
CREATE TRIGGER save_file_version_trigger
    BEFORE UPDATE ON files
    FOR EACH ROW
    EXECUTE FUNCTION save_file_version();