campusbazaar.sql

DROP TRIGGER IF EXISTS trg_mark_listing_sold        ON transactions;
DROP TRIGGER IF EXISTS trg_prevent_self_purchase     ON transactions;
DROP TRIGGER IF EXISTS trg_prevent_duplicate_purchase ON transactions;
DROP TRIGGER IF EXISTS trg_update_listing_timestamp  ON listings;

DROP FUNCTION IF EXISTS mark_listing_sold();
DROP FUNCTION IF EXISTS prevent_self_purchase();
DROP FUNCTION IF EXISTS prevent_duplicate_purchase();
DROP FUNCTION IF EXISTS update_listing_timestamp();

DROP TABLE IF EXISTS reviews      CASCADE;
DROP TABLE IF EXISTS transactions CASCADE;
DROP TABLE IF EXISTS listings     CASCADE;
DROP TABLE IF EXISTS categories   CASCADE;
DROP TABLE IF EXISTS staff        CASCADE;
DROP TABLE IF EXISTS faculty      CASCADE;
DROP TABLE IF EXISTS students     CASCADE;
DROP TABLE IF EXISTS admins       CASCADE;
DROP TABLE IF EXISTS users        CASCADE;



CREATE TABLE users (
    user_id     SERIAL PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(150)  NOT NULL UNIQUE,
    password    TEXT          NOT NULL,
    profile_pic TEXT,
    role        VARCHAR(20)   NOT NULL
                CHECK (role IN ('student', 'faculty', 'staff')),
    join_date   TIMESTAMP     DEFAULT NOW()
);




CREATE TABLE students (
    user_id          INTEGER       PRIMARY KEY
                     REFERENCES users(user_id) ON DELETE CASCADE,
    university_email VARCHAR(150)  UNIQUE,
    is_verified      BOOLEAN       DEFAULT FALSE,
    degree_program   VARCHAR(100)
);





CREATE TABLE faculty (
    user_id     INTEGER      PRIMARY KEY
                REFERENCES users(user_id) ON DELETE CASCADE,
    department  VARCHAR(100),
    designation VARCHAR(100)
);



CREATE TABLE staff (
    user_id         INTEGER      PRIMARY KEY
                    REFERENCES users(user_id) ON DELETE CASCADE,
    role_title      VARCHAR(100),
    office_location VARCHAR(100)
);



CREATE TABLE admins (
    admin_id         SERIAL       PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    email            VARCHAR(150) NOT NULL UNIQUE,
    password         TEXT         NOT NULL,
    permission_level VARCHAR(50)  DEFAULT 'moderator'
);


CREATE TABLE categories (
    category_id   SERIAL       PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE
);





CREATE TABLE listings (
    listing_id   SERIAL          PRIMARY KEY,
    seller_id    INTEGER         NOT NULL
                 REFERENCES users(user_id) ON DELETE CASCADE,
    category_id  INTEGER
                 REFERENCES categories(category_id) ON DELETE SET NULL,
    title        VARCHAR(200)    NOT NULL,
    description  TEXT,
    price        NUMERIC(10, 2)  NOT NULL CHECK (price >= 0),
    status       VARCHAR(20)     DEFAULT 'active'
                 CHECK (status IN ('active', 'sold', 'inactive')),
    condition    VARCHAR(20)
                 CHECK (condition IN ('new', 'like_new', 'used', 'heavily_used')),
    posted_date  TIMESTAMP       DEFAULT NOW(),
    last_updated TIMESTAMP       DEFAULT NOW(),
    images       TEXT
);


CREATE TABLE transactions (
    transaction_id SERIAL         PRIMARY KEY,
    listing_id     INTEGER        NOT NULL
                   REFERENCES listings(listing_id) ON DELETE CASCADE,
    buyer_id       INTEGER        NOT NULL
                   REFERENCES users(user_id) ON DELETE CASCADE,
    tx_date        TIMESTAMP      DEFAULT NOW(),
    amount         NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
    status         VARCHAR(20)    DEFAULT 'pending'
                   CHECK (status IN ('pending', 'completed', 'cancelled')),
    meetup_point   TEXT
);


