-assets
-uploads
-public
-pages
 --admins
    --dean admean
    --assistant admin
    --finance admin
 --users
 
  <!--<div class="align-title-searchBox table__header">-->
    <!--    <h2>ئاگادارکردنەوەکان</h2>-->
    <!--    <div class="">-->
    <!--        <input type="search" placeholder=" گەڕان...">-->
    <!--        <i class="fas fa-search" ></i>-->
    <!--    </div>-->
    <!--</div>-->
ProcurementRequestSystem

create database procurement_request_system;


CREATE TABLE users ( 
    id INT AUTO_INCREMENT PRIMARY KEY,
    userName VARCHAR(100) NOT NULL, 
    password VARCHAR(255) NOT NULL, 
    department VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role ENUM('dean_admin', 'assistant_first_admin', 'finance_second_admin', 'user') NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users (department, email, role) 
VALUES 
    ('سەرۆکی زانکۆ', 'dean@example.com', 'dean_admin'),
    ('یاریدەدەری سەرۆکی زانکۆ', 'firstadmin@example.com', 'assistant_first_admin'),
    ('بەڕێوبەراییەتی دارایی', 'finance@example.com', 'finance_second_admin'),
    ('بەڕێوبەراییەتی ئای تی', 'user1@example.com', 'user'),
    ('بەڕێوبەراییەتی یاسا', 'user2@example.com', 'user'),
    ('بەڕێوبەراییەتی خوێندنی باڵا', 'user3@example.com', 'user');



CREATE TABLE submitted_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    send_to VARCHAR(255) NOT NULL,
    sent_by VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    notes TEXT,
    file_path VARCHAR(255),
    status ENUM('not seen', 'pending', 'done') DEFAULT 'not seen',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE application_status_updates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    previous_receiver VARCHAR(255) NOT NULL,
    new_receiver VARCHAR(255) NOT NULL,
    decision VARCHAR(255) NOT NULL,
    comment TEXT,
    updated_by VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES submitted_applications(id)
);


User submits form → automatically goes to assistant_first_admin

assistant_first_admin chooses:

Send to user → ✅ done

Send to dean_admin → 🔄 pending

Send to finance_second_admin → 🔄 pending

(If they choose dean_admin:)

dean_admin reviews → must send back to assistant_first_admin

assistant_first_admin again chooses:

Send to user → ✅ done

Send to finance_second_admin → 🔄 pending

(If it reaches finance_second_admin:)

finance_second_admin can:

Send to user → ✅ done

Send to assistant_first_admin → 🔄 pending