DROP VIEW "public"."template_detail_view";--> statement-breakpoint
DROP VIEW "public"."template_list_view";--> statement-breakpoint
ALTER TABLE "template_history" ADD COLUMN "subject" text NOT NULL;--> statement-breakpoint
ALTER TABLE "templates" ADD COLUMN "subject" text NOT NULL;--> statement-breakpoint
CREATE VIEW "public"."template_detail_view" AS (
  SELECT
    t.id,
    t.name,
    t.slug,
    t.subject,
    t.content,
    t.description,
    t.category,
    t.sample_data,
    t.is_active,
    t.version,
    t.created_by,
    u.name  AS created_by_name,
    u.email AS created_by_email,
    t.created_at,
    t.updated_at,
    COALESCE(
      json_agg(
        json_build_object(
          'id', v.id,
          'variableName', v.variable_name,
          'variableType', v.variable_type,
          'defaultValue', v.default_value,
          'isRequired', v.is_required,
          'description', v.description,
          'displayOrder', v.display_order
        ) ORDER BY v.display_order
      ) FILTER (WHERE v.id IS NOT NULL),
      '[]'::json
    ) AS variables
  FROM "templates" t
  LEFT JOIN "users" u ON t.created_by = u.id
  LEFT JOIN "template_variables" v ON t.id = v.template_id
  GROUP BY
    t.id,
    t.name,
    t.slug,
    t.subject,
    t.content,
    t.description,
    t.category,
    t.sample_data,
    t.is_active,
    t.version,
    t.created_by,
    u.name,
    u.email,
    t.created_at,
    t.updated_at
);--> statement-breakpoint
CREATE VIEW "public"."template_list_view" AS (
  SELECT
    t.id,
    t.name,
    t.slug,
    t.subject,
    t.description,
    t.category,
    t.is_active,
    t.version,
    t.created_by,
    u.name  AS created_by_name,
    u.email AS created_by_email,
    t.created_at,
    t.updated_at,
    COALESCE(v.variable_count, 0) AS variable_count,
    COALESCE(v.required_variable_count, 0) AS required_variable_count
  FROM "templates" t
  LEFT JOIN "users" u ON t.created_by = u.id
  LEFT JOIN (
    SELECT
      template_id,
      COUNT(*) AS variable_count,
      COUNT(*) FILTER (WHERE is_required) AS required_variable_count
    FROM "template_variables"
    GROUP BY template_id
  ) v ON t.id = v.template_id
);