-- Add fields to case_hours and create case_hour_documents table

ALTER TABLE case_hours
    ADD COLUMN IF NOT EXISTS title VARCHAR(255),
    ADD COLUMN IF NOT EXISTS instructions TEXT,
    ADD COLUMN IF NOT EXISTS posted BOOLEAN DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS posted_at TIMESTAMP,
    ADD COLUMN IF NOT EXISTS approval_comment TEXT;

CREATE TABLE IF NOT EXISTS case_hour_documents (
    id SERIAL PRIMARY KEY,
    case_hour_id INTEGER NOT NULL,
    document_id INTEGER NOT NULL,
    tenant_id INTEGER DEFAULT NULL,
    branch_id INTEGER DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS case_hour_documents_hour_idx ON case_hour_documents(case_hour_id);
CREATE INDEX IF NOT EXISTS case_hour_documents_doc_idx ON case_hour_documents(document_id);
