-- Document ↔ klant/project koppelingen + auto-ingest log

CREATE TABLE IF NOT EXISTS document_links (
    id SERIAL PRIMARY KEY,
    storage_path TEXT NOT NULL,
    is_folder BOOLEAN NOT NULL DEFAULT FALSE,
    client_id INT REFERENCES clients(id) ON DELETE CASCADE,
    project_id INT REFERENCES cockpit_projects(id) ON DELETE SET NULL,
    link_type VARCHAR(32) NOT NULL DEFAULT 'nas_share',
    notes TEXT DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by VARCHAR(64) DEFAULT 'cockpit'
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_document_links_unique
    ON document_links (storage_path, COALESCE(client_id, 0), COALESCE(project_id, 0));

CREATE INDEX IF NOT EXISTS idx_document_links_client ON document_links (client_id);
CREATE INDEX IF NOT EXISTS idx_document_links_project ON document_links (project_id);
CREATE INDEX IF NOT EXISTS idx_document_links_path ON document_links (storage_path);

CREATE TABLE IF NOT EXISTS ingest_automation_log (
    id SERIAL PRIMARY KEY,
    source VARCHAR(64) NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ok',
    details JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ingest_automation_log_at ON ingest_automation_log (created_at DESC);