-- Generated by ATMigrator on 2026-07-30T12:26:49.701Z
-- Migration: 1c4dd594-81ff-4f4c-98ed-e6413865145f
-- Schema: public
-- Tables: 2 · Views: 2
-- Complex formula showcase: projects + tasks, with live SQL in vt_projects / vt_tasks.

CREATE SEQUENCE IF NOT EXISTS projects_id_seq;
CREATE SEQUENCE IF NOT EXISTS tasks_id_seq;

CREATE TABLE "projects" (
  "id" INTEGER NOT NULL DEFAULT nextval('projects_id_seq'::regclass),
  "_at_id" CHARACTER VARYING,
  "_at_hash" CHARACTER VARYING,
  "project_name" TEXT,
  "start_date" TIMESTAMP WITH TIME ZONE,
  "due_date" TIMESTAMP WITH TIME ZONE,
  "completion_percentage" NUMERIC,
  "priority_level" NUMERIC,
  "budget_amount" NUMERIC,
  "actual_spend" NUMERIC,
  "team_size" NUMERIC,
  "complexity_score" NUMERIC,
  "days_remaining" TEXT,
  "project_duration_business_days" TEXT,
  "status_flag" TEXT,
  "budget_variance" TEXT,
  "weighted_project_score" TEXT,
  "dynamic_summary_text" TEXT,
  PRIMARY KEY ("id")
);

CREATE TABLE "tasks" (
  "id" INTEGER NOT NULL DEFAULT nextval('tasks_id_seq'::regclass),
  "_at_id" CHARACTER VARYING,
  "_at_hash" CHARACTER VARYING,
  "task_name" TEXT,
  "project" TEXT,
  "assigned_to" TEXT,
  "start_date" TIMESTAMP WITH TIME ZONE,
  "target_date" TIMESTAMP WITH TIME ZONE,
  "actual_completion_date" TIMESTAMP WITH TIME ZONE,
  "effort_hours" NUMERIC,
  "difficulty_rating" NUMERIC,
  "dependencies_count" NUMERIC,
  "status" TEXT,
  "business_days_until_deadline" TEXT,
  "overdue_days" TEXT,
  "task_tier" TEXT,
  "completion_score" TEXT,
  "risk_level" TEXT,
  "formatted_status_message" TEXT,
  "assignee" TEXT,
  "kanban_tier" TEXT,
  "task_group" TEXT,
  "task_group_kanban_stack" TEXT,
  "kanban_group" TEXT,
  "kanban_group_task_tier" TEXT,
  "kanban_status_group" TEXT,
  "task_kanban_group" TEXT,
  "kanban_tier_group" TEXT,
  PRIMARY KEY ("id"),
  CONSTRAINT "tasks_project_fk" FOREIGN KEY ("project") REFERENCES "projects"("_at_id")
);

