CREATE TABLE IF NOT EXISTS internet_connections_customer_documents (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    connection_id INTEGER REFERENCES internet_connections_connections(id) ON DELETE SET NULL,
    filename VARCHAR(500) NOT NULL,
    original_filename VARCHAR(500) NOT NULL,
    file_path VARCHAR(1000) NOT NULL,
    file_size INTEGER,
    mime_type VARCHAR(120),
    checksum VARCHAR(64) NOT NULL,
    extracted_text TEXT,
    notes TEXT,
    uploaded_by INTEGER,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_internet_customer_documents_customer
ON internet_connections_customer_documents(customer_id)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_internet_customer_documents_connection
ON internet_connections_customer_documents(connection_id)
WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_internet_customer_documents_checksum
ON internet_connections_customer_documents(checksum);

CREATE INDEX IF NOT EXISTS idx_internet_customer_documents_created_at
ON internet_connections_customer_documents(created_at DESC)
WHERE deleted_at IS NULL;

CREATE OR REPLACE FUNCTION update_internet_customer_documents_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_internet_customer_documents_updated_at
ON internet_connections_customer_documents;

CREATE TRIGGER trigger_update_internet_customer_documents_updated_at
    BEFORE UPDATE ON internet_connections_customer_documents
    FOR EACH ROW
    EXECUTE FUNCTION update_internet_customer_documents_updated_at();

COMMENT ON TABLE internet_connections_customer_documents IS 'Kundeoplaeste tekstfiler til internetforbindelser og historisk research';
COMMENT ON COLUMN internet_connections_customer_documents.extracted_text IS 'Udtrukket/forsynlig tekst som wizard v2 og AI kan soege i';

CREATE TABLE IF NOT EXISTS internet_connections_customer_document_segments (
    id SERIAL PRIMARY KEY,
    document_id INTEGER NOT NULL REFERENCES internet_connections_customer_documents(id) ON DELETE CASCADE,
    block_index INTEGER NOT NULL,
    block_title VARCHAR(255),
    content TEXT NOT NULL,
    ip_addresses JSONB NOT NULL DEFAULT '[]'::jsonb,
    cidr_blocks JSONB NOT NULL DEFAULT '[]'::jsonb,
    references_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    socket_numbers JSONB NOT NULL DEFAULT '[]'::jsonb,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(document_id, block_index)
);

CREATE INDEX IF NOT EXISTS idx_internet_customer_document_segments_document
ON internet_connections_customer_document_segments(document_id);