database.txt

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE users (
    id UUID PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE resumes (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES users(id),
    template_id VARCHAR(100) DEFAULT '1',
    title VARCHAR(100),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE abouts (
    id UUID PRIMARY KEY,
    resume_id UUID REFERENCES resumes(id),
    name VARCHAR(100),
    image TEXT,
    jobtitle VARCHAR(100),
    phone VARCHAR(100),
    location VARCHAR(100),
    email VARCHAR(100),
    linkedin TEXT,
    instagram TEXT,
    facebook TEXT,
    twitter TEXT,
    github TEXT,
    website TEXT,
    about TEXT,
    displayimage BOOLEAN,
    displaymail BOOLEAN,
    displaywebSite BOOLEAN,
    displaylinkedIn BOOLEAN,
    displayinstagram BOOLEAN,
    displayfacebook BOOLEAN,
    displaygithub BOOLEAN,
    displaytwitter BOOLEAN,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE skills (
    id UUID PRIMARY KEY,
    resume_id UUID REFERENCES resumes(id),
    skill_type VARCHAR(50),
    skill VARCHAR(100),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE projects (
    id UUID PRIMARY KEY,
    resume_id UUID REFERENCES resumes(id),
    title VARCHAR(100),
    link TEXT,
    summary TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE education (
    id UUID PRIMARY KEY,
    resume_id UUID REFERENCES resumes(id),
    title VARCHAR(100),
    school VARCHAR(100),
    start_date VARCHAR(20),
    end_date VARCHAR(20),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE experiences (
    id UUID PRIMARY KEY,
    resume_id UUID REFERENCES resumes(id),
    title VARCHAR(100),
    company VARCHAR(100),
    start_date VARCHAR(20),
    end_date VARCHAR(20),
    summary TEXT,
    current BOOLEAN,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);