diff options
Diffstat (limited to 'db/migrations/0299_sweet_sebastian_shaw.sql')
| -rw-r--r-- | db/migrations/0299_sweet_sebastian_shaw.sql | 77 |
1 files changed, 77 insertions, 0 deletions
diff --git a/db/migrations/0299_sweet_sebastian_shaw.sql b/db/migrations/0299_sweet_sebastian_shaw.sql new file mode 100644 index 00000000..371eda98 --- /dev/null +++ b/db/migrations/0299_sweet_sebastian_shaw.sql @@ -0,0 +1,77 @@ +CREATE TABLE "compliance_question_options" ( + "id" serial PRIMARY KEY NOT NULL, + "question_id" integer NOT NULL, + "option_value" varchar(100) NOT NULL, + "option_text" varchar(255) NOT NULL, + "allows_other_input" boolean DEFAULT false NOT NULL, + "display_order" integer NOT NULL +); +--> statement-breakpoint +CREATE TABLE "compliance_questions" ( + "id" serial PRIMARY KEY NOT NULL, + "template_id" integer NOT NULL, + "question_number" varchar(10) NOT NULL, + "question_text" text NOT NULL, + "question_type" varchar(20) NOT NULL, + "is_required" boolean DEFAULT true NOT NULL, + "has_detail_text" boolean DEFAULT false NOT NULL, + "has_file_upload" boolean DEFAULT false NOT NULL, + "parent_question_id" integer, + "conditional_value" varchar(100), + "display_order" integer NOT NULL, + "created_at" timestamp DEFAULT now() +); +--> statement-breakpoint +CREATE TABLE "compliance_response_answers" ( + "id" serial PRIMARY KEY NOT NULL, + "response_id" integer NOT NULL, + "question_id" integer NOT NULL, + "answer_value" text, + "detail_text" text, + "other_text" varchar(500), + "percentage_value" numeric(5, 2), + "created_at" timestamp DEFAULT now(), + "updated_at" timestamp DEFAULT now() +); +--> statement-breakpoint +CREATE TABLE "compliance_response_files" ( + "id" serial PRIMARY KEY NOT NULL, + "answer_id" integer NOT NULL, + "file_name" varchar(255) NOT NULL, + "file_path" varchar(1024) NOT NULL, + "file_size" integer, + "mime_type" varchar(100), + "uploaded_at" timestamp DEFAULT now() +); +--> statement-breakpoint +CREATE TABLE "compliance_responses" ( + "id" serial PRIMARY KEY NOT NULL, + "basic_contract_id" integer NOT NULL, + "template_id" integer NOT NULL, + "status" varchar(20) DEFAULT 'IN_PROGRESS' NOT NULL, + "completed_at" timestamp, + "reviewed_by" integer, + "reviewed_at" timestamp, + "review_notes" text, + "created_at" timestamp DEFAULT now(), + "updated_at" timestamp DEFAULT now() +); +--> statement-breakpoint +CREATE TABLE "compliance_survey_templates" ( + "id" serial PRIMARY KEY NOT NULL, + "name" varchar(255) NOT NULL, + "description" text, + "version" varchar(50) DEFAULT '1.0' NOT NULL, + "is_active" boolean DEFAULT true NOT NULL, + "created_at" timestamp DEFAULT now(), + "updated_at" timestamp DEFAULT now() +); +--> statement-breakpoint +ALTER TABLE "compliance_question_options" ADD CONSTRAINT "compliance_question_options_question_id_compliance_questions_id_fk" FOREIGN KEY ("question_id") REFERENCES "public"."compliance_questions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_questions" ADD CONSTRAINT "compliance_questions_template_id_compliance_survey_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."compliance_survey_templates"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_response_answers" ADD CONSTRAINT "compliance_response_answers_response_id_compliance_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."compliance_responses"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_response_answers" ADD CONSTRAINT "compliance_response_answers_question_id_compliance_questions_id_fk" FOREIGN KEY ("question_id") REFERENCES "public"."compliance_questions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_response_files" ADD CONSTRAINT "compliance_response_files_answer_id_compliance_response_answers_id_fk" FOREIGN KEY ("answer_id") REFERENCES "public"."compliance_response_answers"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_responses" ADD CONSTRAINT "compliance_responses_basic_contract_id_basic_contract_id_fk" FOREIGN KEY ("basic_contract_id") REFERENCES "public"."basic_contract"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_responses" ADD CONSTRAINT "compliance_responses_template_id_compliance_survey_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."compliance_survey_templates"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "compliance_responses" ADD CONSTRAINT "compliance_responses_reviewed_by_users_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
\ No newline at end of file |