diff options
Diffstat (limited to 'db/migrations_backup/0094_fresh_blur.sql')
| -rw-r--r-- | db/migrations_backup/0094_fresh_blur.sql | 783 |
1 files changed, 0 insertions, 783 deletions
diff --git a/db/migrations_backup/0094_fresh_blur.sql b/db/migrations_backup/0094_fresh_blur.sql deleted file mode 100644 index 007336d2..00000000 --- a/db/migrations_backup/0094_fresh_blur.sql +++ /dev/null @@ -1,783 +0,0 @@ -CREATE TYPE "public"."user_domain" AS ENUM('evcp', 'partners');--> statement-breakpoint -CREATE TABLE "companies" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "companies_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "name" varchar(255) NOT NULL, - "taxID" integer NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "contract_envelopes" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contract_envelopes_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "contract_id" integer NOT NULL, - "envelope_id" varchar(200) NOT NULL, - "document_id" varchar(200), - "envelope_status" varchar(50), - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "contract_items" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contract_items_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "contract_id" integer NOT NULL, - "item_id" integer NOT NULL, - "description" text, - "quantity" integer DEFAULT 1 NOT NULL, - "unit_price" numeric(10, 2), - "tax_rate" numeric(5, 2), - "tax_amount" numeric(10, 2), - "total_line_amount" numeric(12, 2), - "remark" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL, - CONSTRAINT "contract_items_contract_id_item_id_unique" UNIQUE("contract_id","item_id") -); ---> statement-breakpoint -CREATE TABLE "contract_signers" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contract_signers_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "envelope_id" integer NOT NULL, - "vendor_contact_id" integer, - "signer_type" varchar(20) DEFAULT 'VENDOR' NOT NULL, - "signer_email" varchar(255) NOT NULL, - "signer_name" varchar(100) NOT NULL, - "signer_position" varchar(100), - "signer_status" varchar(50) DEFAULT 'PENDING', - "signed_at" timestamp, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "contracts" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contracts_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "project_id" integer NOT NULL, - "vendor_id" integer NOT NULL, - "contract_no" varchar(100) NOT NULL, - "contract_name" varchar(255) NOT NULL, - "status" varchar(50) DEFAULT 'ACTIVE' NOT NULL, - "start_date" date, - "end_date" date, - "payment_terms" text, - "delivery_terms" text, - "delivery_date" date, - "delivery_location" varchar(255), - "currency" varchar(10) DEFAULT 'KRW', - "total_amount" numeric(12, 2), - "discount" numeric(12, 2), - "tax" numeric(12, 2), - "shipping_fee" numeric(12, 2), - "net_total" numeric(12, 2), - "partial_shipping_allowed" boolean DEFAULT false, - "partial_payment_allowed" boolean DEFAULT false, - "remarks" text, - "version" integer DEFAULT 1, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL, - CONSTRAINT "contracts_contract_no_unique" UNIQUE("contract_no") -); ---> statement-breakpoint -CREATE TABLE "items" ( - "id" serial PRIMARY KEY NOT NULL, - "item_code" varchar(100), - "item_name" varchar(255) NOT NULL, - "description" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL, - CONSTRAINT "items_item_code_unique" UNIQUE("item_code") -); ---> statement-breakpoint -CREATE TABLE "pq_criterias" ( - "id" serial PRIMARY KEY NOT NULL, - "code" varchar(50) NOT NULL, - "check_point" varchar(255) NOT NULL, - "description" text, - "remarks" text, - "group_name" varchar(255), - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_criteria_attachments" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_criteria_answer_id" integer NOT NULL, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "file_type" varchar(50), - "file_size" integer, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_pq_criteria_answers" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_id" integer NOT NULL, - "criteria_id" integer NOT NULL, - "answer" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_pq_review_logs" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_pq_criteria_answer_id" integer NOT NULL, - "reviewer_comment" text NOT NULL, - "reviewer_name" text, - "created_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "projects" ( - "id" serial PRIMARY KEY NOT NULL, - "code" varchar(50) NOT NULL, - "name" text NOT NULL, - "type" varchar(20) DEFAULT 'ship' NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "cbe_evaluations" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_id" integer NOT NULL, - "vendor_id" integer NOT NULL, - "evaluated_by" integer, - "evaluated_at" timestamp DEFAULT now() NOT NULL, - "result" varchar(50), - "total_cost" numeric(18, 2), - "currency" varchar(10) DEFAULT 'USD', - "payment_terms" varchar(255), - "incoterms" varchar(50), - "delivery_schedule" text, - "notes" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "rfq_attachments" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_id" integer, - "vendor_id" integer, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "evaluation_id" integer, - "cbe_id" integer, - "created_at" timestamp DEFAULT now() NOT NULL, - "comment_id" integer -); ---> statement-breakpoint -CREATE TABLE "rfq_comments" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_id" integer, - "vendor_id" integer, - "comment_text" text NOT NULL, - "commented_by" integer NOT NULL, - "evaluation_id" integer, - "cbe_id" integer, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "rfq_evaluations" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_id" integer NOT NULL, - "vendor_id" integer NOT NULL, - "eval_type" varchar(30), - "result" varchar(255), - "notes" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "rfq_items" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_id" integer NOT NULL, - "item_code" varchar(100) NOT NULL, - "description" text, - "quantity" numeric(12, 2) DEFAULT 1, - "uom" varchar(50), - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "rfqs" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_code" varchar(50), - "project_id" integer, - "description" varchar(255), - "due_date" date NOT NULL, - "status" varchar(30) DEFAULT 'DRAFT' NOT NULL, - "rfq_type" varchar(30) DEFAULT 'PURCHASE', - "parent_rfq_id" integer, - "created_by" integer NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL, - CONSTRAINT "rfqs_rfq_code_unique" UNIQUE("rfq_code") -); ---> statement-breakpoint -CREATE TABLE "vendor_commercial_responses" ( - "id" serial PRIMARY KEY NOT NULL, - "response_id" integer NOT NULL, - "total_price" numeric(18, 2), - "currency" varchar(10) DEFAULT 'USD', - "payment_terms" varchar(255), - "incoterms" varchar(50), - "delivery_period" varchar(100), - "warranty_period" varchar(100), - "validity_period" varchar(100), - "price_breakdown" text, - "commercial_notes" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_response_attachments" ( - "id" serial PRIMARY KEY NOT NULL, - "response_id" integer, - "technical_response_id" integer, - "commercial_response_id" integer, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "file_type" varchar(50), - "attachment_type" varchar(50), - "description" varchar(255), - "uploaded_at" timestamp DEFAULT now() NOT NULL, - "uploaded_by" varchar(255) -); ---> statement-breakpoint -CREATE TABLE "vendor_responses" ( - "id" serial PRIMARY KEY NOT NULL, - "rfq_id" integer NOT NULL, - "vendor_id" integer NOT NULL, - "response_status" varchar(30) DEFAULT 'REVIEWING' NOT NULL, - "notes" text, - "responded_by" varchar(255), - "responded_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_technical_responses" ( - "id" serial PRIMARY KEY NOT NULL, - "response_id" integer NOT NULL, - "summary" text, - "notes" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "tasks" ( - "id" varchar(30) PRIMARY KEY NOT NULL, - "code" varchar(128) DEFAULT concat('TASK-', to_char(nextval('tasks_code_seq'), 'FM0000')) NOT NULL, - "title" varchar(128), - "status" varchar(30) DEFAULT 'todo' NOT NULL, - "label" varchar(30) DEFAULT 'bug' NOT NULL, - "priority" varchar(30) DEFAULT 'low' NOT NULL, - "archived" boolean DEFAULT false NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT current_timestamp, - CONSTRAINT "tasks_code_unique" UNIQUE("code") -); ---> statement-breakpoint -CREATE TABLE "otps" ( - "email" varchar(256) PRIMARY KEY NOT NULL, - "code" varchar(6) NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "otpToken" varchar(512) NOT NULL, - "otp_expires" timestamp NOT NULL -); ---> statement-breakpoint -CREATE TABLE "permissions" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permissions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "permission_key" text NOT NULL, - "description" text, - "created_at" timestamp DEFAULT now() -); ---> statement-breakpoint -CREATE TABLE "role_permissions" ( - "role_id" integer NOT NULL, - "permission_id" integer NOT NULL -); ---> statement-breakpoint -CREATE TABLE "roles" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "roles_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "name" text NOT NULL, - "domain" "user_domain" NOT NULL, - "company_id" integer, - "description" text DEFAULT '' NOT NULL, - "created_at" timestamp DEFAULT now() -); ---> statement-breakpoint -CREATE TABLE "user_roles" ( - "user_id" integer NOT NULL, - "role_id" integer NOT NULL -); ---> statement-breakpoint -CREATE TABLE "users" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "name" varchar(255) NOT NULL, - "email" varchar(255) NOT NULL, - "company_id" integer, - "domain" "user_domain" DEFAULT 'partners' NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "image_url" varchar(1024), - CONSTRAINT "users_email_unique" UNIQUE("email") -); ---> statement-breakpoint -CREATE TABLE "form_entries" ( - "id" serial PRIMARY KEY NOT NULL, - "form_code" varchar(50) NOT NULL, - "data" jsonb NOT NULL, - "contract_item_id" integer NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "form_metas" ( - "id" serial PRIMARY KEY NOT NULL, - "form_code" varchar(50) NOT NULL, - "form_name" varchar(255) NOT NULL, - "columns" jsonb NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "forms" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "forms_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "contract_item_id" integer NOT NULL, - "form_code" varchar(100) NOT NULL, - "form_name" varchar(255) NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "form_templates" ( - "id" serial PRIMARY KEY NOT NULL, - "form_id" integer, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "tag_classes" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "tag_classes_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "code" varchar(100) NOT NULL, - "label" text NOT NULL, - "tag_type_code" varchar(50) NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "tag_subfield_options" ( - "id" serial PRIMARY KEY NOT NULL, - "attributes_id" varchar(50) NOT NULL, - "code" varchar(50) NOT NULL, - "label" text NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "tag_subfields" ( - "id" serial PRIMARY KEY NOT NULL, - "tag_type_code" varchar(50) NOT NULL, - "attributes_id" varchar(50) NOT NULL, - "attributes_description" text NOT NULL, - "expression" text, - "delimiter" varchar(10), - "sort_order" integer DEFAULT 0 NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL, - CONSTRAINT "uniq_tag_type_attribute" UNIQUE("tag_type_code","attributes_id") -); ---> statement-breakpoint -CREATE TABLE "tag_type_class_form_mappings" ( - "id" serial PRIMARY KEY NOT NULL, - "tag_type_label" varchar(255) NOT NULL, - "class_label" varchar(255) NOT NULL, - "form_code" varchar(50) NOT NULL, - "form_name" varchar(255) NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "tag_types" ( - "code" varchar(50) PRIMARY KEY NOT NULL, - "description" text NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "tags" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "tags_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "contract_item_id" integer NOT NULL, - "form_id" integer, - "tag_no" varchar(100) NOT NULL, - "tag_type" varchar(50) NOT NULL, - "class" varchar(100) NOT NULL, - "description" text, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_data_report_temps" ( - "id" serial PRIMARY KEY NOT NULL, - "contract_item_id" integer NOT NULL, - "form_id" integer NOT NULL, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "created_at" timestamp with time zone DEFAULT now() NOT NULL, - "updated_at" timestamp with time zone DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "view_tag_subfields" ( - "id" integer PRIMARY KEY NOT NULL, - "tag_type_code" varchar(50) NOT NULL, - "tag_type_description" text, - "attributes_id" varchar(50) NOT NULL, - "attributes_description" text NOT NULL, - "expression" text, - "delimiter" varchar(10), - "sort_order" integer DEFAULT 0 NOT NULL, - "created_at" timestamp with time zone, - "updated_at" timestamp with time zone -); ---> statement-breakpoint -CREATE TABLE "document_attachments" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "document_attachments_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "revision_id" integer NOT NULL, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "file_type" varchar(50), - "file_size" integer, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "documents" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "documents_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "contract_id" integer NOT NULL, - "doc_number" varchar(100) NOT NULL, - "title" varchar(255) NOT NULL, - "status" varchar(50) DEFAULT 'ACTIVE' NOT NULL, - "issued_date" date, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "issue_stages" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "issue_stages_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "document_id" integer NOT NULL, - "stage_name" varchar(100) NOT NULL, - "plan_date" date, - "actual_date" date, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "revisions" ( - "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "revisions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), - "issue_stage_id" integer NOT NULL, - "revision" varchar(50) NOT NULL, - "uploader_type" varchar(20) DEFAULT 'vendor' NOT NULL, - "uploader_id" integer, - "uploader_name" varchar(100), - "comment" varchar(500), - "status" varchar(50), - "approved_date" date, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_attachments" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_id" integer, - "file_name" varchar(255) NOT NULL, - "file_path" varchar(1024) NOT NULL, - "attachment_type" varchar(50) DEFAULT 'GENERAL', - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_contacts" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_id" integer NOT NULL, - "contact_name" varchar(255) NOT NULL, - "contact_position" varchar(100), - "contact_email" varchar(255) NOT NULL, - "contact_phone" varchar(50), - "is_primary" boolean DEFAULT false NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendor_possible_items" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_id" integer NOT NULL, - "item_code" varchar(100) NOT NULL, - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -CREATE TABLE "vendors" ( - "id" serial PRIMARY KEY NOT NULL, - "vendor_name" varchar(255) NOT NULL, - "vendor_code" varchar(100), - "tax_id" varchar(100) NOT NULL, - "address" text, - "country" varchar(100), - "phone" varchar(50), - "email" varchar(255), - "website" varchar(255), - "status" varchar(30) DEFAULT 'PENDING_REVIEW' NOT NULL, - "representative_name" varchar(255), - "representative_birth" varchar(20), - "representative_email" varchar(255), - "representative_phone" varchar(50), - "corporate_registration_number" varchar(100), - "credit_agency" varchar(50), - "credit_rating" varchar(50), - "cash_flow_rating" varchar(50), - "created_at" timestamp DEFAULT now() NOT NULL, - "updated_at" timestamp DEFAULT now() NOT NULL -); ---> statement-breakpoint -ALTER TABLE "contract_envelopes" ADD CONSTRAINT "contract_envelopes_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "contract_items" ADD CONSTRAINT "contract_items_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "contract_signers" ADD CONSTRAINT "contract_signers_envelope_id_contract_envelopes_id_fk" FOREIGN KEY ("envelope_id") REFERENCES "public"."contract_envelopes"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "contract_signers" ADD CONSTRAINT "contract_signers_vendor_contact_id_vendor_contacts_id_fk" FOREIGN KEY ("vendor_contact_id") REFERENCES "public"."vendor_contacts"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "contracts" ADD CONSTRAINT "contracts_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "contracts" ADD CONSTRAINT "contracts_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_criteria_attachments" ADD CONSTRAINT "vendor_criteria_attachments_vendor_criteria_answer_id_vendor_pq_criteria_answers_id_fk" FOREIGN KEY ("vendor_criteria_answer_id") REFERENCES "public"."vendor_pq_criteria_answers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_pq_criteria_answers" ADD CONSTRAINT "vendor_pq_criteria_answers_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_pq_criteria_answers" ADD CONSTRAINT "vendor_pq_criteria_answers_criteria_id_pq_criterias_id_fk" FOREIGN KEY ("criteria_id") REFERENCES "public"."pq_criterias"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_pq_review_logs" ADD CONSTRAINT "vendor_pq_review_logs_vendor_pq_criteria_answer_id_vendor_pq_criteria_answers_id_fk" FOREIGN KEY ("vendor_pq_criteria_answer_id") REFERENCES "public"."vendor_pq_criteria_answers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "cbe_evaluations" ADD CONSTRAINT "cbe_evaluations_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "cbe_evaluations" ADD CONSTRAINT "cbe_evaluations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "cbe_evaluations" ADD CONSTRAINT "cbe_evaluations_evaluated_by_users_id_fk" FOREIGN KEY ("evaluated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_evaluation_id_rfq_evaluations_id_fk" FOREIGN KEY ("evaluation_id") REFERENCES "public"."rfq_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_cbe_id_cbe_evaluations_id_fk" FOREIGN KEY ("cbe_id") REFERENCES "public"."cbe_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_comment_id_rfq_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."rfq_comments"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_commented_by_users_id_fk" FOREIGN KEY ("commented_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_evaluation_id_rfq_evaluations_id_fk" FOREIGN KEY ("evaluation_id") REFERENCES "public"."rfq_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_cbe_id_cbe_evaluations_id_fk" FOREIGN KEY ("cbe_id") REFERENCES "public"."cbe_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_evaluations" ADD CONSTRAINT "rfq_evaluations_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_evaluations" ADD CONSTRAINT "rfq_evaluations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_items" ADD CONSTRAINT "rfq_items_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfq_items" ADD CONSTRAINT "rfq_items_item_code_items_item_code_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("item_code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfqs" ADD CONSTRAINT "rfqs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfqs" ADD CONSTRAINT "rfqs_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "rfqs" ADD CONSTRAINT "rfqs_parent_rfq_id_rfqs_id_fk" FOREIGN KEY ("parent_rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_commercial_responses" ADD CONSTRAINT "vendor_commercial_responses_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_technical_response_id_vendor_technical_responses_id_fk" FOREIGN KEY ("technical_response_id") REFERENCES "public"."vendor_technical_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_commercial_response_id_vendor_commercial_responses_id_fk" FOREIGN KEY ("commercial_response_id") REFERENCES "public"."vendor_commercial_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_responses" ADD CONSTRAINT "vendor_responses_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_responses" ADD CONSTRAINT "vendor_responses_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_technical_responses" ADD CONSTRAINT "vendor_technical_responses_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_permissions_id_fk" FOREIGN KEY ("permission_id") REFERENCES "public"."permissions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "roles" ADD CONSTRAINT "roles_company_id_vendors_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "users" ADD CONSTRAINT "users_company_id_vendors_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "form_entries" ADD CONSTRAINT "form_entries_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "forms" ADD CONSTRAINT "forms_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "form_templates" ADD CONSTRAINT "form_templates_form_id_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "tag_classes" ADD CONSTRAINT "tag_classes_tag_type_code_tag_types_code_fk" FOREIGN KEY ("tag_type_code") REFERENCES "public"."tag_types"("code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "tag_subfield_options" ADD CONSTRAINT "tag_subfield_options_attributes_id_tag_subfields_attributes_id_fk" FOREIGN KEY ("attributes_id") REFERENCES "public"."tag_subfields"("attributes_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "tag_subfields" ADD CONSTRAINT "tag_subfields_tag_type_code_tag_types_code_fk" FOREIGN KEY ("tag_type_code") REFERENCES "public"."tag_types"("code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "tags" ADD CONSTRAINT "tags_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "tags" ADD CONSTRAINT "tags_form_id_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_data_report_temps" ADD CONSTRAINT "vendor_data_report_temps_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_data_report_temps" ADD CONSTRAINT "vendor_data_report_temps_form_id_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "document_attachments" ADD CONSTRAINT "document_attachments_revision_id_revisions_id_fk" FOREIGN KEY ("revision_id") REFERENCES "public"."revisions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "documents" ADD CONSTRAINT "documents_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "issue_stages" ADD CONSTRAINT "issue_stages_document_id_documents_id_fk" FOREIGN KEY ("document_id") REFERENCES "public"."documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_attachments" ADD CONSTRAINT "vendor_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_contacts" ADD CONSTRAINT "vendor_contacts_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_possible_items" ADD CONSTRAINT "vendor_possible_items_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint -ALTER TABLE "vendor_possible_items" ADD CONSTRAINT "vendor_possible_items_item_code_items_item_code_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("item_code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint -CREATE UNIQUE INDEX "contract_items_contract_item_idx" ON "contract_items" USING btree ("contract_id","item_id");--> statement-breakpoint -CREATE UNIQUE INDEX "vendor_response_unique" ON "vendor_responses" USING btree ("rfq_id","vendor_id");--> statement-breakpoint -CREATE UNIQUE INDEX "contract_item_form_code_unique" ON "forms" USING btree ("contract_item_id","form_code");--> statement-breakpoint -CREATE UNIQUE INDEX "unique_contract_doc_status" ON "documents" USING btree ("contract_id","doc_number","status");--> statement-breakpoint -CREATE UNIQUE INDEX "unique_document_stage" ON "issue_stages" USING btree ("document_id","stage_name");--> statement-breakpoint -CREATE UNIQUE INDEX "unique_stage_rev" ON "revisions" USING btree ("issue_stage_id","revision");--> statement-breakpoint -CREATE VIEW "public"."contracts_detail_view" AS (select "contracts"."id", "contracts"."contract_no", "contracts"."contract_name", "contracts"."status", "contracts"."start_date", "contracts"."end_date", "contracts"."project_id", "projects"."code", "projects"."name", "contracts"."vendor_id", "vendors"."vendor_name", "contracts"."payment_terms", "contracts"."delivery_terms", "contracts"."delivery_date", "contracts"."delivery_location", "contracts"."currency", "contracts"."total_amount", "contracts"."discount", "contracts"."tax", "contracts"."shipping_fee", "contracts"."net_total", "contracts"."partial_shipping_allowed", "contracts"."partial_payment_allowed", "contracts"."remarks", "contracts"."version", "contracts"."created_at", "contracts"."updated_at", EXISTS ( - SELECT 1 - FROM "contract_envelopes" - WHERE "contract_envelopes"."contract_id" = "contracts"."id" - ) as "has_signature", EXISTS ( - SELECT 1 - FROM "contract_items" - WHERE "contract_items"."contract_id" = "contracts"."id" - ) as "has_signature", COALESCE(( - SELECT json_agg( - json_build_object( - 'id', ci.id, - 'itemId', ci.item_id, - 'description', ci.description, - 'quantity', ci.quantity, - 'unitPrice', ci.unit_price, - 'taxRate', ci.tax_rate, - 'taxAmount', ci.tax_amount, - 'totalLineAmount', ci.total_line_amount, - 'remark', ci.remark, - 'createdAt', ci.created_at, - 'updatedAt', ci.updated_at - ) - ) - FROM "contract_items" AS ci - WHERE ci.contract_id = "contracts"."id" - ), '[]') as "items", COALESCE(( - SELECT json_agg( - json_build_object( - 'id', ce.id, - 'envelopeId', ce.envelope_id, - 'documentId', ce.document_id, - 'envelopeStatus', ce.envelope_status, - 'fileName', ce.file_name, - 'filePath', ce.file_path, - 'createdAt', ce.created_at, - 'updatedAt', ce.updated_at, - 'signers', ( - SELECT json_agg( - json_build_object( - 'id', cs.id, - 'vendorContactId', cs.vendor_contact_id, - 'signerType', cs.signer_type, - 'signerEmail', cs.signer_email, - 'signerName', cs.signer_name, - 'signerPosition', cs.signer_position, - 'signerStatus', cs.signer_status, - 'signedAt', cs.signed_at - ) - ) - FROM "contract_signers" AS cs - WHERE cs.envelope_id = ce.id - ) - ) - ) - FROM "contract_envelopes" AS ce - WHERE ce.contract_id = "contracts"."id" - ), '[]') as "envelopes" from "contracts" left join "projects" on "contracts"."project_id" = "projects"."id" left join "vendors" on "contracts"."vendor_id" = "vendors"."id");--> statement-breakpoint -CREATE VIEW "public"."cbe_view" AS (select "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."rfq_id" as "rfq_id", "cbe_evaluations"."vendor_id" as "vendor_id", "cbe_evaluations"."total_cost" as "total_cost", "cbe_evaluations"."currency" as "currency", "cbe_evaluations"."payment_terms" as "payment_terms", "cbe_evaluations"."incoterms" as "incoterms", "cbe_evaluations"."result" as "result", "cbe_evaluations"."notes" as "notes", "cbe_evaluations"."evaluated_by" as "evaluated_by", "cbe_evaluations"."evaluated_at" as "evaluated_at", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "users"."name" as "evaluator_name", "users"."email" as "evaluator_email" from "cbe_evaluations" inner join "rfqs" on "cbe_evaluations"."rfq_id" = "rfqs"."id" inner join "vendors" on "cbe_evaluations"."vendor_id" = "vendors"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "users" on "cbe_evaluations"."evaluated_by" = "users"."id");--> statement-breakpoint -CREATE VIEW "public"."rfqs_view" AS (select "rfqs"."id" as "rfq_id", "rfqs"."status" as "status", "rfqs"."created_at" as "created_at", "rfqs"."updated_at" as "updated_at", "rfqs"."created_by" as "created_by", "rfqs"."rfq_type" as "rfq_type", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "rfqs"."parent_rfq_id" as "parent_rfq_id", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "users"."email" as "user_email", "users"."name" as "user_name", ( - SELECT COUNT(*) - FROM "rfq_items" - WHERE "rfq_items"."rfq_id" = "rfqs"."id" - ) as "item_count", ( - SELECT COUNT(*) - FROM "rfq_attachments" - WHERE "rfq_attachments"."rfq_id" = "rfqs"."id" - ) as "attachment_count" from "rfqs" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "users" on "rfqs"."created_by" = "users"."id");--> statement-breakpoint -CREATE VIEW "public"."vendor_cbe_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."id" as "vendor_response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."rfq_type" as "rfq_type", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."result" as "cbe_result", "cbe_evaluations"."notes" as "cbe_note", "cbe_evaluations"."updated_at" as "cbe_updated", "cbe_evaluations"."total_cost" as "total_cost", "cbe_evaluations"."currency" as "currency", "cbe_evaluations"."payment_terms" as "payment_terms", "cbe_evaluations"."incoterms" as "incoterms", "cbe_evaluations"."delivery_schedule" as "delivery_schedule" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "cbe_evaluations" on ("cbe_evaluations"."vendor_id" = "vendors"."id" and "cbe_evaluations"."rfq_id" = "vendor_responses"."rfq_id"));--> statement-breakpoint -CREATE VIEW "public"."vendor_responses_view" AS (select "vendor_responses"."id" as "response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."vendor_id" as "vendor_id", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "rfqs"."due_date" as "rfq_due_date", "rfqs"."status" as "rfq_status", "rfqs"."rfq_type" as "rfq_type", "rfqs"."created_at" as "rfq_created_at", "rfqs"."updated_at" as "rfq_updated_at", "rfqs"."created_by" as "rfq_created_by", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendor_responses"."response_status" as "response_status", "vendor_responses"."responded_at" as "responded_at", CASE WHEN "vendor_technical_responses"."id" IS NOT NULL THEN TRUE ELSE FALSE END as "has_technical_response", "vendor_technical_responses"."id" as "technical_response_id", CASE WHEN "vendor_commercial_responses"."id" IS NOT NULL THEN TRUE ELSE FALSE END as "has_commercial_response", "vendor_commercial_responses"."id" as "commercial_response_id", "vendor_commercial_responses"."total_price" as "total_price", "vendor_commercial_responses"."currency" as "currency", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."result" as "cbe_result", ( - SELECT COUNT(*) - FROM "vendor_response_attachments" - WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id" - ) as "attachment_count" from "vendor_responses" inner join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" inner join "vendors" on "vendor_responses"."vendor_id" = "vendors"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "vendor_technical_responses" on "vendor_technical_responses"."response_id" = "vendor_responses"."id" left join "vendor_commercial_responses" on "vendor_commercial_responses"."response_id" = "vendor_responses"."id" left join "rfq_evaluations" on ("rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id" and "rfq_evaluations"."vendor_id" = "vendor_responses"."vendor_id" and "rfq_evaluations"."eval_type" = 'TBE') left join "cbe_evaluations" on ("cbe_evaluations"."rfq_id" = "vendor_responses"."rfq_id" and "cbe_evaluations"."vendor_id" = "vendor_responses"."vendor_id"));--> statement-breakpoint -CREATE VIEW "public"."vendor_rfq_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id");--> statement-breakpoint -CREATE VIEW "public"."vendor_tbe_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."id" as "vendor_response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."rfq_type" as "rfq_type", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "rfq_evaluations"."notes" as "tbe_note", "rfq_evaluations"."updated_at" as "tbe_updated" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "rfq_evaluations" on ("rfq_evaluations"."vendor_id" = "vendors"."id" and "rfq_evaluations"."eval_type" = 'TBE' and "rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id"));--> statement-breakpoint -CREATE VIEW "public"."role_view" AS (select "roles"."id" as "id", "roles"."name" as "name", "roles"."description" as "description", "roles"."domain" as "domain", "roles"."created_at" as "created_at", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name", COUNT("users"."id") as "user_count" from "roles" left join "user_roles" on "user_roles"."role_id" = "roles"."id" left join "users" on "users"."id" = "user_roles"."user_id" left join "vendors" on "roles"."company_id" = "vendors"."id" group by "roles"."id", "vendors"."id");--> statement-breakpoint -CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."email" as "user_email", "users"."domain" as "user_domain", "users"."image_url" as "user_image", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name", - array_agg("roles"."name") - as "roles", "users"."created_at" as "created_at" from "users" left join "vendors" on "users"."company_id" = "vendors"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "vendors"."id");--> statement-breakpoint -CREATE VIEW "public"."document_stages_view" AS ( - SELECT - d.id AS document_id, - d.doc_number, - d.title, - d.status, - d.issued_date, - d.contract_id, - - ( - SELECT COUNT(*) - FROM issue_stages - WHERE document_id = d.id - ) AS stage_count, - - COALESCE( - ( - SELECT json_agg(i.stage_name) - FROM issue_stages i - WHERE i.document_id = d.id - ), - '[]' - ) AS stage_list, - - d.created_at, - d.updated_at - FROM documents d -);--> statement-breakpoint -CREATE VIEW "public"."vendor_documents_view" AS ( - SELECT - d.id, - d.doc_number, - d.title, - d.status, - d.issued_date, - - d.contract_id, - - ( - SELECT id FROM issue_stages - WHERE document_id = d.id - ORDER BY created_at DESC LIMIT 1 - ) AS latest_stage_id, - ( - SELECT stage_name FROM issue_stages - WHERE document_id = d.id - ORDER BY created_at DESC LIMIT 1 - ) AS latest_stage_name, - ( - SELECT plan_date FROM issue_stages - WHERE document_id = d.id - ORDER BY created_at DESC LIMIT 1 - ) AS latest_stage_plan_date, - ( - SELECT actual_date FROM issue_stages - WHERE document_id = d.id - ORDER BY created_at DESC LIMIT 1 - ) AS latest_stage_actual_date, - - ( - SELECT r.id FROM revisions r - JOIN issue_stages i ON r.issue_stage_id = i.id - WHERE i.document_id = d.id - ORDER BY r.created_at DESC LIMIT 1 - ) AS latest_revision_id, - ( - SELECT r.revision FROM revisions r - JOIN issue_stages i ON r.issue_stage_id = i.id - WHERE i.document_id = d.id - ORDER BY r.created_at DESC LIMIT 1 - ) AS latest_revision, - ( - SELECT r.uploader_type FROM revisions r - JOIN issue_stages i ON r.issue_stage_id = i.id - WHERE i.document_id = d.id - ORDER BY r.created_at DESC LIMIT 1 - ) AS latest_revision_uploader_type, - ( - SELECT r.uploader_name FROM revisions r - JOIN issue_stages i ON r.issue_stage_id = i.id - WHERE i.document_id = d.id - ORDER BY r.created_at DESC LIMIT 1 - ) AS latest_revision_uploader_name, - - ( - SELECT COUNT(*) FROM document_attachments a - JOIN revisions r ON a.revision_id = r.id - JOIN issue_stages i ON r.issue_stage_id = i.id - WHERE i.document_id = d.id - ) AS attachment_count, - - d.created_at, - d.updated_at - FROM documents d - JOIN contracts c ON d.contract_id = c.id - );--> statement-breakpoint -CREATE VIEW "public"."vendor_items_view" AS (select "vendor_possible_items"."id", "vendor_possible_items"."vendor_id", "items"."item_name", "items"."item_code", "items"."description", "vendor_possible_items"."created_at", "vendor_possible_items"."updated_at" from "vendor_possible_items" left join "items" on "vendor_possible_items"."item_code" = "items"."item_code");
\ No newline at end of file |