CREATE TABLE SpeciesInformation (
    SpeciesID INT PRIMARY KEY,
    CommonName VARCHAR(255),
    ScientificName VARCHAR(255),
    ConservationStatus VARCHAR(50),
    SpeciesType VARCHAR(50),
    GeneralHabitat VARCHAR(255)
);

CREATE TABLE UrbanHabitats (
    HabitatID INT PRIMARY KEY,
    Location VARCHAR(255),
    Type VARCHAR(50),
    Size INT,
    ProximityToWaterSources VARCHAR(255),
    DominantVegetationType VARCHAR(255)
);

CREATE TABLE WildlifeObservations (
    ObservationID INT PRIMARY KEY,
    SpeciesID INT,
    HabitatID INT,
    Date DATE,
    Time TIME,
    ObservedBehavior VARCHAR(255),
    FOREIGN KEY (SpeciesID) REFERENCES SpeciesInformation(SpeciesID),
    FOREIGN KEY (HabitatID) REFERENCES UrbanHabitats(HabitatID)
);

CREATE TABLE UrbanDevelopmentProjects (
    ProjectID INT PRIMARY KEY,
    Location VARCHAR(255),
    StartDate DATE,
    EndDate DATE,
    ProjectType VARCHAR(50),
    ImpactAssessmentScore VARCHAR(50)
);

CREATE TABLE FeedingHabits (
    FeedingHabitID INT PRIMARY KEY,
    SpeciesID INT,
    PrimaryFoodSources VARCHAR(255),
    FeedingTimes VARCHAR(50),
    AdaptationsToUrbanFoodSources VARCHAR(255),
    InteractionWithHumanFoodWaste VARCHAR(50),
    FOREIGN KEY (SpeciesID) REFERENCES SpeciesInformation(SpeciesID)
);

CREATE TABLE NestingBreedingSites (
    SiteID INT PRIMARY KEY,
    SpeciesID INT,
    LocationType VARCHAR(50),
    SpecificLocation VARCHAR(255),
    BreedingSeason VARCHAR(50),
    AverageOffspringPerSeason INT,
    FOREIGN KEY (SpeciesID) REFERENCES SpeciesInformation(SpeciesID)
);

CREATE TABLE WildlifeCorridors (
    CorridorID INT PRIMARY KEY,
    StartLocation VARCHAR(255),
    EndLocation VARCHAR(255),
    Length INT,
    PrimarySpeciesUsingCorridor VARCHAR(255),
    CorridorType VARCHAR(50)
);

CREATE TABLE HumanWildlifeInteractions (
    InteractionID INT PRIMARY KEY,
    SpeciesID INT,
    Description VARCHAR(255),
    Frequency VARCHAR(50),
    Outcome VARCHAR(50),
    MitigationMeasures VARCHAR(255),
    FOREIGN KEY (SpeciesID) REFERENCES SpeciesInformation(SpeciesID)
);

CREATE TABLE ConservationInitiatives (
    InitiativeID INT PRIMARY KEY,
    Title VARCHAR(255),
    StartDate DATE,
    EndDate DATE,
    TargetSpecies INT,
    OutcomeMeasures VARCHAR(255),
    FOREIGN KEY (TargetSpecies) REFERENCES SpeciesInformation(SpeciesID)
);

CREATE TABLE UrbanEcologyResearch (
    ResearchID INT PRIMARY KEY,
    Title VARCHAR(255),
    LeadResearcher VARCHAR(255),
    Institution VARCHAR(255),
    PublicationDate DATE,
    KeyFindings TEXT
);

