-- Drop existing tables if they exist
DROP TABLE IF EXISTS InspectionInfraction;
DROP TABLE IF EXISTS Infraction;
DROP TABLE IF EXISTS Inspection;
DROP TABLE IF EXISTS Establishment;
DROP TABLE IF EXISTS EstablishmentType;

-- Create EstablishmentType table
CREATE TABLE EstablishmentType (
    TypeId INT IDENTITY(1,1) PRIMARY KEY,
    TypeName VARCHAR(100)
);

-- Create Establishment table
CREATE TABLE Establishment (
    EstablishmentId INT PRIMARY KEY,
    Name VARCHAR(255),
    TypeId INT,
    StreetAddress VARCHAR(255),
    UnitNumber VARCHAR(50),
    RawAddress VARCHAR(255),
    City VARCHAR(100),
    PostalCode VARCHAR(20),
    Status VARCHAR(50),
    Latitude FLOAT,
    Longitude FLOAT
);

-- Create Inspection table
CREATE TABLE Inspection (
    InspectionId INT PRIMARY KEY,
    EstablishmentId INT,
    InspectionDate DATE,
    OutcomeType VARCHAR(100),
    OutcomeDetail VARCHAR(100),
    AmountFined DECIMAL(10, 2)
);

-- Create Infraction table
CREATE TABLE Infraction (
    InfractionId INT IDENTITY(1,1) PRIMARY KEY,
    InfractionText TEXT,
    InfractionCode VARCHAR(50)
);

-- Create InspectionInfraction table
CREATE TABLE InspectionInfraction (
    UniqueId VARCHAR(100) PRIMARY KEY,
    InspectionId INT,
    InfractionId INT,
    SeverityCode VARCHAR(10),
    SeverityLabel VARCHAR(50),
    Action VARCHAR(255)
);

-- Add foreign key constraints
ALTER TABLE Establishment
ADD CONSTRAINT FK_Establishment_Type
FOREIGN KEY (TypeId)
REFERENCES EstablishmentType(TypeId);

ALTER TABLE Inspection
ADD CONSTRAINT FK_Inspection_Establishment
FOREIGN KEY (EstablishmentId)
REFERENCES Establishment(EstablishmentId);

ALTER TABLE InspectionInfraction
ADD CONSTRAINT FK_InspectionInfraction_Inspection
FOREIGN KEY (InspectionId)
REFERENCES Inspection(InspectionId);

ALTER TABLE InspectionInfraction
ADD CONSTRAINT FK_InspectionInfraction_Infraction
FOREIGN KEY (InfractionId)
REFERENCES Infraction(InfractionId);
