CREATE DATABASE IF NOT EXISTS blood_doner CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blood_doner;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address TEXT NOT NULL,
    mobile VARCHAR(20) NOT NULL UNIQUE,
    role ENUM('patient', 'donor', 'admin') NOT NULL,
    aadhar_no VARCHAR(50) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS requisitions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requester_id INT UNSIGNED NOT NULL,
    assigned_donor_id INT UNSIGNED DEFAULT NULL,
    status ENUM('Pending', 'Assigned', 'Done') NOT NULL DEFAULT 'Pending',
    completion_image VARCHAR(255) DEFAULT NULL,
    feedback_text TEXT DEFAULT NULL,
    rating DECIMAL(3,1) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_requisition_requester
        FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_requisition_donor
        FOREIGN KEY (assigned_donor_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS requisition_images (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requisition_id INT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_requisition_images
        FOREIGN KEY (requisition_id) REFERENCES requisitions(id) ON DELETE CASCADE
);

INSERT INTO users (name, address, mobile, role, aadhar_no)
SELECT 'Admin User', 'Control Room', '9999', 'admin', NULL
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE mobile = '9999' AND role = 'admin'
);

INSERT INTO users (name, address, mobile, role, aadhar_no)
SELECT 'Sohan Karim', 'Mirpur, Dhaka', '01844002211', 'donor', '6655 1100 2233'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE mobile = '01844002211'
);

INSERT INTO users (name, address, mobile, role, aadhar_no)
SELECT 'Nusrat Jahan', 'Chattogram', '01988004455', 'donor', '8855 4411 9900'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE mobile = '01988004455'
);