70 lines
2.4 KiB
SQL
70 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS migrations (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
migration_name VARCHAR(255) NOT NULL UNIQUE,
|
|
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS donors (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
full_name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
phone VARCHAR(50),
|
|
blood_type VARCHAR(10),
|
|
organs_to_donate TEXT,
|
|
medical_history TEXT,
|
|
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
status VARCHAR(50) DEFAULT 'pending_verification',
|
|
password_hash VARCHAR(255) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS hospitals (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
phone VARCHAR(255),
|
|
address TEXT,
|
|
password VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
status VARCHAR(50) DEFAULT 'pending' -- pending, approved, rejected
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
user_type VARCHAR(50) NOT NULL, -- 'hospital', 'donor', 'admin'
|
|
token VARCHAR(255) NOT NULL UNIQUE,
|
|
expires_at DATETIME NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS recipients (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
hospital_id INT NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
blood_type VARCHAR(10) NOT NULL,
|
|
organ VARCHAR(50) NOT NULL,
|
|
urgency INT NOT NULL, -- 1-10 scale
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admins (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Add a default admin user
|
|
INSERT INTO admins (email, password) VALUES ('admin@email.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'); -- password
|
|
|
|
CREATE TABLE IF NOT EXISTS matches (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
donor_id INT NOT NULL,
|
|
recipient_id INT NOT NULL,
|
|
status VARCHAR(50) DEFAULT 'pending', -- pending, approved, rejected
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (donor_id) REFERENCES donors(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (recipient_id) REFERENCES recipients(id) ON DELETE CASCADE
|
|
);
|