Closed bpintel closed 11 months ago
CREATE TABLE IF NOT EXISTS program ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, program_name VARCHAR(25), stream_name VARCHAR(25), personnel_lead INTEGER REFERENCES personnel, funding_agency INTEGER REFERENCES agency, efficiency_protocols_document_link VARCHAR(200), trapping_protocols_document_link VARCHAR(200), created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() );
CREATE TABLE IF NOT EXISTS trap_locations ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, trap_name VARCHAR(50), program_id INTEGER REFERENCES program, data_recorder_id INTEGER REFERENCES personnel, data_recorder_agency_id INTEGER REFERENCES agency, site_name VARCHAR(50), cone_size_ft NUMERIC, x_coord NUMERIC, y_coord NUMERIC, coordinate_system VARCHAR(100), projection VARCHAR(100), datum VARCHAR(100), gage_number NUMERIC, gage_agency INTEGER REFERENCES agency, comments VARCHAR(500), created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() );
CREATE TABLE IF NOT EXISTS release_site ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, trap_locations_id INTEGER REFERENCES trap_locations, release_site_name VARCHAR(50), release_site_x_coord NUMERIC, release_site_y_coord NUMERIC, release_site_coordinate_system VARCHAR(50), release_site_datum VARCHAR(50), release_site_projection VARCHAR(50) );
CREATE TABLE IF NOT EXISTS program_personnel_team ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, personnel_id INTEGER REFERENCES personnel, program_id INTEGER REFERENCES program );
CREATE TABLE IF NOT EXISTS personnel ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, first_name VARCHAR(25), last_name VARCHAR(25), email VARCHAR(50), phone VARCHAR(12), agency_id INTEGER REFERENCES agency, role role_enum, orcid_id VARCHAR(25), created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() );
CREATE TABLE IF NOT EXISTS hatchery_info ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, hatchery_name VARCHAR(25), stream_name VARCHAR(25), agreement_id VARCHAR(25), program_id INTEGER REFERENCES program, aggrement_start_date DATE, aggrement_end_date DATE, renewal_date DATE, frequency_of_fish_collection INTEGER REFERENCES frequency, quantity_of_fish NUMERIC, hatchery_file_link VARCHAR(200) );
CREATE TABLE IF NOT EXISTS fish_measure_protocol ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, program_id INTEGER REFERENCES program, species VARCHAR(10) REFERENCES taxon (code), life_stage INTEGER REFERENCES life_stage, run INTEGER REFERENCES run, number_measured NUMERIC );
CREATE TABLE IF NOT EXISTS permit_info ( id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY, permit_id VARCHAR(25), program_id INTEGER REFERENCES program, stream_name VARCHAR(25), permit_start_date DATE, permit_end_date DATE, flow_threshold NUMERIC, temperature_threshold NUMERIC, frequency_sampling_inclement_weather NUMERIC, species VARCHAR(10) REFERENCES taxon (code), listing_unit INTEGER REFERENCES listing_unit, fish_life_stage fish_life_stage_enum, allowed_expected_take NUMERIC, allowed_mortality_count NUMERIC, permit_file_link VARCHAR(200) );
make POST requests to the following tables:
[x] program
[x] trap_locations
[x] release_site
[x] program_personnel
[x] personnel
[x] Permit_information
Add fundingAgency and listingUnitOrStock to the dropdownValues response
Finalize the create new program post bundler (client)
update all models