INSERT INTO SpeciesInformation (SpeciesID, CommonName, ScientificName, ConservationStatus, SpeciesType, GeneralHabitat) VALUES
(1, 'Urban Red Fox', 'Vulpes vulpes', 'Least Concern', 'Mammal', 'Urban and Suburban Areas'),
(2, 'Peregrine Falcon', 'Falco peregrinus', 'Least Concern', 'Bird', 'High-rise Buildings/Urban Cliffs'),
(3, 'Raccoon', 'Procyon lotor', 'Least Concern', 'Mammal', 'Wooded Areas near Urban'),
(4, 'European Starling', 'Sturnus vulgaris', 'Least Concern', 'Bird', 'Urban Areas'),
(5, 'Rock Pigeon', 'Columba livia', 'Least Concern', 'Bird', 'Urban Areas'),
(6, 'American Robin', 'Turdus migratorius', 'Least Concern', 'Bird', 'Urban Gardens'),
(7, 'Eastern Gray Squirrel', 'Sciurus carolinensis', 'Least Concern', 'Mammal', 'Urban Parks'),
(8, 'Common Rat', 'Rattus norvegicus', 'Least Concern', 'Mammal', 'Urban Areas'),
(9, 'House Sparrow', 'Passer domesticus', 'Least Concern', 'Bird', 'Urban Areas'),
(10, 'Monarch Butterfly', 'Danaus plexippus', 'Endangered', 'Insect', 'Urban Gardens');

INSERT INTO UrbanHabitats (HabitatID, Location, Type, Size, ProximityToWaterSources, DominantVegetationType) VALUES
(1, 'Central Park', 'Park', 843, 'Lakes within park', 'Deciduous Forest'),
(2, 'Downtown Rooftops', 'Rooftop Gardens', 0, 'Artificial', 'Cultivated Plants'),
(3, 'Riverside Park', 'Park', 400, 'River adjacent', 'Grassland'),
(4, 'Urban Backyards', 'Private Gardens', 0, 'Man-made ponds', 'Mixed'),
(5, 'City Square', 'Urban Square', 10, 'Fountains', 'Ornamental Trees'),
(6, 'Railway Sidings', 'Linear Habitats', 5, 'None', 'Shrubs'),
(7, 'Suburban Woodlands', 'Woodland', 500, 'Streams', 'Deciduous Trees'),
(8, 'Industrial Areas', 'Wasteland', 300, 'Puddles', 'Grass and Bushes'),
(9, 'Public Library Gardens', 'Public Gardens', 2, 'Man-made ponds', 'Flowering Plants'),
(10, 'University Campus', 'Educational Institutions', 150, 'Artificial Lakes', 'Lawns and Trees');

INSERT INTO WildlifeObservations (ObservationID, SpeciesID, HabitatID, Date, Time, ObservedBehavior) VALUES
(1, 1, 1, '2024-04-07', '18:00:00', 'Foraging'),
(2, 2, 2, '2024-04-08', '09:00:00', 'Nesting'),
(3, 3, 1, '2024-04-09', '20:00:00', 'Exploring Trash'),
(4, 4, 3, '2024-04-10', '13:00:00', 'Flocking'),
(5, 5, 5, '2024-04-11', '12:00:00', 'Feeding on Seeds'),
(6, 6, 4, '2024-04-12', '04:00:00', 'Worm Hunting'),
(7, 7, 7, '2024-04-13', '10:00:00', 'Tree Climbing'),
(8, 8, 8, '2024-04-14', '22:00:00', 'Scavenging'),
(9, 9, 9, '2024-04-15', '11:00:00', 'Bathing in Pond'),
(10, 10, 4, '2024-04-16', '14:00:00', 'Pollinating Flowers'),
(11, 1, 3, '2024-04-16', '4:00:00', 'Bathing');

INSERT INTO UrbanDevelopmentProjects (ProjectID, Location, StartDate, EndDate, ProjectType, ImpactAssessmentScore) VALUES
(1, 'Near Central Park', '2023-01-01', '2024-12-31', 'Residential', 'Medium'),
(2, 'Downtown', '2024-01-01', '2025-12-31', 'Commercial', 'High'),
(3, 'Suburban Expansion East', '2023-05-15', '2024-08-20', 'Residential', 'Low'),
(4, 'Old Town Revitalization', '2023-06-01', '2025-07-15', 'Mixed Use', 'Medium'),
(5, 'Riverside Development', '2024-02-20', '2026-12-31', 'Residential', 'High'),
(6, 'Tech Park', '2024-04-10', '2027-01-30', 'Commercial', 'Medium'),
(7, 'Greenway Expansion', '2023-03-12', '2024-09-05', 'Park', 'Low'),
(8, 'Waterfront Leisure Area', '2024-05-25', '2025-03-30', 'Leisure', 'Medium'),
(9, 'Urban Farming Initiative', '2023-08-01', '2024-11-15', 'Agricultural', 'Low'),
(10, 'City Center Pedestrianization', '2023-09-01', '2024-05-20', 'Infrastructure', 'High');

