-- Add document categories table and category_id column

CREATE TABLE IF NOT EXISTS document_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT DEFAULT 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 document_categories_tenant_idx ON document_categories(tenant_id);
CREATE INDEX IF NOT EXISTS document_categories_branch_idx ON document_categories(branch_id);

ALTER TABLE documents ADD COLUMN IF NOT EXISTS category_id INTEGER;
CREATE INDEX IF NOT EXISTS documents_category_idx ON documents(category_id);