CREATE OR REPLACE VIEW "vt_projects" AS
SELECT t.id,
    t._at_id,
    t._at_hash,
    t.project_name,
    t.start_date,
    t.due_date,
    t.completion_percentage,
    t.priority_level,
    t.budget_amount,
    t.actual_spend,
    t.team_size,
    t.complexity_score,
    _cv0.days_remaining,
    _cv1.project_duration_business_days,
    _cv2.status_flag,
    _cv3.budget_variance,
    _cv4.weighted_project_score,
    _cv5.dynamic_summary_text
   FROM ((((((projects t
     LEFT JOIN ( SELECT projects._at_id AS source_id,
            (
                CASE
                    WHEN (((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN (0)::bigint
                    ELSE
                    CASE
                        WHEN ((((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower(((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM (projects.due_date - (CURRENT_DATE)::timestamp with time zone)) / (86400)::numeric))::bigint
                        ELSE NULL::bigint
                    END
                END)::text AS days_remaining
           FROM projects) _cv0 ON (((_cv0.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT projects._at_id AS source_id,
            (
                CASE
                    WHEN ((projects.start_date IS NULL) OR (projects.due_date IS NULL)) THEN NULL::bigint
                    ELSE (
                    CASE
                        WHEN (((projects.due_date AT TIME ZONE 'UTC'::text))::date < ((projects.start_date AT TIME ZONE 'UTC'::text))::date) THEN '-1'::integer
                        ELSE 1
                    END * ( SELECT count(*) AS count
                       FROM generate_series((LEAST(((projects.start_date AT TIME ZONE 'UTC'::text))::date, ((projects.due_date AT TIME ZONE 'UTC'::text))::date))::timestamp with time zone, (GREATEST(((projects.start_date AT TIME ZONE 'UTC'::text))::date, ((projects.due_date AT TIME ZONE 'UTC'::text))::date))::timestamp with time zone, '1 day'::interval) d(d)
                      WHERE (EXTRACT(isodow FROM d.d) < (6)::numeric)))
                END)::text AS project_duration_business_days
           FROM projects) _cv1 ON (((_cv1.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT projects._at_id AS source_id,
                CASE
                    WHEN (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) >= (1)::numeric) THEN 'Completed'::text
                    WHEN ((projects.due_date IS NOT NULL) AND (((projects.due_date)::date - CURRENT_DATE) < 0) AND (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) < (1)::numeric)) THEN 'Overdue'::text
                    WHEN ((projects.due_date IS NOT NULL) AND ((((projects.due_date)::date - CURRENT_DATE) >= 0) AND (((projects.due_date)::date - CURRENT_DATE) <= 5)) AND (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) < (1)::numeric)) THEN 'At Risk'::text
                    WHEN (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) < (1)::numeric) THEN 'On Track'::text
                    ELSE ''::text
                END AS status_flag
           FROM projects) _cv2 ON (((_cv2.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT projects._at_id AS source_id,
            (
                CASE
                    WHEN ((((projects.budget_amount = (0)::numeric))::text IS NOT NULL) AND (lower(((projects.budget_amount = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN NULL::numeric
                    ELSE ((projects.actual_spend - projects.budget_amount) / projects.budget_amount)
                END)::text AS budget_variance
           FROM projects) _cv3 ON (((_cv3.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT projects._at_id AS source_id,
            (((
                CASE projects.priority_level
                    WHEN 5 THEN (2)::numeric
                    WHEN 4 THEN 1.5
                    WHEN 3 THEN (1)::numeric
                    WHEN 2 THEN 0.75
                    WHEN 1 THEN 0.5
                    ELSE (1)::numeric
                END * projects.complexity_score) *
                CASE
                    WHEN ((((projects.budget_amount > (0)::numeric))::text IS NOT NULL) AND (lower(((projects.budget_amount > (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((1)::numeric - (projects.actual_spend / projects.budget_amount))
                    ELSE (1)::numeric
                END))::text AS weighted_project_score
           FROM projects) _cv4 ON (((_cv4.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT projects._at_id AS source_id,
            concat(concat(concat(concat(concat(concat('Status: ',
                CASE
                    WHEN (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) >= (1)::numeric) THEN 'Completed'::text
                    WHEN ((projects.due_date IS NOT NULL) AND (((projects.due_date)::date - CURRENT_DATE) < 0) AND (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) < (1)::numeric)) THEN 'Overdue'::text
                    WHEN ((projects.due_date IS NOT NULL) AND ((((projects.due_date)::date - CURRENT_DATE) >= 0) AND (((projects.due_date)::date - CURRENT_DATE) <= 5)) AND (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) < (1)::numeric)) THEN 'At Risk'::text
                    WHEN (COALESCE(
                    CASE
                        WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                        ELSE (projects.completion_percentage)::numeric
                    END, (0)::numeric) < (1)::numeric) THEN 'On Track'::text
                    ELSE ''::text
                END), ' | Start: '),
                CASE
                    WHEN (((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN to_char(projects.start_date, 'YYYY-MM-DD'::text)
                    ELSE 'N/A'::text
                END), ' | Due: '),
                CASE
                    WHEN (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN to_char(projects.due_date, 'YYYY-MM-DD'::text)
                    ELSE 'N/A'::text
                END),
                CASE
                    WHEN ((((
                    CASE
                        WHEN (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) >= (1)::numeric) THEN 'Completed'::text
                        WHEN ((projects.due_date IS NOT NULL) AND (((projects.due_date)::date - CURRENT_DATE) < 0) AND (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) < (1)::numeric)) THEN 'Overdue'::text
                        WHEN ((projects.due_date IS NOT NULL) AND ((((projects.due_date)::date - CURRENT_DATE) >= 0) AND (((projects.due_date)::date - CURRENT_DATE) <= 5)) AND (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) < (1)::numeric)) THEN 'At Risk'::text
                        WHEN (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) < (1)::numeric) THEN 'On Track'::text
                        ELSE ''::text
                    END = 'Overdue'::text))::text IS NOT NULL) AND (lower(((
                    CASE
                        WHEN (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) >= (1)::numeric) THEN 'Completed'::text
                        WHEN ((projects.due_date IS NOT NULL) AND (((projects.due_date)::date - CURRENT_DATE) < 0) AND (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) < (1)::numeric)) THEN 'Overdue'::text
                        WHEN ((projects.due_date IS NOT NULL) AND ((((projects.due_date)::date - CURRENT_DATE) >= 0) AND (((projects.due_date)::date - CURRENT_DATE) <= 5)) AND (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) < (1)::numeric)) THEN 'At Risk'::text
                        WHEN (COALESCE(
                        CASE
                            WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                            ELSE (projects.completion_percentage)::numeric
                        END, (0)::numeric) < (1)::numeric) THEN 'On Track'::text
                        ELSE ''::text
                    END = 'Overdue'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ' ⚠ OVERDUE! '::text
                    ELSE
                    CASE
                        WHEN ((((
                        CASE
                            WHEN (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) >= (1)::numeric) THEN 'Completed'::text
                            WHEN ((projects.due_date IS NOT NULL) AND (((projects.due_date)::date - CURRENT_DATE) < 0) AND (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) < (1)::numeric)) THEN 'Overdue'::text
                            WHEN ((projects.due_date IS NOT NULL) AND ((((projects.due_date)::date - CURRENT_DATE) >= 0) AND (((projects.due_date)::date - CURRENT_DATE) <= 5)) AND (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) < (1)::numeric)) THEN 'At Risk'::text
                            WHEN (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) < (1)::numeric) THEN 'On Track'::text
                            ELSE ''::text
                        END = 'At Risk'::text))::text IS NOT NULL) AND (lower(((
                        CASE
                            WHEN (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) >= (1)::numeric) THEN 'Completed'::text
                            WHEN ((projects.due_date IS NOT NULL) AND (((projects.due_date)::date - CURRENT_DATE) < 0) AND (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) < (1)::numeric)) THEN 'Overdue'::text
                            WHEN ((projects.due_date IS NOT NULL) AND ((((projects.due_date)::date - CURRENT_DATE) >= 0) AND (((projects.due_date)::date - CURRENT_DATE) <= 5)) AND (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) < (1)::numeric)) THEN 'At Risk'::text
                            WHEN (COALESCE(
                            CASE
                                WHEN (abs(COALESCE((projects.completion_percentage)::numeric, (0)::numeric)) > (1)::numeric) THEN ((projects.completion_percentage)::numeric / (100)::numeric)
                                ELSE (projects.completion_percentage)::numeric
                            END, (0)::numeric) < (1)::numeric) THEN 'On Track'::text
                            ELSE ''::text
                        END = 'At Risk'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN concat(' ! At Risk: ',
                        CASE
                            WHEN ((((
                            CASE
                                WHEN (((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN (0)::bigint
                                ELSE
                                CASE
                                    WHEN ((((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower(((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM (projects.due_date - (CURRENT_DATE)::timestamp with time zone)) / (86400)::numeric))::bigint
                                    ELSE NULL::bigint
                                END
                            END >= 0))::text IS NOT NULL) AND (lower(((
                            CASE
                                WHEN (((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN (0)::bigint
                                ELSE
                                CASE
                                    WHEN ((((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower(((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM (projects.due_date - (CURRENT_DATE)::timestamp with time zone)) / (86400)::numeric))::bigint
                                    ELSE NULL::bigint
                                END
                            END >= 0))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN concat(
                            CASE
                                WHEN (((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower((((((projects.completion_percentage = (1)::numeric))::text IS NOT NULL) AND (lower(((projects.completion_percentage = (1)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN (0)::bigint
                                ELSE
                                CASE
                                    WHEN ((((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower(((((projects.start_date)::text IS NOT NULL) AND (lower((projects.start_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND (((projects.due_date)::text IS NOT NULL) AND (lower((projects.due_date)::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM (projects.due_date - (CURRENT_DATE)::timestamp with time zone)) / (86400)::numeric))::bigint
                                    ELSE NULL::bigint
                                END
                            END, ' days left')
                            ELSE 'Urgent!'::text
                        END)
                        ELSE NULL::text
                    END
                END) AS dynamic_summary_text
           FROM projects) _cv5 ON (((_cv5.source_id)::text = (t._at_id)::text)));

CREATE OR REPLACE VIEW "vt_tasks" AS
SELECT t.id,
    t._at_id,
    t._at_hash,
    t.task_name,
    t.project,
    t.assigned_to,
    t.start_date,
    t.target_date,
    t.actual_completion_date,
    t.effort_hours,
    t.difficulty_rating,
    t.dependencies_count,
    t.status,
    t.assignee,
    t.kanban_tier,
    t.task_group,
    t.task_group_kanban_stack,
    t.kanban_group,
    t.kanban_group_task_tier,
    t.kanban_status_group,
    t.task_kanban_group,
    t.kanban_tier_group,
    _cv0.business_days_until_deadline,
    _cv1.overdue_days,
    _cv2.task_tier,
    _cv3.completion_score,
    _cv4.risk_level,
    _cv5.formatted_status_message
   FROM ((((((tasks t
     LEFT JOIN ( SELECT tasks._at_id AS source_id,
            (
                CASE
                    WHEN ((tasks.start_date IS NULL) OR (tasks.target_date IS NULL)) THEN NULL::integer
                    ELSE (
                    CASE
                        WHEN ((tasks.target_date)::date >= (tasks.start_date)::date) THEN 1
                        ELSE '-1'::integer
                    END * ( SELECT (count(*))::integer AS count
                       FROM generate_series((LEAST((tasks.start_date)::date, (tasks.target_date)::date))::timestamp without time zone, (GREATEST((tasks.start_date)::date, (tasks.target_date)::date))::timestamp without time zone, '1 day'::interval) g(d)
                      WHERE (EXTRACT(isodow FROM g.d) < (6)::numeric)))
                END)::text AS business_days_until_deadline
           FROM tasks) _cv0 ON (((_cv0.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT tasks._at_id AS source_id,
            (
                CASE
                    WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                    ELSE (0)::bigint
                END)::text AS overdue_days
           FROM tasks) _cv1 ON (((_cv1.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT tasks._at_id AS source_id,
                CASE
                    WHEN ((((tasks.difficulty_rating > (8)::numeric))::text IS NOT NULL) AND (lower(((tasks.difficulty_rating > (8)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Expert'::text
                    ELSE
                    CASE
                        WHEN ((((tasks.difficulty_rating > (5)::numeric))::text IS NOT NULL) AND (lower(((tasks.difficulty_rating > (5)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN
                        CASE
                            WHEN ((((tasks.effort_hours > (40)::numeric))::text IS NOT NULL) AND (lower(((tasks.effort_hours > (40)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Advanced'::text
                            ELSE 'Intermediate'::text
                        END
                        ELSE
                        CASE
                            WHEN ((((tasks.effort_hours > (24)::numeric))::text IS NOT NULL) AND (lower(((tasks.effort_hours > (24)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Trained'::text
                            ELSE 'Basic'::text
                        END
                    END
                END AS task_tier
           FROM tasks) _cv2 ON (((_cv2.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT tasks._at_id AS source_id,
            (
                CASE
                    WHEN (((((((tasks.status = 'Completed'::text))::text IS NOT NULL) AND (lower(((tasks.status = 'Completed'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND ((((
                    CASE
                        WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                        ELSE (0)::bigint
                    END = 0))::text IS NOT NULL) AND (lower(((
                    CASE
                        WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                        ELSE (0)::bigint
                    END = 0))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower((((((tasks.status = 'Completed'::text))::text IS NOT NULL) AND (lower(((tasks.status = 'Completed'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND ((((
                    CASE
                        WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                        ELSE (0)::bigint
                    END = 0))::text IS NOT NULL) AND (lower(((
                    CASE
                        WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                        ELSE (0)::bigint
                    END = 0))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN (100)::numeric
                    ELSE
                    CASE
                        WHEN (((((((tasks.status = 'Completed'::text))::text IS NOT NULL) AND (lower(((tasks.status = 'Completed'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND ((((
                        CASE
                            WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                            ELSE (0)::bigint
                        END > 0))::text IS NOT NULL) AND (lower(((
                        CASE
                            WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                            ELSE (0)::bigint
                        END > 0))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text IS NOT NULL) AND (lower((((((tasks.status = 'Completed'::text))::text IS NOT NULL) AND (lower(((tasks.status = 'Completed'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])) AND ((((
                        CASE
                            WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                            ELSE (0)::bigint
                        END > 0))::text IS NOT NULL) AND (lower(((
                        CASE
                            WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                            ELSE (0)::bigint
                        END > 0))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text])))))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN (80)::numeric
                        ELSE
                        CASE
                            WHEN ((((tasks.status = 'In Progress'::text))::text IS NOT NULL) AND (lower(((tasks.status = 'In Progress'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN round(((50)::numeric + ((tasks.effort_hours + tasks.difficulty_rating) / (2)::numeric)), 1)
                            ELSE (20)::numeric
                        END
                    END
                END)::text AS completion_score
           FROM tasks) _cv3 ON (((_cv3.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT tasks._at_id AS source_id,
                CASE
                    WHEN ((((mod((power((tasks.dependencies_count)::numeric, (2)::numeric) + tasks.difficulty_rating), (3)::numeric) = (0)::numeric))::text IS NOT NULL) AND (lower(((mod((power((tasks.dependencies_count)::numeric, (2)::numeric) + tasks.difficulty_rating), (3)::numeric) = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'High'::text
                    ELSE
                    CASE
                        WHEN ((((mod((tasks.effort_hours)::numeric, (2)::numeric) = (0)::numeric))::text IS NOT NULL) AND (lower(((mod((tasks.effort_hours)::numeric, (2)::numeric) = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Medium'::text
                        ELSE 'Low'::text
                    END
                END AS risk_level
           FROM tasks) _cv4 ON (((_cv4.source_id)::text = (t._at_id)::text)))
     LEFT JOIN ( SELECT tasks._at_id AS source_id,
            concat('[',
                CASE
                    WHEN ((((tasks.difficulty_rating > (8)::numeric))::text IS NOT NULL) AND (lower(((tasks.difficulty_rating > (8)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Expert'::text
                    ELSE
                    CASE
                        WHEN ((((tasks.difficulty_rating > (5)::numeric))::text IS NOT NULL) AND (lower(((tasks.difficulty_rating > (5)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN
                        CASE
                            WHEN ((((tasks.effort_hours > (40)::numeric))::text IS NOT NULL) AND (lower(((tasks.effort_hours > (40)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Advanced'::text
                            ELSE 'Intermediate'::text
                        END
                        ELSE
                        CASE
                            WHEN ((((tasks.effort_hours > (24)::numeric))::text IS NOT NULL) AND (lower(((tasks.effort_hours > (24)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Trained'::text
                            ELSE 'Basic'::text
                        END
                    END
                END, '] ', "left"(tasks.assigned_to, 3), ' | Due ', to_char(tasks.target_date, 'MM/DD'::text),
                CASE
                    WHEN ((((
                    CASE
                        WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                        ELSE (0)::bigint
                    END > 0))::text IS NOT NULL) AND (lower(((
                    CASE
                        WHEN (((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text IS NOT NULL) AND (lower((((CURRENT_DATE)::timestamp with time zone > tasks.target_date))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ((EXTRACT(epoch FROM ((CURRENT_DATE)::timestamp with time zone - tasks.target_date)) / (86400)::numeric))::bigint
                        ELSE (0)::bigint
                    END > 0))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ' - OVERDUE by '::text
                    ELSE ' On Time; '::text
                END, "right"(tasks.status, 4),
                CASE
                    WHEN ((((
                    CASE
                        WHEN ((((mod((power((tasks.dependencies_count)::numeric, (2)::numeric) + tasks.difficulty_rating), (3)::numeric) = (0)::numeric))::text IS NOT NULL) AND (lower(((mod((power((tasks.dependencies_count)::numeric, (2)::numeric) + tasks.difficulty_rating), (3)::numeric) = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'High'::text
                        ELSE
                        CASE
                            WHEN ((((mod((tasks.effort_hours)::numeric, (2)::numeric) = (0)::numeric))::text IS NOT NULL) AND (lower(((mod((tasks.effort_hours)::numeric, (2)::numeric) = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Medium'::text
                            ELSE 'Low'::text
                        END
                    END = 'High'::text))::text IS NOT NULL) AND (lower(((
                    CASE
                        WHEN ((((mod((power((tasks.dependencies_count)::numeric, (2)::numeric) + tasks.difficulty_rating), (3)::numeric) = (0)::numeric))::text IS NOT NULL) AND (lower(((mod((power((tasks.dependencies_count)::numeric, (2)::numeric) + tasks.difficulty_rating), (3)::numeric) = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'High'::text
                        ELSE
                        CASE
                            WHEN ((((mod((tasks.effort_hours)::numeric, (2)::numeric) = (0)::numeric))::text IS NOT NULL) AND (lower(((mod((tasks.effort_hours)::numeric, (2)::numeric) = (0)::numeric))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN 'Medium'::text
                            ELSE 'Low'::text
                        END
                    END = 'High'::text))::text) <> ALL (ARRAY[''::text, '0'::text, 'false'::text, 'f'::text, 'null'::text]))) THEN ' ⚠'::text
                    ELSE NULL::text
                END) AS formatted_status_message
           FROM tasks) _cv5 ON (((_cv5.source_id)::text = (t._at_id)::text)));
