CREATE TABLE "catalog_shares" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"template_id" uuid NOT NULL,
	"public_token" text NOT NULL,
	"is_active" boolean DEFAULT true NOT NULL,
	"settings" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"utm_source" text,
	"utm_medium" text,
	"utm_campaign" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "catalog_shares_public_token_unique" UNIQUE("public_token")
);
--> statement-breakpoint
CREATE TABLE "catalog_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"name" text NOT NULL,
	"content" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"is_active" boolean DEFAULT true 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 "contract_audit_log" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"contract_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"event" text NOT NULL,
	"actor_type" text NOT NULL,
	"actor_id" text,
	"ip_address" text,
	"user_agent" text,
	"metadata" jsonb DEFAULT '{}'::jsonb,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "contract_audit_log_event_check" CHECK ("contract_audit_log"."event" IN ('sent','viewed','signed','declined','voided','downloaded','reminder_sent')),
	CONSTRAINT "contract_audit_log_actor_type_check" CHECK ("contract_audit_log"."actor_type" IN ('staff','signatory'))
);
--> statement-breakpoint
CREATE TABLE "contract_signatories" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"contract_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"email" text NOT NULL,
	"name" text,
	"token" text NOT NULL,
	"token_expires_at" timestamp with time zone,
	"signed_at" timestamp with time zone,
	"declined_at" timestamp with time zone,
	"signature_data" jsonb,
	"signature_type" text,
	"signer_ip" text,
	"signer_user_agent" text,
	"signing_order" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "contract_signatories_token_unique" UNIQUE("token"),
	CONSTRAINT "contract_signatories_signature_type_check" CHECK ("contract_signatories"."signature_type" IS NULL OR "contract_signatories"."signature_type" IN ('draw','type'))
);
--> statement-breakpoint
CREATE TABLE "proposal_view_events" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"proposal_id" uuid NOT NULL,
	"tenant_id" uuid NOT NULL,
	"ip" text,
	"user_agent" text,
	"referrer" text,
	"utm_source" text,
	"utm_medium" text,
	"utm_campaign" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "portal_files" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"customer_id" uuid NOT NULL,
	"project_id" uuid,
	"uploaded_by" uuid,
	"uploaded_by_portal_user" uuid,
	"r2_key" text NOT NULL,
	"filename" text NOT NULL,
	"file_size_bytes" integer NOT NULL,
	"mime_type" text NOT NULL,
	"description" text,
	"visible_to_portal" boolean DEFAULT true NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone,
	CONSTRAINT "portal_files_r2_key_unique" UNIQUE("r2_key")
);
--> statement-breakpoint
CREATE TABLE "proposal_templates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid,
	"name" text NOT NULL,
	"content" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "proposals" DROP CONSTRAINT "proposals_status_check";--> statement-breakpoint
ALTER TABLE "contracts" ADD COLUMN "signing_deadline" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "contracts" ADD COLUMN "signed_pdf_r2_key" text;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "name" text;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "content" jsonb;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "total_amount" numeric(10, 2);--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "expires_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "public_token" text;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "view_count" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "first_viewed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "last_viewed_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "locale" text DEFAULT 'he';--> statement-breakpoint
ALTER TABLE "proposals" ADD COLUMN "created_by" uuid;--> statement-breakpoint
ALTER TABLE "catalog_shares" ADD CONSTRAINT "catalog_shares_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "catalog_shares" ADD CONSTRAINT "catalog_shares_template_id_catalog_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."catalog_templates"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "catalog_templates" ADD CONSTRAINT "catalog_templates_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contract_audit_log" ADD CONSTRAINT "contract_audit_log_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_audit_log" ADD CONSTRAINT "contract_audit_log_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "contract_signatories" ADD CONSTRAINT "contract_signatories_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_signatories" ADD CONSTRAINT "contract_signatories_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "proposal_view_events" ADD CONSTRAINT "proposal_view_events_proposal_id_proposals_id_fk" FOREIGN KEY ("proposal_id") REFERENCES "public"."proposals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portal_files" ADD CONSTRAINT "portal_files_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portal_files" ADD CONSTRAINT "portal_files_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portal_files" ADD CONSTRAINT "portal_files_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 "portal_files" ADD CONSTRAINT "portal_files_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "proposal_templates" ADD CONSTRAINT "proposal_templates_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "proposal_templates" ADD CONSTRAINT "proposal_templates_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_catalog_shares_tenant" ON "catalog_shares" USING btree ("tenant_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_catalog_shares_public_token" ON "catalog_shares" USING btree ("public_token");--> statement-breakpoint
CREATE INDEX "idx_catalog_templates_tenant" ON "catalog_templates" USING btree ("tenant_id");--> statement-breakpoint
CREATE INDEX "idx_contract_audit_log_contract" ON "contract_audit_log" USING btree ("contract_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_signatories_contract" ON "contract_signatories" USING btree ("contract_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_signatories_token" ON "contract_signatories" USING btree ("token");--> statement-breakpoint
CREATE INDEX "idx_proposal_view_events_proposal" ON "proposal_view_events" USING btree ("proposal_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_proposal_view_events_tenant" ON "proposal_view_events" USING btree ("tenant_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_portal_files_customer" ON "portal_files" USING btree ("tenant_id","customer_id","created_at");--> statement-breakpoint
CREATE INDEX "idx_proposal_templates_tenant" ON "proposal_templates" USING btree ("tenant_id");--> statement-breakpoint
CREATE UNIQUE INDEX "idx_proposals_public_token" ON "proposals" USING btree ("public_token");--> statement-breakpoint
ALTER TABLE "proposals" ADD CONSTRAINT "proposals_public_token_unique" UNIQUE("public_token");--> statement-breakpoint
ALTER TABLE "proposals" ADD CONSTRAINT "proposals_locale_check" CHECK ("proposals"."locale" IN ('he','en'));--> statement-breakpoint
ALTER TABLE "proposals" ADD CONSTRAINT "proposals_status_check" CHECK ("proposals"."status" IN ('draft','sent','viewed','accepted','rejected'));