-- Restore original table names

ALTER TABLE "PlotMetadata" RENAME TO "plotMetadata";

ALTER TABLE "PlotObservations" RENAME TO "plotObservations";

-- Delete rows that do not satisfy foreign key constraints

-- these must happen in order starting with the outermost table

DELETE FROM "plotObservations" WHERE "PlotID" IN (

	SELECT "plotObservations"."PlotID"

	FROM "plotObservations"

	LEFT JOIN "plotMetadata" ON "plotMetadata"."PlotID" = "plotObservations"."PlotID"

	WHERE "plotMetadata"."PlotID" IS NULL

);

DELETE FROM stems WHERE "plotobs_id" IN (

	SELECT stems."plotobs_id"

	FROM stems

	LEFT JOIN "plotObservations" ON "plotObservations"."PlotObsID" = stems."plotobs_id"

	WHERE "plotObservations"."PlotObsID" IS NULL

);

-- 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 ("plotobs_id");

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

-- Remove private data that should not be publicly visible

/*

"select * from lookup_plot_security_levels;

+-------+---------------------------+

| level | description               |

+-------+---------------------------+

|     1 | No Access                 |

|     2 | Can view plot metadata    |

|     3 | Can download plot details |

|     4 | Can assign access to plot |

+-------+---------------------------+

" (http://vegpath.org/fs/inputs/SALVIAS/_src/salvias_data_access_controls.txt)

*/

DELETE FROM "plotMetadata" WHERE "AccessCode" < 3;

-- Ensure globally unique column names when tables are joined

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