DROP VIEW "public"."bidding_list_view";--> statement-breakpoint
ALTER TABLE "biddings" ADD COLUMN "is_urgent" boolean DEFAULT false;--> 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"."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");