ALTER TYPE "public"."invitation_status" ADD VALUE 'bidding_invited' BEFORE 'accepted';--> statement-breakpoint
ALTER TYPE "public"."invitation_status" ADD VALUE 'bidding_submitted';--> statement-breakpoint
DROP VIEW "public"."bidding_list_view";--> statement-breakpoint
DROP VIEW "public"."tbe_session_summary_view";--> statement-breakpoint
ALTER TABLE "general_contracts" ALTER COLUMN "type" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "general_contracts" ALTER COLUMN "execution_method" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "general_contracts" ALTER COLUMN "name" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "general_contracts" ALTER COLUMN "start_date" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "general_contracts" ALTER COLUMN "end_date" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "general_contracts" ALTER COLUMN "validity_end_date" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "biddings" ADD COLUMN "bidding_source_type" varchar(20) DEFAULT 'manual' NOT NULL;--> statement-breakpoint
ALTER TABLE "general_contracts" ADD COLUMN "contract_source_type" varchar(20) DEFAULT 'manual' NOT NULL;--> statement-breakpoint
CREATE VIEW "public"."bidding_list_view" AS (select "biddings"."id", "biddings"."bidding_number", "biddings"."revision", "biddings"."project_name", "biddings"."item_name", "biddings"."title", "biddings"."description", "biddings"."content", "biddings"."bidding_source_type", "biddings"."is_urgent", "biddings"."contract_type", "biddings"."bidding_type", "biddings"."award_count", "biddings"."contract_period", "biddings"."pre_quote_date", "biddings"."bidding_registration_date", "biddings"."submission_start_date", "biddings"."submission_end_date", "biddings"."evaluation_date", "biddings"."has_specification_meeting", "biddings"."has_pr_document", "biddings"."pr_number", "biddings"."currency", "biddings"."budget", "biddings"."target_price", "biddings"."final_bid_price", "biddings"."status", "biddings"."is_public", "biddings"."manager_name", "biddings"."manager_email", "biddings"."manager_phone", "biddings"."remarks", "biddings"."created_by", "biddings"."created_at", "biddings"."updated_at", "biddings"."updated_by", "specification_meetings"."id" IS NOT NULL as "has_specification_meeting_details", "specification_meetings"."meeting_date", "specification_meetings"."location", "specification_meetings"."contact_person", "specification_meetings"."is_required", 
        COALESCE((
          SELECT count(*) 
          FROM pr_documents 
          WHERE bidding_id = "biddings"."id"
        ), 0)
       as "pr_document_count", 
        (
          SELECT array_agg(document_name ORDER BY registered_at DESC)
          FROM pr_documents 
          WHERE bidding_id = "biddings"."id"
          LIMIT 5
        )
       as "pr_document_names", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id"
        ), 0)
       as "participant_expected", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND invitation_status = 'submitted'
        ), 0)
       as "participant_participated", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND invitation_status = 'declined'
        ), 0)
       as "participant_declined", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND invitation_status IN ('pending', 'sent')
        ), 0)
       as "participant_pending", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND invitation_status = 'accepted'
        ), 0)
       as "participant_accepted", 
        CASE 
          WHEN (
            SELECT count(*) 
            FROM bidding_companies 
            WHERE bidding_id = "biddings"."id"
          ) > 0 
          THEN ROUND(
            (
              SELECT count(*)::decimal 
              FROM bidding_companies 
              WHERE bidding_id = "biddings"."id" 
              AND invitation_status = 'submitted'
            ) / (
              SELECT count(*)::decimal 
              FROM bidding_companies 
              WHERE bidding_id = "biddings"."id"
            ) * 100, 1
          )
          ELSE 0 
        END
       as "participation_rate", 
        (
          SELECT AVG(pre_quote_amount) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND pre_quote_amount IS NOT NULL
        )
       as "avg_pre_quote_amount", 
        (
          SELECT MIN(pre_quote_amount) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND pre_quote_amount IS NOT NULL
        )
       as "min_pre_quote_amount", 
        (
          SELECT MAX(pre_quote_amount) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND pre_quote_amount IS NOT NULL
        )
       as "max_pre_quote_amount", 
        (
          SELECT AVG(final_quote_amount) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND final_quote_amount IS NOT NULL
        )
       as "avg_final_quote_amount", 
        (
          SELECT MIN(final_quote_amount) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND final_quote_amount IS NOT NULL
        )
       as "min_final_quote_amount", 
        (
          SELECT MAX(final_quote_amount) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND final_quote_amount IS NOT NULL
        )
       as "max_final_quote_amount", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND is_pre_quote_selected = true
        ), 0)
       as "selected_for_final_bid_count", 
        COALESCE((
          SELECT count(*) 
          FROM bidding_companies 
          WHERE bidding_id = "biddings"."id" 
          AND is_winner = true
        ), 0)
       as "winner_count", 
        (
          SELECT array_agg(v.vendor_name ORDER BY v.vendor_name)
          FROM bidding_companies bc
          JOIN vendors v ON bc.company_id = v.id
          WHERE bc.bidding_id = "biddings"."id" 
          AND bc.is_winner = true
        )
       as "winner_company_names", 
        CASE 
          WHEN "biddings"."submission_start_date" IS NULL OR "biddings"."submission_end_date" IS NULL 
          THEN 'not_scheduled'
          WHEN NOW() < "biddings"."submission_start_date" 
          THEN 'scheduled'
          WHEN NOW() BETWEEN "biddings"."submission_start_date" AND "biddings"."submission_end_date" 
          THEN 'active'
          WHEN NOW() > "biddings"."submission_end_date" 
          THEN 'closed'
          ELSE 'unknown'
        END
       as "submission_status", 
        CASE 
          WHEN "biddings"."submission_end_date" IS NOT NULL 
          AND NOW() < "biddings"."submission_end_date" 
          THEN EXTRACT(DAYS FROM ("biddings"."submission_end_date" - NOW()))::integer
          ELSE NULL 
        END
       as "days_until_deadline", 
        CASE 
          WHEN "biddings"."submission_start_date" IS NOT NULL 
          AND NOW() < "biddings"."submission_start_date" 
          THEN EXTRACT(DAYS FROM ("biddings"."submission_start_date" - NOW()))::integer
          ELSE NULL 
        END
       as "days_until_start", 
        CASE 
          WHEN "biddings"."budget" IS NOT NULL AND "biddings"."budget" > 0
          AND (
            SELECT MIN(final_quote_amount) 
            FROM bidding_companies 
            WHERE bidding_id = "biddings"."id" 
            AND final_quote_amount IS NOT NULL
          ) IS NOT NULL
          THEN ROUND(
            (
              SELECT MIN(final_quote_amount) 
              FROM bidding_companies 
              WHERE bidding_id = "biddings"."id" 
              AND final_quote_amount IS NOT NULL
            ) / "biddings"."budget" * 100, 1
          )
          ELSE NULL 
        END
       as "budget_efficiency_rate", 
        CASE 
          WHEN "biddings"."target_price" IS NOT NULL AND "biddings"."target_price" > 0
          AND (
            SELECT MIN(final_quote_amount) 
            FROM bidding_companies 
            WHERE bidding_id = "biddings"."id" 
            AND final_quote_amount IS NOT NULL
          ) IS NOT NULL
          THEN ROUND(
            (
              SELECT MIN(final_quote_amount) 
              FROM bidding_companies 
              WHERE bidding_id = "biddings"."id" 
              AND final_quote_amount IS NOT NULL
            ) / "biddings"."target_price" * 100, 1
          )
          ELSE NULL 
        END
       as "target_price_efficiency_rate", 
        CASE "biddings"."status"
          WHEN 'bidding_generated' THEN 10
          WHEN 'request_for_quotation' THEN 20
          WHEN 'received_quotation' THEN 40
          WHEN 'set_target_price' THEN 60
          WHEN 'bidding_opened' THEN 70
          WHEN 'bidding_closed' THEN 80
          WHEN 'evaluation_of_bidding' THEN 90
          WHEN 'vendor_selected' THEN 100
          WHEN 'bidding_disposal' THEN 0
          ELSE 0
        END
       as "progress_score", 
        GREATEST(
          "biddings"."updated_at",
          COALESCE((
            SELECT MAX(updated_at) 
            FROM bidding_companies 
            WHERE bidding_id = "biddings"."id"
          ), "biddings"."updated_at")
        )
       as "last_activity_date" from "biddings" left join "specification_meetings" on "biddings"."id" = "specification_meetings"."bidding_id");--> statement-breakpoint
