externals/databases/_pg_tables.py

# CREATE TABLES 
query_create_cv_raw = """
CREATE TABLE cv_raw (
    filename TEXT NOT NULL UNIQUE,
    profile_id SERIAL PRIMARY KEY,
    file_content BYTEA NOT NULL,
    is_extracted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
"""

alter_table_cv_raw_add_is_extracted = """
ALTER TABLE cv_raw
ADD COLUMN is_extracted BOOLEAN DEFAULT FALSE;"""

query_create_cv_profile_extracted = """
CREATE TABLE cv_profile_extracted (
    fullname VARCHAR(100) NOT NULL,
    profile_id SERIAL PRIMARY KEY,
    
    gpa_edu_1 DECIMAL(4,2),
    gpa_edu_2 DECIMAL(4,2),
    gpa_edu_3 DECIMAL(4,2),

    univ_edu_1 varchar(100),
    univ_edu_2 varchar(100),
    univ_edu_3 varchar(100),

    major_edu_1 varchar(100),
    major_edu_2 varchar(100),
    major_edu_3 varchar(100),
    
    domicile TEXT,
    yoe DECIMAL(3,1),
    hardskills TEXT,
    softskills TEXT,
    certifications TEXT,
    business_domain TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_profile_extracted
        FOREIGN KEY (profile_id)
        REFERENCES cv_raw (profile_id)
        ON DELETE CASCADE
);
"""

query_create_cv_profile_norm = """
CREATE TABLE cv_profile_norm (
    fullname VARCHAR(100) NOT NULL,
    profile_id SERIAL PRIMARY KEY,
    
    gpa_edu_1 DECIMAL(4,2),
    gpa_edu_2 DECIMAL(4,2),
    gpa_edu_3 DECIMAL(4,2),

    univ_edu_1 varchar(100),
    univ_edu_2 varchar(100),
    univ_edu_3 varchar(100),

    major_edu_1 varchar(100),
    major_edu_2 varchar(100),
    major_edu_3 varchar(100),
    
    domicile TEXT,
    yoe DECIMAL(3,1),
    hardskills TEXT,
    softskills TEXT,
    certifications TEXT,
    business_domain TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_profile_norm
        FOREIGN KEY (profile_id)
        REFERENCES cv_profile_extracted (profile_id)
        ON DELETE CASCADE
);
"""

# DROP TABLES
query_drop_cv_raw = "drop table cv_raw cascade"
query_drop_cv_profile_extracted = "drop table cv_profile_extracted cascade"
query_drop_cv_profile_norm = "drop table cv_profile_norm cascade"


# OPERATIONS
q_creates = [query_create_cv_raw, query_create_cv_profile_extracted, query_create_cv_profile_norm]
q_drops = [
    # query_drop_cv_raw, 
    query_drop_cv_profile_extracted, query_drop_cv_profile_norm]

from externals.databases._pgdb import execute_query

async def get_create_queries():
    for q in q_creates:
        await execute_query(q)

async def get_drop_queries():
    for q in q_drops:
        await execute_query(q)


if __name__ == "__main__":
    import asyncio
    # asyncio.run(get_drop_queries())
    asyncio.run(get_create_queries())