-- Sankofa HW Infra initial schema
CREATE TABLE IF NOT EXISTS "org_units" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "name" text NOT NULL,
  "parent_id" uuid,
  "org_id" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "email" text NOT NULL UNIQUE,
  "name" text,
  "org_unit_id" uuid,
  "org_id" text NOT NULL,
  "external_id" text,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "users" ADD CONSTRAINT "users_org_unit_id_org_units_id_fk" FOREIGN KEY ("org_unit_id") REFERENCES "public"."org_units"("id");
ALTER TABLE "org_units" ADD CONSTRAINT "org_units_parent_id_org_units_id_fk" FOREIGN KEY ("parent_id") REFERENCES "public"."org_units"("id");

CREATE TABLE IF NOT EXISTS "vendors" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "legal_name" text NOT NULL,
  "contacts" jsonb,
  "trust_tier" text DEFAULT 'unknown' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "vendor_bank_details" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "vendor_id" uuid NOT NULL,
  "version" integer DEFAULT 1 NOT NULL,
  "instructions" jsonb,
  "approved_by_1" uuid,
  "approved_by_2" uuid,
  "approved_at" timestamp,
  "created_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "vendor_bank_details" ADD CONSTRAINT "vendor_bank_details_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id");

CREATE TABLE IF NOT EXISTS "offers" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "vendor_id" uuid NOT NULL,
  "sku" text,
  "mpn" text,
  "quantity" integer NOT NULL,
  "unit_price" decimal(20, 4) NOT NULL,
  "incoterms" text,
  "lead_time_days" integer,
  "country_of_origin" text,
  "condition" text,
  "warranty" text,
  "evidence_refs" jsonb,
  "risk_score" decimal(5, 2),
  "risk_factors" jsonb,
  "status" text DEFAULT 'draft' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "offers_vendor_idx" ON "offers" ("vendor_id");
CREATE INDEX IF NOT EXISTS "offers_org_idx" ON "offers" ("org_id");
ALTER TABLE "offers" ADD CONSTRAINT "offers_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id");

CREATE TABLE IF NOT EXISTS "regions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "sites" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "region_id" uuid,
  "name" text NOT NULL,
  "address" text,
  "network_metadata" jsonb,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "sites_org_idx" ON "sites" ("org_id");
ALTER TABLE "sites" ADD CONSTRAINT "sites_region_id_regions_id_fk" FOREIGN KEY ("region_id") REFERENCES "public"."regions"("id");

CREATE TABLE IF NOT EXISTS "rooms" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "site_id" uuid NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "rooms" ADD CONSTRAINT "rooms_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id");

CREATE TABLE IF NOT EXISTS "rows" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "room_id" uuid NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "rows" ADD CONSTRAINT "rows_room_id_rooms_id_fk" FOREIGN KEY ("room_id") REFERENCES "public"."rooms"("id");

CREATE TABLE IF NOT EXISTS "racks" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "row_id" uuid NOT NULL,
  "name" text NOT NULL,
  "ru_total" integer NOT NULL,
  "power_feeds" jsonb,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "racks_row_idx" ON "racks" ("row_id");
ALTER TABLE "racks" ADD CONSTRAINT "racks_row_id_rows_id_fk" FOREIGN KEY ("row_id") REFERENCES "public"."rows"("id");

CREATE TABLE IF NOT EXISTS "assets" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "asset_id" text NOT NULL UNIQUE,
  "category" text NOT NULL,
  "manufacturer_serial" text,
  "service_tag" text,
  "macs" jsonb,
  "wwns" jsonb,
  "part_number" text,
  "condition" text,
  "warranty" text,
  "proof_artifact_refs" jsonb,
  "site_id" uuid,
  "position_id" uuid,
  "owner_id" uuid,
  "project_id" text,
  "sensitivity_tier" text,
  "status" text DEFAULT 'pending' NOT NULL,
  "chain_of_custody" jsonb,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "assets_org_idx" ON "assets" ("org_id");
CREATE INDEX IF NOT EXISTS "assets_site_idx" ON "assets" ("site_id");
CREATE INDEX IF NOT EXISTS "assets_category_idx" ON "assets" ("category");
CREATE INDEX IF NOT EXISTS "assets_status_idx" ON "assets" ("status");
ALTER TABLE "assets" ADD CONSTRAINT "assets_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id");
ALTER TABLE "assets" ADD CONSTRAINT "assets_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id");

CREATE TABLE IF NOT EXISTS "positions" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "rack_id" uuid NOT NULL,
  "ru_start" integer NOT NULL,
  "ru_end" integer NOT NULL,
  "asset_id" uuid,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "positions_rack_idx" ON "positions" ("rack_id");