INSERT INTO FeedingHabits (FeedingHabitID, SpeciesID, PrimaryFoodSources, FeedingTimes, AdaptationsToUrbanFoodSources, InteractionWithHumanFoodWaste) VALUES
(1, 1, 'Rodents, Insects, Berries', 'Dusk to Dawn', 'Opportunistic Feeding', 'High'),
(2, 2, 'Birds, Rodents', 'Dawn', 'High Altitude Hunting', 'Low'),
(3, 3, 'Garbage, Plants, Small Animals', 'Night', 'Foraging in Trash', 'High'),
(4, 4, 'Insects, Fruits', 'Day', 'Flocking in Groups', 'Medium'),
(5, 5, 'Seeds, Food Scraps', 'All Day', 'Feeding in Crowds', 'High'),
(6, 6, 'Worms, Insects, Berries', 'Morning', 'Ground Foraging', 'Medium'),
(7, 7, 'Nuts, Seeds, Insects', 'Day', 'Tree Foraging', 'Low'),
(8, 8, 'Garbage, Stored Foods', 'Night', 'Nest Building in Buildings', 'High'),
(9, 9, 'Seeds, Insects', 'All Day', 'Urban Nesting', 'High'),
(10, 10, 'Nectar, Pollen', 'Day', 'Migration', 'Low');

INSERT INTO NestingBreedingSites (SiteID, SpeciesID, LocationType, SpecificLocation, BreedingSeason, AverageOffspringPerSeason) VALUES
(1, 1, 'Natural', 'Underbrush in Parks', 'Spring', 4),
(2, 2, 'Man-made', 'Rooftop Ledges', 'Late Spring', 3),
(3, 3, 'Man-made', 'Abandoned Buildings', 'All Year', 5),
(4, 4, 'Man-made', 'Building Vents', 'Spring', 5),
(5, 5, 'Man-made', 'Building Ledges', 'All Year', 2),
(6, 6, 'Natural', 'Trees and Shrubs', 'Spring', 3),
(7, 7, 'Natural', 'Trees', 'All Year', 2),
(8, 8, 'Man-made', 'Underground Burrows', 'Spring', 6),
(9, 9, 'Man-made', 'Urban Trees and Bushes', 'All Year', 3),
(10, 10, 'Natural', 'Flowering Plants', 'Summer', 'Varies');

INSERT INTO WildlifeCorridors (CorridorID, StartLocation, EndLocation, Length, PrimarySpeciesUsingCorridor, CorridorType) VALUES
(1, 'Central Park', 'Riverside Park', 2, 'Urban Red Fox', 'Greenway'),
(2, 'Suburban Areas', 'Central Park', 5, 'Various Bird Species', 'Flight Path'),
(3, 'East Woodlands', 'West Woodlands', 3, 'Eastern Gray Squirrel', 'Tree Line'),
(4, 'City North', 'City South', 4, 'Raccoon', 'Stream Path'),
(5, 'Downtown', 'City Square', 1, 'Rock Pigeon', 'Urban Street'),
(6, 'Suburbs', 'Industrial Areas', 6, 'Common Rat', 'Sewer System'),
(7, 'University Campus', 'Public Library Gardens', 1.5, 'Monarch Butterfly', 'Floral Path'),
(8, 'Old Town', 'Tech Park', 2, 'European Starling', 'Open Air'),
(9, 'Riverside Development', 'Riverside Park', 3, 'American Robin', 'Riverside Path'),
(10, 'Greenway Expansion', 'Central Park', 4, 'Various Insect Species', 'Greenway');