CREATE TABLE reviews (
    review_id      SERIAL    PRIMARY KEY,
    transaction_id INTEGER   NOT NULL
                   REFERENCES transactions(transaction_id) ON DELETE CASCADE,
    reviewer_id    INTEGER   NOT NULL
                   REFERENCES users(user_id) ON DELETE CASCADE,
    rating         INTEGER   NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment        TEXT,
    review_date    TIMESTAMP DEFAULT NOW()
);



ALTER TABLE users        DISABLE ROW LEVEL SECURITY;
ALTER TABLE students     DISABLE ROW LEVEL SECURITY;
ALTER TABLE faculty      DISABLE ROW LEVEL SECURITY;
ALTER TABLE staff        DISABLE ROW LEVEL SECURITY;
ALTER TABLE admins       DISABLE ROW LEVEL SECURITY;
ALTER TABLE categories   DISABLE ROW LEVEL SECURITY;
ALTER TABLE listings     DISABLE ROW LEVEL SECURITY;
ALTER TABLE transactions DISABLE ROW LEVEL SECURITY;
ALTER TABLE reviews      DISABLE ROW LEVEL SECURITY;




CREATE OR REPLACE FUNCTION mark_listing_sold()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'completed' THEN
        UPDATE listings
        SET    status = 'sold'
        WHERE  listing_id = NEW.listing_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_mark_listing_sold
    AFTER UPDATE ON transactions
    FOR EACH ROW
    EXECUTE FUNCTION mark_listing_sold();
--to prevent self purchase

CREATE OR REPLACE FUNCTION prevent_self_purchase()
RETURNS TRIGGER AS $$
DECLARE
    listing_seller_id INTEGER;
BEGIN
    SELECT seller_id
    INTO   listing_seller_id
    FROM   listings
    WHERE  listing_id = NEW.listing_id;

    IF listing_seller_id = NEW.buyer_id THEN
        RAISE EXCEPTION 'You cannot buy your own listing!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_self_purchase
    BEFORE INSERT ON transactions
    FOR EACH ROW
    EXECUTE FUNCTION prevent_self_purchase();


CREATE OR REPLACE FUNCTION prevent_duplicate_purchase()
RETURNS TRIGGER AS $$
DECLARE
    current_listing_status VARCHAR(20);
BEGIN
    SELECT status
    INTO   current_listing_status
    FROM   listings
    WHERE  listing_id = NEW.listing_id;

    IF current_listing_status = 'sold' THEN
        RAISE EXCEPTION 'This listing is already sold!';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_duplicate_purchase
    BEFORE INSERT ON transactions
    FOR EACH ROW
    EXECUTE FUNCTION prevent_duplicate_purchase();



CREATE OR REPLACE FUNCTION update_listing_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_listing_timestamp
    BEFORE UPDATE ON listings
    FOR EACH ROW
    EXECUTE FUNCTION update_listing_timestamp();


INSERT INTO categories (category_name) VALUES
    ('Books & Notes'),
    ('Food & Snacks'),
    ('Clothing'),
    ('Utilities'),
    ('Electronics'),
    ('Stationery');


--default users added for testing
INSERT INTO users (name, email, password, role) VALUES
    ('Ali Hassan',  'ali@nuces.edu.pk',   '1234', 'student'),
    ('Sara Khan',   'sara@nuces.edu.pk',  '1234', 'student'),
    ('Dr. Ahmed',   'ahmed@nuces.edu.pk', '1234', 'faculty'),
    ('Usman Bhai',  'usman@nuces.edu.pk', '1234', 'staff');



INSERT INTO students (user_id, university_email, is_verified, degree_program) VALUES
    (1, 'ali-s@campus.edu.pk',  TRUE, 'BS Computer Science'),
    (2, 'sara-s@campus.edu.pk', TRUE, 'BS Software Engineering');


