-- Migration 005: Create scope template tables
-- Templates are reusable blueprints that can be instantiated as ServiceScope or ProjectScope
-- Structure: ScopeTemplate → ScopeTemplateArea → ScopeTemplateTask

-- Reusable scope template
CREATE TABLE scope_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    name VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

-- Area within a scope template
CREATE TABLE scope_template_areas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    template_id UUID NOT NULL REFERENCES scope_templates(id) ON DELETE CASCADE,

    name VARCHAR(100) NOT NULL,
    "order" INTEGER NOT NULL CHECK ("order" >= 0)
);

-- Task within a scope template area
-- Has THREE descriptions for different audiences
CREATE TABLE scope_template_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    area_id UUID NOT NULL REFERENCES scope_template_areas(id) ON DELETE CASCADE,

    -- Three descriptions for different audiences
    scope_description TEXT NOT NULL,         -- Customer-facing
    checklist_description TEXT NOT NULL,     -- QA/punchlist format
    session_description TEXT NOT NULL,       -- Team member work instructions

    frequency task_frequency NOT NULL DEFAULT 'DAILY',
    "order" INTEGER NOT NULL CHECK ("order" >= 0),
    estimated_minutes INTEGER CHECK (estimated_minutes >= 0)
);

-- Indexes
CREATE INDEX idx_scope_templates_active ON scope_templates(is_active);
CREATE INDEX idx_scope_template_areas_template ON scope_template_areas(template_id);
CREATE INDEX idx_scope_template_tasks_area ON scope_template_tasks(area_id);