
-- Create User table
CREATE TABLE User (
    UserID SERIAL PRIMARY KEY,
    Username VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    Password VARCHAR(255) NOT NULL,
    Contact_Details VARCHAR(255) NOT NULL
);

-- Create Social Media Profile table
CREATE TABLE SocialMediaProfile (
    ProfileID SERIAL PRIMARY KEY,
    Platform VARCHAR(255) NOT NULL,
    User_Name VARCHAR(255) NOT NULL,
    Account_Type VARCHAR(50) NOT NULL,
    Followers_Count INT NOT NULL,
    UserID INT NOT NULL,
    FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE
);

-- Create Post table
CREATE TABLE Post (
    PostID SERIAL PRIMARY KEY,
    Content TEXT,
    Post_Date TIMESTAMP,
    URL VARCHAR(255) NOT NULL,
    Likes_Count INT NOT NULL,
    Comments_Count INT NOT NULL,
    ProfileID INT NOT NULL,
    FOREIGN KEY (ProfileID) REFERENCES SocialMediaProfile(ProfileID) ON DELETE CASCADE
);

-- Create Profile Marketing Request table
CREATE TABLE ProfileMarketingRequest (
    RequestID SERIAL PRIMARY KEY,
    Target_Followers INT NOT NULL,
    Timeline TIMESTAMP NOT NULL,
    ProfileID INT NOT NULL UNIQUE,
    Date_Created TIMESTAMP NOT NULL,
    Status VARCHAR(50) NOT NULL,
    UserID INT NOT NULL,
    ProviderID INT,
    FOREIGN KEY (ProfileID) REFERENCES SocialMediaProfile(ProfileID) ON DELETE CASCADE,
    FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE,
    FOREIGN KEY (ProviderID) REFERENCES ServiceProvider(ProviderID) ON DELETE SET NULL
);

-- Create Post Marketing Request table
CREATE TABLE PostMarketingRequest (
    RequestID SERIAL PRIMARY KEY,
    Target_Likes INT NOT NULL,
    Target_Comments INT NOT NULL,
    Timeline TIMESTAMP NOT NULL,
    PostID INT NOT NULL UNIQUE,
    Date_Created TIMESTAMP NOT NULL,
    Status VARCHAR(50) NOT NULL,
    UserID INT NOT NULL,
    ProviderID INT,
    FOREIGN KEY (PostID) REFERENCES Post(PostID) ON DELETE CASCADE,
    FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE,
    FOREIGN KEY (ProviderID) REFERENCES ServiceProvider(ProviderID) ON DELETE SET NULL
);

-- Create Service Provider table
CREATE TABLE ServiceProvider (
    ProviderID SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Availability_Status BOOLEAN NOT NULL,
    Pricing FLOAT NOT NULL,
    Services_Offered TEXT NOT NULL,
    Contact_Email VARCHAR(255) NOT NULL
);