INSERT INTO faculty (user_id, department, designation) VALUES
    (3, 'Computer Science', 'Assistant Professor');



INSERT INTO staff (user_id, role_title, office_location) VALUES
    (4, 'Lab Technician', 'Block B - Room 12');


INSERT INTO admins (name, email, password, permission_level) VALUES
    ('Super Admin', 'admin@campusbazaar.pk', 'admin123', 'superadmin');



INSERT INTO listings (seller_id, category_id, title, description, price, condition, status) VALUES
    (1, 1, 'Data Structures Textbook (CLRS)',
     'Slightly used, all pages intact. Perfect for CS-II course.',
     800.00, 'like_new', 'active'),

    (1, 6, 'Pack of 10 Ballpoint Pens',
     'Blue ink, smooth ballpoint. Brand new pack.',
     150.00, 'new', 'active'),

    (2, 3, 'NUCES University Hoodie XL',
     'Official NUCES hoodie. Worn twice. Very warm.',
     1200.00, 'like_new', 'active'),

    (3, 5, 'Casio fx-991 Scientific Calculator',
     'Perfect working condition. All functions intact.',
     2500.00, 'used', 'active'),

    (2, 1, 'Calculus by Stewart 8th Edition',
     'No highlights. Great for Math-I and Math-II.',
     950.00, 'like_new', 'active'),

    (4, 4, 'Mini USB Desk Fan',
     'Compact and powerful. Perfect for hostel rooms.',
     600.00, 'used', 'active'),

    (1, 2, 'Maggi Instant Noodles x5',
     '5 packs. Expires in 4 months. Midnight hunger solved.',
     200.00, 'new', 'active'),

    (2, 3, 'Lab Coat Size M',
     'White lab coat. Worn 3 times. Required for chemistry lab.',
     450.00, 'like_new', 'active');



SELECT 'users'        AS tbl, COUNT(*) AS rows FROM users
UNION ALL
SELECT 'students',    COUNT(*) FROM students
UNION ALL
SELECT 'faculty',     COUNT(*) FROM faculty
UNION ALL
SELECT 'staff',       COUNT(*) FROM staff
UNION ALL
SELECT 'admins',      COUNT(*) FROM admins
UNION ALL
SELECT 'categories',  COUNT(*) FROM categories
UNION ALL
SELECT 'listings',    COUNT(*) FROM listings
UNION ALL
SELECT 'transactions',COUNT(*) FROM transactions
UNION ALL
SELECT 'reviews',     COUNT(*) FROM reviews;



INSERT INTO transactions (listing_id, buyer_id, amount, status, meetup_point)
VALUES (1, 2, 800.00, 'completed', 'Library Entrance');

SELECT listing_id, title, status
FROM   listings
WHERE  listing_id = 1;


INSERT INTO transactions (listing_id, buyer_id, amount, status, meetup_point)
VALUES (1, 2, 800.00, 'completed', 'Cafeteria');


INSERT INTO transactions (listing_id, buyer_id, amount, status, meetup_point)
VALUES (2, 1, 150.00, 'completed', 'Block A');

UPDATE listings SET price = 750.00 WHERE listing_id = 2;
SELECT listing_id, title, price, last_updated FROM listings WHERE listing_id = 2;

SELECT
    l.listing_id,
    l.title,
    l.price,
    l.status,
    l.condition,
    c.category_name,
    u.name    AS seller_name,
    u.role    AS seller_role
FROM
    listings    l
    JOIN categories c ON l.category_id  = c.category_id
    JOIN users      u ON l.seller_id    = u.user_id
ORDER BY
    l.posted_date DESC;

SELECT
    t.transaction_id,
    t.tx_date,
    t.amount,
    t.status,
    t.meetup_point,
    l.title       AS listing_title,
    u.name        AS buyer_name
FROM
    transactions t
    JOIN listings l ON t.listing_id = l.listing_id
    JOIN users    u ON t.buyer_id   = u.user_id;