buildfor_life_ops/drizzle/0002_phase1_constraints_and_search.sql

-- Phase 1 follow-up: integrity constraints, FTS, GIN, partial FK,
-- and updated_at + asset_types.schema_version triggers.
-- Drizzle-kit can't express any of these from the TS schema, so they live here.

-- ---------------------------------------------------------------------------
-- 1. assets.current_container_kind XOR location FK
-- ---------------------------------------------------------------------------
ALTER TABLE "assets" ADD CONSTRAINT "assets_location_xor" CHECK (
	(current_container_kind = 'project'  AND current_project_id  IS NOT NULL AND current_property_id IS NULL)
	OR
	(current_container_kind = 'property' AND current_property_id IS NOT NULL AND current_project_id  IS NULL)
);
--> statement-breakpoint

ALTER TABLE "asset_location_history" ADD CONSTRAINT "alh_to_xor" CHECK (
	(to_kind = 'project'  AND to_project_id  IS NOT NULL AND to_property_id IS NULL)
	OR
	(to_kind = 'property' AND to_property_id IS NOT NULL AND to_project_id  IS NULL)
);
--> statement-breakpoint

-- ---------------------------------------------------------------------------
-- 2. Self-referential FK on asset_types.parent_id
-- ---------------------------------------------------------------------------
ALTER TABLE "asset_types" ADD CONSTRAINT "asset_types_parent_id_fk"
	FOREIGN KEY ("parent_id") REFERENCES "asset_types"("id") ON DELETE SET NULL;
--> statement-breakpoint

-- ---------------------------------------------------------------------------
-- 3. JSONB GIN index on assets.custom_fields
-- ---------------------------------------------------------------------------
CREATE INDEX "assets_custom_fields_gin" ON "assets" USING GIN ("custom_fields" jsonb_path_ops);
--> statement-breakpoint

-- ---------------------------------------------------------------------------
-- 4. Full-text search on assets (tsvector + trigger + GIN)
-- ---------------------------------------------------------------------------
ALTER TABLE "assets" ALTER COLUMN "search_tsv" TYPE tsvector USING NULL::tsvector;
--> statement-breakpoint

CREATE OR REPLACE FUNCTION assets_tsv_trigger() RETURNS trigger AS $$
BEGIN
	NEW.search_tsv :=
		setweight(to_tsvector('simple', coalesce(NEW.name, '')), 'A') ||
		setweight(to_tsvector('simple', coalesce(NEW.tag, '')), 'B') ||
		setweight(to_tsvector('simple', coalesce(NEW.serial_number, '')), 'B') ||
		setweight(to_tsvector('simple', coalesce(NEW.manufacturer, '')), 'C') ||
		setweight(to_tsvector('simple', coalesce(NEW.model, '')), 'C');
	RETURN NEW;
END $$ LANGUAGE plpgsql;
--> statement-breakpoint

CREATE TRIGGER assets_tsv_upd BEFORE INSERT OR UPDATE
	ON "assets" FOR EACH ROW EXECUTE FUNCTION assets_tsv_trigger();
--> statement-breakpoint

CREATE INDEX "assets_search_tsv_gin" ON "assets" USING GIN ("search_tsv");
--> statement-breakpoint

-- ---------------------------------------------------------------------------
-- 5. Generic updated_at trigger, attached to every Phase-0/1 table that has it
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
	NEW.updated_at := now();
	RETURN NEW;
END $$ LANGUAGE plpgsql;
--> statement-breakpoint

CREATE TRIGGER companies_set_updated_at BEFORE UPDATE ON "companies"
	FOR EACH ROW EXECUTE FUNCTION set_updated_at();
--> statement-breakpoint
CREATE TRIGGER users_set_updated_at BEFORE UPDATE ON "users"
	FOR EACH ROW EXECUTE FUNCTION set_updated_at();
--> statement-breakpoint
CREATE TRIGGER properties_set_updated_at BEFORE UPDATE ON "properties"
	FOR EACH ROW EXECUTE FUNCTION set_updated_at();
--> statement-breakpoint
CREATE TRIGGER asset_types_set_updated_at BEFORE UPDATE ON "asset_types"
	FOR EACH ROW EXECUTE FUNCTION set_updated_at();
--> statement-breakpoint
CREATE TRIGGER asset_field_defs_set_updated_at BEFORE UPDATE ON "asset_field_defs"
	FOR EACH ROW EXECUTE FUNCTION set_updated_at();
--> statement-breakpoint
CREATE TRIGGER assets_set_updated_at BEFORE UPDATE ON "assets"
	FOR EACH ROW EXECUTE FUNCTION set_updated_at();
--> statement-breakpoint

-- ---------------------------------------------------------------------------
-- 6. Bump asset_types.schema_version when its field defs change
--    (cache key for the runtime Zod validator)
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION bump_asset_type_schema_version() RETURNS trigger AS $$
DECLARE
	target_id uuid;
BEGIN
	IF TG_OP = 'DELETE' THEN
		target_id := OLD.asset_type_id;
	ELSE
		target_id := NEW.asset_type_id;
	END IF;
	UPDATE asset_types
	   SET schema_version = schema_version + 1,
	       updated_at = now()
	 WHERE id = target_id;
	RETURN NULL;
END $$ LANGUAGE plpgsql;
--> statement-breakpoint

CREATE TRIGGER asset_field_defs_bump_version
	AFTER INSERT OR UPDATE OR DELETE ON "asset_field_defs"
	FOR EACH ROW EXECUTE FUNCTION bump_asset_type_schema_version();