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, 783 insertions, 0 deletions
diff --git a/db/migrations_backup/0094_fresh_blur.sql b/db/migrations_backup/0094_fresh_blur.sql new file mode 100644 index 00000000..007336d2 --- /dev/null +++ b/db/migrations_backup/0094_fresh_blur.sql @@ -0,0 +1,783 @@ +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 |