ALTER TABLE "positions" ADD CONSTRAINT "positions_rack_id_racks_id_fk" FOREIGN KEY ("rack_id") REFERENCES "public"."racks"("id");
ALTER TABLE "positions" ADD CONSTRAINT "positions_asset_id_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets"("id");
ALTER TABLE "assets" ADD CONSTRAINT "assets_position_id_positions_id_fk" FOREIGN KEY ("position_id") REFERENCES "public"."positions"("id");

CREATE TABLE IF NOT EXISTS "purchase_orders" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "vendor_id" uuid NOT NULL,
  "line_items" jsonb NOT NULL,
  "status" text DEFAULT 'draft' NOT NULL,
  "approval_stage" text,
  "escrow_terms" text,
  "inspection_site_id" uuid,
  "delivery_site_id" uuid,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "po_vendor_idx" ON "purchase_orders" ("vendor_id");
CREATE INDEX IF NOT EXISTS "po_org_idx" ON "purchase_orders" ("org_id");
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id");
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_inspection_site_id_sites_id_fk" FOREIGN KEY ("inspection_site_id") REFERENCES "public"."sites"("id");
ALTER TABLE "purchase_orders" ADD CONSTRAINT "purchase_orders_delivery_site_id_sites_id_fk" FOREIGN KEY ("delivery_site_id") REFERENCES "public"."sites"("id");

CREATE TABLE IF NOT EXISTS "shipments" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "purchase_order_id" uuid NOT NULL,
  "tracking" text,
  "cartons_pallets" jsonb,
  "customs_docs_refs" jsonb,
  "status" text DEFAULT 'pending' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "shipments" ADD CONSTRAINT "shipments_purchase_order_id_purchase_orders_id_fk" FOREIGN KEY ("purchase_order_id") REFERENCES "public"."purchase_orders"("id");

CREATE TABLE IF NOT EXISTS "asset_components" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "parent_asset_id" uuid NOT NULL,
  "child_asset_id" uuid NOT NULL,
  "role" text NOT NULL,
  "slot_index" integer,
  "created_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "asset_components_parent_idx" ON "asset_components" ("parent_asset_id");
CREATE INDEX IF NOT EXISTS "asset_components_child_idx" ON "asset_components" ("child_asset_id");
ALTER TABLE "asset_components" ADD CONSTRAINT "asset_components_parent_asset_id_assets_id_fk" FOREIGN KEY ("parent_asset_id") REFERENCES "public"."assets"("id");
ALTER TABLE "asset_components" ADD CONSTRAINT "asset_components_child_asset_id_assets_id_fk" FOREIGN KEY ("child_asset_id") REFERENCES "public"."assets"("id");

CREATE TABLE IF NOT EXISTS "provisioning_records" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "asset_id" uuid NOT NULL,
  "os_image" text,
  "hypervisor_node" text,
  "cluster_id" text,
  "metadata" jsonb,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "provisioning_records" ADD CONSTRAINT "provisioning_records_asset_id_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets"("id");

CREATE TABLE IF NOT EXISTS "maintenances" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "asset_id" uuid NOT NULL,
  "type" text NOT NULL,
  "vendor_ticket_ref" text,
  "description" text,
  "status" text DEFAULT 'open' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "maintenances" ADD CONSTRAINT "maintenances_asset_id_assets_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets"("id");

CREATE TABLE IF NOT EXISTS "audit_events" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "org_id" text NOT NULL,
  "actor_id" uuid,
  "actor_email" text,
  "action" text NOT NULL,
  "resource_type" text NOT NULL,
  "resource_id" text NOT NULL,
  "before_state" jsonb,
  "after_state" jsonb,
  "occurred_at" timestamp DEFAULT now() NOT NULL
);

CREATE INDEX IF NOT EXISTS "audit_events_org_idx" ON "audit_events" ("org_id");
CREATE INDEX IF NOT EXISTS "audit_events_resource_idx" ON "audit_events" ("resource_type", "resource_id");
CREATE INDEX IF NOT EXISTS "audit_events_occurred_idx" ON "audit_events" ("occurred_at");
ALTER TABLE "audit_events" ADD CONSTRAINT "audit_events_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id");

CREATE TABLE IF NOT EXISTS "roles" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "name" text NOT NULL UNIQUE,
  "description" text,
  "permissions" jsonb DEFAULT '[]' NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "user_roles" (
  "user_id" uuid NOT NULL,
  "role_id" uuid NOT NULL,
  "scope_site_id" uuid,
  "scope_project_id" text,
  "assigned_at" timestamp DEFAULT now() NOT NULL
);

ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id");
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id");
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_scope_site_id_sites_id_fk" FOREIGN KEY ("scope_site_id") REFERENCES "public"."sites"("id");
ALTER TABLE "user_roles" ADD PRIMARY KEY ("user_id", "role_id");