CREATE VIEW "public"."tbe_session_summary_view" AS (select "tbe"."id" as "tbe_id", "tbe"."session_code" as "session_code", "tbe"."session_title" as "session_title", "rfq"."rfq_code" as "rfq_code", "vendor"."vendor_name" as "vendor_name", "vendor"."vendor_code" as "vendor_code", "tbe"."status" as "status", "tbe"."evaluation_result" as "evaluation_result", "lead_evaluator"."name" as "lead_evaluator_name", (
                SELECT COUNT(*)
                FROM rfq_last_tbe_document_reviews
                WHERE tbe_session_id = "tbe"."id"
            ) as "total_documents", (
                SELECT COUNT(*)
                FROM rfq_last_tbe_document_reviews
                WHERE tbe_session_id = "tbe"."id"
                AND review_status IN ('검토완료', '승인')
            ) as "reviewed_documents", (
                SELECT COUNT(*)
                FROM rfq_last_tbe_pdftron_comments pc
                JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id
                WHERE dr.tbe_session_id = "tbe"."id"
            ) as "total_comments", "tbe"."actual_start_date" as "actual_start_date", "tbe"."actual_end_date" as "actual_end_date", "tbe"."created_at" as "created_at", "tbe"."updated_at" as "updated_at" from "rfq_last_tbe_sessions" "tbe" left join "rfqs_last" "rfq" on "tbe"."rfqs_last_id" = "rfq"."id" left join "vendors" "vendor" on "tbe"."vendor_id" = "vendor"."id" left join "users" "lead_evaluator" on "tbe"."lead_evaluator_id" = "lead_evaluator"."id");