INSERT INTO HumanWildlifeInteractions (InteractionID, SpeciesID, Description, Frequency, Outcome, MitigationMeasures) VALUES
(1, 1, 'Foxes searching trash cans for food', 'Nightly', 'Neutral', 'Secure Trash Cans'),
(2, 2, 'Falcons diving in populated areas', 'Rare', 'Positive (Pest Control)', 'Awareness Campaigns'),
(3, 3, 'Raccoons overturning garbage bins', 'Frequently', 'Negative', 'Secure Garbage Bins'),
(4, 4, 'Starlings nesting in building vents', 'Common', 'Negative', 'Vent Covers'),
(5, 5, 'Pigeons roosting on building ledges', 'Daily', 'Neutral', 'Bird Spikes'),
(6, 6, 'Robins eating from bird feeders', 'Frequently', 'Positive', 'Provide Bird Feeders'),
(7, 7, 'Squirrels raiding bird feeders', 'Daily', 'Neutral', 'Squirrel-proof Feeders'),
(8, 8, 'Rats scavenging food waste', 'Nightly', 'Negative', 'Improved Waste Management'),
(9, 9, 'Sparrows bathing in public fountains', 'Common', 'Neutral', 'Maintain Clean Water'),
(10, 10, 'Butterflies pollinating urban gardens', 'Seasonal', 'Positive', 'Plant Native Flowers');

INSERT INTO ConservationInitiatives (InitiativeID, Title, StartDate, EndDate, TargetSpecies, OutcomeMeasures) VALUES
(1, 'Urban Fox Conservation', '2023-01-01', 'Indefinite', 1, 'Population Stability, Reduced Negative Interactions'),
(2, 'High-rise Habitat Creation', '2024-05-01', 'Indefinite', 2, 'Increased Nesting Sites, Population Support'),
(3, 'Raccoon Management Program', '2023-02-15', '2025-12-31', 3, 'Reduce Negative Interactions, Educate Public'),
(4, 'Starling Population Control', '2023-03-01', '2024-08-30', 4, 'Control Population Growth'),
(5, 'Pigeon Roosting Mitigation', '2023-04-20', 'Indefinite', 5, 'Reduce Public Nuisance, Health Risks'),
(6, 'Native Bird Feeding Awareness', '2024-06-10', 'Indefinite', 6, 'Promote Bird Conservation, Enhance Biodiversity'),
(7, 'Squirrel Habitat Enhancement', '2023-07-05', '2026-05-15', 7, 'Increase Natural Food Sources, Habitat Areas'),
(8, 'Urban Rat Control Initiative', '2023-08-25', '2025-02-20', 8, 'Reduce Population, Disease Risk'),
(9, 'Urban Bird Sanctuary Zones', '2024-01-15', 'Indefinite', 9, 'Protect Bird Populations, Increase Green Spaces'),
(10, 'Monarch Butterfly Waystations', '2024-03-30', 'Indefinite', 10, 'Create Migration Stopovers, Increase Population');

INSERT INTO UrbanEcologyResearch (ResearchID, Title, LeadResearcher, Institution, PublicationDate, KeyFindings) VALUES
(1, 'The Impact of Urbanization on Red Fox Behaviors', 'Dr. Jane Doe', 'Urban Wildlife Institute', '2023-12-15', 'Adaptability and Increased Human Interaction'),
(2, 'Peregrine Falcons: Urban Predators', 'Dr. John Smith', 'City University Ecology Dept.', '2024-03-22', 'Successful Adaptation to Urban Environments, Role in Pest Control'),
(3, 'Raccoon Urban Survival Strategies', 'Dr. Emily White', 'Institute for Urban Ecology', '2023-11-05', 'Use of Urban Features for Foraging and Shelter'),
(4, 'Starlings and Urban Noise Pollution', 'Dr. Michael Brown', 'Urban Environmental Research Center', '2024-02-28', 'Changes in Communication Behaviors'),
(5, 'The Effects of Urban Landscapes on Pigeon Populations', 'Dr. Laura Green', 'City Wildlife Study Group', '2023-09-17', 'Population Dynamics in Urban Centers'),
(6, 'Feeding Habits of Urban Robins', 'Dr. Henry Adams', 'Urban Birdwatching Society', '2024-04-12', 'Impact of Human-Provided Food Sources'),
(7, 'Squirrel Navigational Skills in Urban Parks', 'Dr. Olivia Johnson', 'National Wildlife Research Institute', '2023-10-29', 'Spatial Memory and Resource Locations'),
(8, 'Urban Rat Populations and Public Health', 'Dr. Alex Martinez', 'Public Health and Ecology Lab', '2024-01-07', 'Correlation Between Waste Management and Rat Populations'),
(9, 'Sparrows: Adapting to Urban Life', 'Dr. Isabella Torres', 'Urban Ecology Innovations', '2023-08-15', 'Nesting and Feeding Adaptations'),
(10, 'Conservation Efforts for Urban Monarch Habitats', 'Dr. Nathan Chung', 'Butterfly Conservation Network', '2024-05-06', 'Creating Urban Waystations for Migratory Butterflies');

