schema.sql

-- 1. Create Independent/Parent Tables

CREATE TABLE Institution (
    institution_id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    subscription_status VARCHAR(50),
    PRIMARY KEY (institution_id)
);

CREATE TABLE Domain (
    domain_id INT(11) NOT NULL AUTO_INCREMENT,
    domain_topic VARCHAR(255),
    semantic_embedding VECTOR(1024),
    PRIMARY KEY (domain_id)
);

CREATE TABLE Venue (
    venue_id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    type VARCHAR(50),
    url VARCHAR(255),
    PRIMARY KEY (venue_id)
);

-- 2. Create Tables with Single Dependencies

CREATE TABLE User (
    user_id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role VARCHAR(50),
    institution_id INT(11),
    semantic_embedding VECTOR(1024),
    PRIMARY KEY (user_id),
    FOREIGN KEY (institution_id) REFERENCES Institution(institution_id)
);

CREATE TABLE Search_Queries (
    query_id INT(11) NOT NULL AUTO_INCREMENT,
    query_text VARCHAR(500) NOT NULL,
    optimized_text TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INT(11),
    type VARCHAR(50),
    semantic_embedding VECTOR(1024),
    expires_at DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 1 YEAR),
    PRIMARY KEY (query_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id)
);

CREATE TABLE Job_Posting (
    job_id INT(11) NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    budget DECIMAL(10,2),
    posted_by VARCHAR(255),
    poster_user_id INT(11),
    domain_id INT(11),
    status VARCHAR(50),
    type VARCHAR(50),
    semantic_embedding VECTOR(1024),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (job_id),
    FOREIGN KEY (domain_id) REFERENCES Domain(domain_id),
    FOREIGN KEY (poster_user_id) REFERENCES User(user_id)
);

CREATE TABLE Job_Application (
    application_id INT(11) NOT NULL AUTO_INCREMENT,
    job_id INT(11) NOT NULL,
    applicant_id INT(11) NOT NULL,
    cover_letter TEXT,
    proposed_budget DECIMAL(10,2),
    status VARCHAR(50) DEFAULT 'pending',
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (application_id),
    FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id),
    FOREIGN KEY (applicant_id) REFERENCES User(user_id)
);

CREATE TABLE Research_Paper (
    paper_id INT(11) NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    abstract TEXT,
    year INT(11),
    citation_count INT(11) DEFAULT 0,
    quality_score DECIMAL(3,2),
    publication_type VARCHAR(50),
    venue_id INT(11),
    domain_id INT(11),
    semantic_embedding VECTOR(1024),
    url VARCHAR(255),
    PRIMARY KEY (paper_id),
    FOREIGN KEY (venue_id) REFERENCES Venue(venue_id),
    FOREIGN KEY (domain_id) REFERENCES Domain(domain_id)
);

-- 3. Create Relationship/Link Tables

CREATE TABLE Authorship (
    user_id INT(11) NOT NULL,
    paper_id INT(11) NOT NULL,
    contribution_order INT(11),
    PRIMARY KEY (user_id, paper_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (paper_id) REFERENCES Research_Paper(paper_id)
);

CREATE TABLE Has_Interest_In (
    user_id INT(11) NOT NULL,
    domain_id INT(11) NOT NULL,
    PRIMARY KEY (user_id, domain_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (domain_id) REFERENCES Domain(domain_id)
);

CREATE TABLE IS_AFFLIATED_WITH (
    user_id INT(11) NOT NULL,
    institution_id INT(11) NOT NULL,
    PRIMARY KEY (user_id, institution_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (institution_id) REFERENCES Institution(institution_id)
);

CREATE TABLE Job_Belongs_To (
    job_id INT(11) NOT NULL,
    domain_id INT(11) NOT NULL,
    PRIMARY KEY (job_id, domain_id),
    FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id),
    FOREIGN KEY (domain_id) REFERENCES Domain(domain_id)
);

CREATE TABLE Jobs_Returned (
    job_id INT(11) NOT NULL,
    query_id INT(11) NOT NULL,
    PRIMARY KEY (job_id, query_id),
    FOREIGN KEY (job_id) REFERENCES Job_Posting(job_id),
    FOREIGN KEY (query_id) REFERENCES Search_Queries(query_id)
);

CREATE TABLE Paper_Belongs_To (
    paper_id INT(11) NOT NULL,
    domain_id INT(11) NOT NULL,
    PRIMARY KEY (paper_id, domain_id),
    FOREIGN KEY (paper_id) REFERENCES Research_Paper(paper_id),
    FOREIGN KEY (domain_id) REFERENCES Domain(domain_id)
);

CREATE TABLE Published_In (
    venue_id INT(11) NOT NULL,
    paper_id INT(11) NOT NULL,
    PRIMARY KEY (venue_id, paper_id),
    FOREIGN KEY (venue_id) REFERENCES Venue(venue_id),
    FOREIGN KEY (paper_id) REFERENCES Research_Paper(paper_id)
);

CREATE TABLE Research_Paper_Returned (
    paper_id INT(11) NOT NULL,
    query_id INT(11) NOT NULL,
    PRIMARY KEY (paper_id, query_id),
    FOREIGN KEY (paper_id) REFERENCES Research_Paper(paper_id),
    FOREIGN KEY (query_id) REFERENCES Search_Queries(query_id)
);

CREATE TABLE Searches (
    user_id INT(11) NOT NULL,
    query_id INT(11) NOT NULL,
    PRIMARY KEY (user_id, query_id),
    FOREIGN KEY (user_id) REFERENCES User(user_id),
    FOREIGN KEY (query_id) REFERENCES Search_Queries(query_id)
);

CREATE TABLE Subscribes_To (
    venue_id INT(11) NOT NULL,
    institution_id INT(11) NOT NULL,
    PRIMARY KEY (venue_id, institution_id),
    FOREIGN KEY (venue_id) REFERENCES Venue(venue_id),
    FOREIGN KEY (institution_id) REFERENCES Institution(institution_id)
);