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;