-- Find the nocturnal species and their most common urban habitats. Night can be defined as between 20:00:00 and 06:00:00.
-- Display the species name, habitat, percentage of their actvities observed in the night.
WITH NocturnalObservations AS (
    SELECT
        wo.SpeciesID,
        wo.HabitatID,
        COUNT(*) AS NocturnalCount
    FROM WildlifeObservations wo
    WHERE 
        wo.Time > '20:00:00' OR wo.Time < '06:00:00'
    GROUP BY wo.SpeciesID, wo.HabitatID
),
TotalObservations AS (
    SELECT
        SpeciesID,
        COUNT(*) AS TotalCount
    FROM WildlifeObservations
    GROUP BY SpeciesID
),
NocturnalPercentage AS (
    SELECT
        no.SpeciesID,
        (SUM(no.NocturnalCount) * 100.0 / SUM(obs.TotalCount)) AS NocturnalActivityPercentage
    FROM NocturnalObservations no
    INNER JOIN TotalObservations obs ON no.SpeciesID = obs.SpeciesID
    GROUP BY no.SpeciesID
),
MaxNocturnalHabitat AS (
    SELECT
        no.SpeciesID,
        no.HabitatID,
        ROW_NUMBER() OVER (PARTITION BY no.SpeciesID ORDER BY NocturnalCount DESC) AS Rank
    FROM NocturnalObservations no
)
SELECT
    si.CommonName AS Species,
    uh.Location AS MostCommonNocturnalHabitat,
    np.NocturnalActivityPercentage
FROM NocturnalPercentage np
INNER JOIN MaxNocturnalHabitat mnh ON np.SpeciesID = mnh.SpeciesID AND mnh.Rank = 1
INNER JOIN SpeciesInformation si ON si.SpeciesID = np.SpeciesID
INNER JOIN UrbanHabitats uh ON uh.HabitatID = mnh.HabitatID
WHERE mnh.Rank = 1
ORDER BY np.NocturnalActivityPercentage DESC;

-- WITH InitiativeImpact AS (
--     SELECT
--         ci.InitiativeID,
--         ci.Title,
--         ci.TargetSpecies,
--         ci.StartDate,
--         ci.EndDate,
--         COUNT(wo.ObservationID) AS ObservationCount,
--         SUM(CASE WHEN wo.Date BETWEEN ci.StartDate AND ci.EndDate THEN 1 ELSE 0 END) AS ObservationsDuringInitiative
--     FROM ConservationInitiatives ci
--     JOIN WildlifeObservations wo ON ci.TargetSpecies = wo.SpeciesID
--     GROUP BY ci.InitiativeID
-- )
-- SELECT
--     si.CommonName,
--     ci.Title AS InitiativeTitle,
--     ii.ObservationCount AS TotalObservations,
--     ii.ObservationsDuringInitiative,
--     (ii.ObservationsDuringInitiative * 100.0 / ii.ObservationCount) AS PercentageDuringInitiative
-- FROM InitiativeImpact ii
-- JOIN SpeciesInformation si ON ii.TargetSpecies = si.SpeciesID
-- JOIN ConservationInitiatives ci ON ii.InitiativeID = ci.InitiativeID
-- ORDER BY PercentageDuringInitiative DESC, TotalObservations DESC;