ALTER TABLE "basic_contract" ADD COLUMN "deadline" date NOT NULL;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "vendor_signed_at" timestamp;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "buyer_signed_at" timestamp;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "legal_review_requested_at" timestamp;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "legal_review_completed_at" timestamp;--> statement-breakpoint
CREATE VIEW "public"."basic_contract_template_stats_view" AS (select "basic_contract_templates"."id" as "template_id", "basic_contract_templates"."template_name" as "template_name", "basic_contract_templates"."revision" as "revision", "basic_contract_templates"."legal_review_required" as "legal_review_required", "basic_contract_templates"."validity_period" as "validity_period", "basic_contract_templates"."created_at" as "template_created_at", COUNT("basic_contract"."id") as "total_sent_count", 
        COUNT(CASE 
          WHEN "basic_contract"."deadline" < CURRENT_DATE 
               AND "basic_contract"."status" NOT IN ('COMPLETED', 'REJECTED') 
          THEN 1 
        END)
       as "overdue_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" = 'PENDING' 
          THEN 1 
        END)
       as "unsigned_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" IN ('VENDOR_SIGNED', 'BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED') 
          THEN 1 
        END)
       as "vendor_signed_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" IN ('LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED') 
          THEN 1 
        END)
       as "legal_request_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" = 'LEGAL_REVIEW_COMPLETED' 
               OR ("basic_contract"."status" = 'COMPLETED' AND "basic_contract"."legal_review_completed_at" IS NOT NULL)
          THEN 1 
        END)
       as "legal_completed_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" = 'COMPLETED' 
          THEN 1 
        END)
       as "contract_completed_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" IN ('BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED') 
          THEN 1 
        END)
       as "buyer_signed_count", 
        COUNT(CASE 
          WHEN "basic_contract"."status" = 'REJECTED' 
          THEN 1 
        END)
       as "rejected_count", 
        AVG(CASE 
          WHEN "basic_contract"."status" = 'COMPLETED' AND "basic_contract"."completed_at" IS NOT NULL
          THEN EXTRACT(EPOCH FROM ("basic_contract"."completed_at" - "basic_contract"."created_at")) / 86400
        END)
       as "avg_processing_days", MAX("basic_contract"."updated_at") as "last_activity_date" from "basic_contract_templates" left join "basic_contract" on "basic_contract_templates"."id" = "basic_contract"."template_id" where "basic_contract_templates"."status" = 'ACTIVE' group by "basic_contract_templates"."id", "basic_contract_templates"."template_name", "basic_contract_templates"."revision", "basic_contract_templates"."legal_review_required", "basic_contract_templates"."validity_period", "basic_contract_templates"."created_at");