RelationModel: kreiranje_v3.sql

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    birth_date DATE NOT NULL,
    gender VARCHAR(10) NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    address_street VARCHAR(100) NOT NULL,
    address_number INT,
    address_city VARCHAR(50) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('donor','volunteer','medical','institution','admin'))
);


CREATE TABLE donation_types (
    type_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);


CREATE TABLE donation_actions (
    action_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('physical','online')),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created_by INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE

);


CREATE TABLE physical_actions (
    action_id INT PRIMARY KEY REFERENCES donation_actions(action_id) ON DELETE CASCADE,
    location VARCHAR(100) NOT NULL,
    needed_people INT NOT NULL
);

-- Online Actions
CREATE TABLE online_actions (
    action_id INT PRIMARY KEY REFERENCES donation_actions(action_id) ON DELETE CASCADE,
    bank_account VARCHAR(50) NOT NULL
);


CREATE TABLE participation (
    participation_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    action_id INT NOT NULL REFERENCES donation_actions(action_id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL,
    UNIQUE(user_id, action_id)
);


CREATE TABLE donations (
    donation_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(user_id),
    action_id INT NOT NULL REFERENCES donation_actions(action_id),
    type_id INT NOT NULL REFERENCES donation_types(type_id),
    quantity INT,
    amount DECIMAL(10,2),
    donated_at TIMESTAMP DEFAULT NOW()
);


CREATE TABLE action_requests (
    request_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(user_id),
    title VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('physical','online')),
    created_at DATE NOT NULL DEFAULT CURRENT_DATE,
    approved_at DATE,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected'))
);


CREATE TABLE sponsors (
    sponsor_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    address_street VARCHAR(100),
    address_number INT,
    address_city VARCHAR(50)
);