-- Migration 024: Rename scope templates to service_scope_templates and create project_scope_templates
-- This provides consistent naming: service_scope_templates vs project_scope_templates
-- Note: Service tasks have frequency, project tasks do not

-- ==================== RENAME SERVICE SCOPE TEMPLATES ====================

-- Rename tables for consistency
ALTER TABLE scope_templates RENAME TO service_scope_templates;
ALTER TABLE scope_template_areas RENAME TO service_scope_template_areas;
ALTER TABLE scope_template_tasks RENAME TO service_scope_template_tasks;

-- Rename indexes
ALTER INDEX idx_scope_templates_active RENAME TO idx_service_scope_templates_active;
ALTER INDEX idx_scope_template_areas_template RENAME TO idx_service_scope_template_areas_template;
ALTER INDEX idx_scope_template_tasks_area RENAME TO idx_service_scope_template_tasks_area;

-- ==================== CREATE PROJECT SCOPE TEMPLATES ====================

-- Reusable project scope template
CREATE TABLE project_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
);

-- Category within a project scope template (equivalent to Area for services)
CREATE TABLE project_scope_template_categories (
    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 project_scope_templates(id) ON DELETE CASCADE,

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

-- Task within a project scope template category
-- Note: No frequency field - project tasks are one-time
CREATE TABLE project_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(),

    category_id UUID NOT NULL REFERENCES project_scope_template_categories(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

    "order" INTEGER NOT NULL CHECK ("order" >= 0),
    estimated_minutes INTEGER CHECK (estimated_minutes >= 0)
);

-- Indexes for project scope templates
CREATE INDEX idx_project_scope_templates_active ON project_scope_templates(is_active);
CREATE INDEX idx_project_scope_template_categories_template ON project_scope_template_categories(template_id);
CREATE INDEX idx_project_scope_template_tasks_category ON project_scope_template_tasks(category_id);

-- Note: Data migration from Django tables removed as they don't exist in production.
-- If needed locally, run INSERT statements manually from core_projectscopetemplate,
-- core_projectareatemplate, and core_projecttasktemplate tables.