-- Ensure globally unique column names when tables are joined

ALTER TABLE "lookup_MethodCode" RENAME "Description" TO "lookup_MethodCode_Description";

-- Enable cascading deletes

CREATE INDEX ON "plotMetadata" (project_id);

ALTER TABLE "plotMetadata" ADD FOREIGN KEY (project_id) REFERENCES projects(project_id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE INDEX ON "plotObservations" ("PlotID");

ALTER TABLE "plotObservations" ADD FOREIGN KEY ("PlotID") REFERENCES "plotMetadata"("PlotID") ON UPDATE CASCADE ON DELETE CASCADE;

CREATE INDEX ON stems ("PlotObsID");

ALTER TABLE stems ADD FOREIGN KEY ("PlotObsID") REFERENCES "plotObservations"("PlotObsID") ON UPDATE CASCADE ON DELETE CASCADE;

-- Remove private data that should not be publicly visible

DELETE FROM "plotMetadata" WHERE "AccessCode" = 1;