CREATE TYPE "public"."award_count" AS ENUM('single', 'multiple');--> statement-breakpoint
CREATE TYPE "public"."bidding_status" AS ENUM('bidding_generated', 'request_for_quotation', 'received_quotation', 'set_target_price', 'bidding_opened', 'bidding_closed', 'evaluation_of_bidding', 'bidding_disposal', 'vendor_selected');--> statement-breakpoint
CREATE TYPE "public"."bidding_type" AS ENUM('equipment', 'construction', 'service', 'lease', 'steel_stock', 'piping', 'transport', 'waste', 'sale');--> statement-breakpoint
CREATE TYPE "public"."contract_type" AS ENUM('unit_price', 'general', 'sale');--> statement-breakpoint
CREATE TYPE "public"."document_type" AS ENUM('notice', 'specification', 'contract_draft', 'company_proposal', 'financial_doc', 'technical_doc', 'certificate', 'pr_document', 'spec_document', 'evaluation_doc', 'bid_attachment', 'other');--> statement-breakpoint
CREATE TYPE "public"."invitation_status" AS ENUM('pending', 'sent', 'accepted', 'declined', 'submitted');--> statement-breakpoint
CREATE TYPE "public"."quantity_unit" AS ENUM('ea', 'set', 'kg', 'ton', 'm', 'm2', 'm3', 'lot', 'other');--> statement-breakpoint
CREATE TYPE "public"."weight_unit" AS ENUM('kg', 'ton', 'lb', 'g');--> statement-breakpoint
CREATE TABLE "bidding_companies" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_id" integer NOT NULL,
	"company_id" integer NOT NULL,
	"invitation_status" "invitation_status" DEFAULT 'pending' NOT NULL,
	"invited_at" timestamp,
	"responded_at" timestamp,
	"pre_quote_amount" numeric(15, 2),
	"pre_quote_submitted_at" timestamp,
	"is_pre_quote_selected" boolean DEFAULT false,
	"final_quote_amount" numeric(15, 2),
	"final_quote_submitted_at" timestamp,
	"is_winner" boolean DEFAULT false,
	"notes" text,
	"contact_person" varchar(100),
	"contact_email" varchar(100),
	"contact_phone" varchar(20),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "bidding_conditions" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_id" integer NOT NULL,
	"payment_terms" text,
	"tax_conditions" text,
	"contract_delivery_date" date,
	"is_price_adjustment_applicable" boolean,
	"incoterms" text,
	"shipping_port" varchar(200),
	"destination_port" varchar(200),
	"spare_part_options" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "bidding_documents" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_id" integer NOT NULL,
	"company_id" integer,
	"pr_item_id" integer,
	"document_type" "document_type" NOT NULL,
	"file_name" varchar(500) NOT NULL,
	"original_file_name" varchar(500) NOT NULL,
	"file_size" integer,
	"mime_type" varchar(100),
	"file_path" varchar(1000) NOT NULL,
	"title" varchar(300),
	"description" text,
	"is_public" boolean DEFAULT false,
	"is_required" boolean DEFAULT false,
	"uploaded_by" varchar(100),
	"uploaded_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "biddings" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_number" varchar(50) NOT NULL,
	"revision" integer DEFAULT 0,
	"project_name" varchar(300),
	"item_name" varchar(300),
	"title" varchar(300) NOT NULL,
	"description" text,
	"content" text,
	"contract_type" "contract_type" NOT NULL,
	"bidding_type" "bidding_type" NOT NULL,
	"award_count" "award_count" DEFAULT 'single',
	"contract_period" varchar(100),
	"pre_quote_date" date,
	"bidding_registration_date" date,
	"submission_start_date" timestamp,
	"submission_end_date" timestamp,
	"evaluation_date" timestamp,
	"has_specification_meeting" boolean DEFAULT false,
	"currency" varchar(3) DEFAULT 'KRW',
	"budget" numeric(15, 2),
	"target_price" numeric(15, 2),
	"final_bid_price" numeric(15, 2),
	"pr_number" varchar(50),
	"has_pr_document" boolean DEFAULT false,
	"status" "bidding_status" DEFAULT 'bidding_generated' NOT NULL,
	"is_public" boolean DEFAULT false,
	"manager_name" varchar(100),
	"manager_email" varchar(100),
	"manager_phone" varchar(20),
	"remarks" text,
	"created_by" varchar(100),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	"updated_by" varchar(100),
	CONSTRAINT "biddings_bidding_number_unique" UNIQUE("bidding_number")
);
--> statement-breakpoint
CREATE TABLE "company_condition_responses" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_company_id" integer NOT NULL,
	"payment_terms_response" varchar(200),
	"tax_conditions_response" varchar(200),
	"proposed_contract_delivery_date" date,
	"price_adjustment_response" boolean,
	"incoterms_response" varchar(100),
	"proposed_shipping_port" varchar(200),
	"proposed_destination_port" varchar(200),
	"spare_part_response" varchar(200),
	"additional_proposals" text,
	"is_pre_quote" boolean DEFAULT false,
	"submitted_at" timestamp DEFAULT now() NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "company_pr_item_bids" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_company_id" integer NOT NULL,
	"pr_item_id" integer NOT NULL,
	"proposed_delivery_date" date,
	"bid_unit_price" numeric(15, 2),
	"bid_amount" numeric(15, 2),
	"currency" varchar(3) DEFAULT 'KRW',
	"technical_specification" text,
	"is_pre_quote" boolean DEFAULT false,
	"submitted_at" timestamp DEFAULT now() NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "pr_documents" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_id" integer NOT NULL,
	"document_name" varchar(300) NOT NULL,
	"file_name" varchar(500) NOT NULL,
	"original_file_name" varchar(500) NOT NULL,
	"file_size" integer,
	"mime_type" varchar(100),
	"file_path" varchar(1000) NOT NULL,
	"registered_at" timestamp DEFAULT now() NOT NULL,
	"registered_by" varchar(100) NOT NULL,
	"description" text,
	"version" varchar(20),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "specification_meetings" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_id" integer NOT NULL,
	"meeting_date" timestamp NOT NULL,
	"meeting_time" varchar(20),
	"location" varchar(500),
	"address" text,
	"contact_person" varchar(100),
	"contact_phone" varchar(20),
	"contact_email" varchar(100),
	"agenda" text,
	"materials" text,
	"notes" text,
	"is_required" boolean DEFAULT false,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_selection_results" (
	"id" serial PRIMARY KEY NOT NULL,
	"bidding_id" integer NOT NULL,
	"selected_company_id" integer NOT NULL,
	"selection_reason" text NOT NULL,
	"evaluation_summary" text,
	"has_result_documents" boolean DEFAULT false,
	"selected_at" timestamp DEFAULT now() NOT NULL,
	"selected_by" varchar(100) NOT NULL,
	"remarks" text,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "bidding_companies" ADD CONSTRAINT "bidding_companies_bidding_id_biddings_id_fk" FOREIGN KEY ("bidding_id") REFERENCES "public"."biddings"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bidding_companies" ADD CONSTRAINT "bidding_companies_company_id_vendors_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bidding_conditions" ADD CONSTRAINT "bidding_conditions_bidding_id_biddings_id_fk" FOREIGN KEY ("bidding_id") REFERENCES "public"."biddings"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bidding_documents" ADD CONSTRAINT "bidding_documents_bidding_id_biddings_id_fk" FOREIGN KEY ("bidding_id") REFERENCES "public"."biddings"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bidding_documents" ADD CONSTRAINT "bidding_documents_company_id_vendors_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "bidding_documents" ADD CONSTRAINT "bidding_documents_pr_item_id_pr_items_id_fk" FOREIGN KEY ("pr_item_id") REFERENCES "public"."pr_items"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "company_condition_responses" ADD CONSTRAINT "company_condition_responses_bidding_company_id_bidding_companies_id_fk" FOREIGN KEY ("bidding_company_id") REFERENCES "public"."bidding_companies"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "company_pr_item_bids" ADD CONSTRAINT "company_pr_item_bids_bidding_company_id_bidding_companies_id_fk" FOREIGN KEY ("bidding_company_id") REFERENCES "public"."bidding_companies"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "company_pr_item_bids" ADD CONSTRAINT "company_pr_item_bids_pr_item_id_pr_items_id_fk" FOREIGN KEY ("pr_item_id") REFERENCES "public"."pr_items"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "pr_documents" ADD CONSTRAINT "pr_documents_bidding_id_biddings_id_fk" FOREIGN KEY ("bidding_id") REFERENCES "public"."biddings"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "specification_meetings" ADD CONSTRAINT "specification_meetings_bidding_id_biddings_id_fk" FOREIGN KEY ("bidding_id") REFERENCES "public"."biddings"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_selection_results" ADD CONSTRAINT "vendor_selection_results_bidding_id_biddings_id_fk" FOREIGN KEY ("bidding_id") REFERENCES "public"."biddings"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_selection_results" ADD CONSTRAINT "vendor_selection_results_selected_company_id_vendors_id_fk" FOREIGN KEY ("selected_company_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;