CREATE TYPE "public"."approver_decision" AS ENUM('PENDING', 'APPROVED', 'REJECTED');--> statement-breakpoint
CREATE TYPE "public"."proposal_status" AS ENUM('PENDING', 'APPROVED', 'REJECTED');--> statement-breakpoint
CREATE TABLE "company_documents" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"title" text NOT NULL,
	"file_name" text NOT NULL,
	"mime_type" text NOT NULL,
	"file_size" integer NOT NULL,
	"file_path" text NOT NULL,
	"uploaded_by" uuid,
	"uploaded_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "customer_course_completions" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"course_id" uuid NOT NULL,
	"completed_date" date NOT NULL,
	"is_free" boolean DEFAULT false NOT NULL,
	"note" text,
	"recorded_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "proposal_approvers" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"proposal_id" uuid NOT NULL,
	"approver_id" uuid NOT NULL,
	"decision" "approver_decision" DEFAULT 'PENDING' NOT NULL,
	"note" text,
	"decided_at" timestamp with time zone,
	"sort_order" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "proposals" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"title" text NOT NULL,
	"content" text NOT NULL,
	"created_by" uuid NOT NULL,
	"status" "proposal_status" DEFAULT 'PENDING' NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "training_courses" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"is_active" boolean DEFAULT true NOT NULL
);
--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "alert_revenue_drop_percent" integer DEFAULT 30 NOT NULL;--> statement-breakpoint
ALTER TABLE "tenants" ADD COLUMN "alert_staff_min_achievement_percent" integer DEFAULT 70 NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "can_upload_documents" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "customer_course_completions" ADD CONSTRAINT "customer_course_completions_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "customer_course_completions" ADD CONSTRAINT "customer_course_completions_course_id_training_courses_id_fk" FOREIGN KEY ("course_id") REFERENCES "public"."training_courses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "proposal_approvers" ADD CONSTRAINT "proposal_approvers_proposal_id_proposals_id_fk" FOREIGN KEY ("proposal_id") REFERENCES "public"."proposals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "company_documents_tenant_idx" ON "company_documents" USING btree ("tenant_id","uploaded_at");--> statement-breakpoint
CREATE INDEX "customer_course_completions_customer_idx" ON "customer_course_completions" USING btree ("customer_id");--> statement-breakpoint
CREATE INDEX "proposal_approvers_proposal_idx" ON "proposal_approvers" USING btree ("proposal_id");--> statement-breakpoint
CREATE INDEX "proposal_approvers_approver_idx" ON "proposal_approvers" USING btree ("approver_id","decision");--> statement-breakpoint
CREATE INDEX "proposals_tenant_status_idx" ON "proposals" USING btree ("tenant_id","status","created_at");--> statement-breakpoint
CREATE INDEX "training_courses_tenant_idx" ON "training_courses" USING btree ("tenant_id");