DROP TABLE IF EXISTS UserActivity CASCADE;
DROP TABLE IF EXISTS Analytics CASCADE;
DROP TABLE IF EXISTS Subscriptions CASCADE;
DROP TABLE IF EXISTS APIAccess CASCADE;
DROP TABLE IF EXISTS Favorites CASCADE;
DROP TABLE IF EXISTS Feedback CASCADE;
DROP TABLE IF EXISTS News CASCADE;
DROP TABLE IF EXISTS Deals CASCADE;
DROP TABLE IF EXISTS PreparationTimes CASCADE;
DROP TABLE IF EXISTS RestaurantFoodItems CASCADE;
DROP TABLE IF EXISTS FoodItems CASCADE;
DROP TABLE IF EXISTS SocialMedia CASCADE;
DROP TABLE IF EXISTS Locations CASCADE;
DROP TABLE IF EXISTS Companies CASCADE;
DROP TABLE IF EXISTS Users CASCADE;

-- Users table
CREATE TABLE Users (
    UserID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    Email VARCHAR(255) UNIQUE NOT NULL,
    PasswordHash VARCHAR(255) NOT NULL,
    Name VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Companies table
CREATE TABLE Companies (
    CompanyID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    CompanyName VARCHAR(255) NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Locations table
CREATE TABLE Locations (
    LocationID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    CompanyID UUID NOT NULL REFERENCES Companies(CompanyID),
    Address VARCHAR(255),
    City VARCHAR(100),
    State VARCHAR(100),
    ZipCode VARCHAR(20),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- FoodItems table
CREATE TABLE FoodItems (
    FoodItemID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    CompanyID UUID NOT NULL REFERENCES Companies(CompanyID),
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- SocialMedia table
CREATE TABLE SocialMedia (
    SocialMediaPostID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    CompanyID UUID NOT NULL REFERENCES Companies(CompanyID),
    Content TEXT NOT NULL,
    PublishedAt TIMESTAMP,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- PreparationTimes table
CREATE TABLE PreparationTimes (
    PreparationTimeID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    LocationID UUID NOT NULL REFERENCES Locations(LocationID),
    FoodItemID UUID NOT NULL REFERENCES FoodItems(FoodItemID),
    AveragePrepTimeMinutes INT NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Deals table
CREATE TABLE Deals (
    DealID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    CompanyID UUID NOT NULL REFERENCES Companies(CompanyID),
    LocationID UUID NOT NULL REFERENCES Locations(LocationID),
    Description TEXT,
    StartDate TIMESTAMP,
    EndDate TIMESTAMP,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- News table
CREATE TABLE News (
    NewsID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    CompanyID UUID NOT NULL REFERENCES Companies(CompanyID),
    Title VARCHAR(255),
    Content TEXT,
    PublishedAt TIMESTAMP,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Feedback table
CREATE TABLE Feedback (
    FeedbackID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    UserID UUID NOT NULL REFERENCES Users(UserID),
    LocationID UUID REFERENCES Locations(LocationID),
    DealID UUID REFERENCES Deals(DealID),
    FoodItemID UUID REFERENCES FoodItems(FoodItemID),
    Rating INT NOT NULL,
    Comment TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Favorites table
CREATE TABLE Favorites (
    FavoriteID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    UserID UUID NOT NULL REFERENCES Users(UserID),
    LocationID UUID REFERENCES Locations(LocationID),
    DealID UUID REFERENCES Deals(DealID),
    NewsID UUID REFERENCES News(NewsID),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- APIAccess table
CREATE TABLE APIAccess (
    APIAccessID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    UserID UUID NOT NULL REFERENCES Users(UserID),
    Token VARCHAR(255),
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Subscriptions table
CREATE TABLE Subscriptions (
    SubscriptionID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    UserID UUID NOT NULL REFERENCES Users(UserID),
    PlanType VARCHAR(100),
    StartDate TIMESTAMP,
    EndDate TIMESTAMP,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Analytics table
CREATE TABLE Analytics (
    AnalyticsID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    LocationID UUID REFERENCES Locations(LocationID),
    DealID UUID REFERENCES Deals(DealID),
    NewsID UUID REFERENCES News(NewsID),
    Views INT,
    Clicks INT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UserActivity table
CREATE TABLE UserActivity (
    ActivityID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    UserID UUID NOT NULL REFERENCES Users(UserID),
    ActivityType VARCHAR(255) NOT NULL,
    RelatedID UUID,
    Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
