supabase_context.txt

CREATE TABLE "User" (
    user_id VARCHAR(255) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    address VARCHAR(255),
    fullname VARCHAR(255),
    phone_number VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "User_Credit_Account" (
    user_id VARCHAR(255) PRIMARY KEY,
    num_of_available_gens INT DEFAULT 3,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES "User"(user_id)
);

CREATE TABLE "Credit_Order_History" (
    credit_order_id SERIAL PRIMARY KEY, -- Changed from INT PRIMARY KEY AUTO_INCREMENT
    user_id VARCHAR(255),
    price DECIMAL(10, 2) NOT NULL,
    number_of_generations INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    payment_status VARCHAR(50) DEFAULT 'pending',
    transaction_id VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES "User"(user_id)
);

CREATE TABLE "Generated_Models" (
    generated_model_id SERIAL PRIMARY KEY,
    user_id VARCHAR(255),
    meshy_api_job_id TEXT,
    prompts_and_models_config JSON,
    ordered BOOLEAN DEFAULT FALSE,
    generation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    model_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status generation_status NOT NULL DEFAULT 'IN_PROGRESS',  -- generation_status is an ENUM (IN_PROGESS, COMPLETED, FAILED)
    FOREIGN KEY (user_id) REFERENCES "User"(user_id)
);


CREATE TABLE "Model_Order_History" (
    model_order_id SERIAL PRIMARY KEY, -- Changed from INT PRIMARY KEY AUTO_INCREMENT
    user_id VARCHAR(255),
    generated_model_id INT,
    price DECIMAL(10, 2) NOT NULL,
    packaging_price DECIMAL(10, 2),
    material_price DECIMAL(10, 2) NOT NULL,
    delivery_address VARCHAR(255) NOT NULL,
    order_status VARCHAR(50) DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estimated_delivery_date DATE,
    shipping_tracking_id VARCHAR(255),
    payment_status VARCHAR(50) DEFAULT 'pending',
    transaction_id VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES "User"(user_id),
    FOREIGN KEY (generated_model_id) REFERENCES "Generated_Models"(generated_model_id)
);

CREATE TABLE "Images" (
    image_id SERIAL PRIMARY KEY, -- Changed from INT PRIMARY KEY AUTO_INCREMENT
    user_id VARCHAR(255),
    generated_model_id INT NULL,
    image_data BYTEA NOT NULL, -- Changed MEDIUMBLOB to BYTEA for PostgreSQL
    file_name VARCHAR(255),
    mime_type VARCHAR(50),
    file_size INT,
    metadata TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES "User"(user_id),
    FOREIGN KEY (generated_model_id) REFERENCES "Generated_Models"(generated_model_id)
);

CREATE TABLE "Model_Files" (
    model_file_id SERIAL PRIMARY KEY, -- Changed from INT PRIMARY KEY AUTO_INCREMENT
    user_id VARCHAR(255),
    generated_model_id INT NULL,
    model_data BYTEA NOT NULL, -- Changed MEDIUMBLOB to BYTEA for PostgreSQL
    file_name VARCHAR(255) NOT NULL,
    file_format VARCHAR(10) NOT NULL,
    file_size INT,
    metadata TEXT,
    is_preview_file BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES "User"(user_id),
    FOREIGN KEY (generated_model_id) REFERENCES "Generated_Models"(generated_model_id)
);