diff options
Diffstat (limited to 'db/migrations/0268_striped_pepper_potts.sql')
| -rw-r--r-- | db/migrations/0268_striped_pepper_potts.sql | 196 |
1 files changed, 196 insertions, 0 deletions
diff --git a/db/migrations/0268_striped_pepper_potts.sql b/db/migrations/0268_striped_pepper_potts.sql new file mode 100644 index 00000000..86bccca6 --- /dev/null +++ b/db/migrations/0268_striped_pepper_potts.sql @@ -0,0 +1,196 @@ +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;
\ No newline at end of file |