-- Directive Order Groups (DOGs): Epic-like groupings of orders within a directive.
CREATE TABLE directive_order_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
    name VARCHAR(500) NOT NULL,
    description TEXT,
    status VARCHAR(32) NOT NULL DEFAULT 'open'
        CHECK (status IN ('open', 'in_progress', 'done', 'archived')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_dog_directive_id ON directive_order_groups(directive_id);
CREATE INDEX IF NOT EXISTS idx_dog_owner_id ON directive_order_groups(owner_id);

-- Add optional dog_id to orders
ALTER TABLE orders ADD COLUMN dog_id UUID REFERENCES directive_order_groups(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_orders_dog_id ON orders(dog_id);