-- PostgreSQL Audit Schema for Knowledge Base Tracking
-- Task 1.2: Create 3 tables with audit trail functionality

-- Table 1: knowledge_base_updates
-- Tracks all knowledge base updates with approval workflow
CREATE TABLE IF NOT EXISTS knowledge_base_updates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ticket_id INTEGER NOT NULL,
    problem_text TEXT NOT NULL,
    kategorie VARCHAR(100),
    lösung TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP,
    executed_at TIMESTAMP,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    freescout_note TEXT,
    CONSTRAINT status_check CHECK (status IN ('PENDING', 'APPROVED', 'REJECTED', 'EXECUTED'))
);

-- Indexes for knowledge_base_updates
CREATE INDEX idx_kbu_ticket_id ON knowledge_base_updates(ticket_id);
CREATE INDEX idx_kbu_status ON knowledge_base_updates(status);
CREATE INDEX idx_kbu_kategorie ON knowledge_base_updates(kategorie);

-- Table 2: kb_feedback
-- Stores user feedback on knowledge base entries
CREATE TABLE IF NOT EXISTS kb_feedback (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    kb_entry_id VARCHAR(255) NOT NULL,
    feedback VARCHAR(50) NOT NULL,
    reason TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT feedback_check CHECK (feedback IN ('helpful', 'not_helpful'))
);

-- Index for kb_feedback
CREATE INDEX idx_kbf_entry_id ON kb_feedback(kb_entry_id);

-- Table 3: workflow_executions
-- Tracks workflow execution history
CREATE TABLE IF NOT EXISTS workflow_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_name VARCHAR(255),
    ticket_id INTEGER,
    status VARCHAR(50),
    error_message TEXT,
    execution_time_ms INTEGER,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for workflow_executions
CREATE INDEX idx_we_ticket_id ON workflow_executions(ticket_id);
CREATE INDEX idx_we_workflow_name ON workflow_executions(workflow_name);

-- Grant permissions to kb_user
GRANT SELECT, INSERT, UPDATE, DELETE ON knowledge_base_updates TO kb_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON kb_feedback TO kb_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON workflow_executions TO kb_user;