Spaces:
Sleeping
Sleeping
File size: 6,987 Bytes
eb6a2f9 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 | /* ----------------------
Enum/type definitions
---------------------- */
CREATE TYPE role_enum AS ENUM ('student','sheikh','admin');
CREATE TYPE gender_enum AS ENUM ('male','female');
CREATE TYPE quraan_level_enum AS ENUM ('beginner','intermediate','advanced');
CREATE TYPE specialization_enum AS ENUM ('tajweed','qiraat','tafseer','general');
CREATE TYPE live_status_enum AS ENUM ('online','offline','busy','away');
CREATE TYPE day_of_week_enum AS ENUM ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');
CREATE TYPE session_status_enum AS ENUM ('scheduled','completed','cancelled','missed');
CREATE TYPE payment_method_enum AS ENUM ('credit_card','paypal','vodafone_cash','wallet');
CREATE TYPE payment_status_enum AS ENUM ('pending','paid','failed','refunded');
CREATE TYPE transaction_type_enum AS ENUM ('deposit','withdraw','session_payment','refund','bonus');
/* ----------------------
Trigger to update updatedAt columns
---------------------- */
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updatedAt = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
/* ----------------------
Users table (was `user` in MySQL)
---------------------- */
CREATE TABLE users (
userId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(150) NOT NULL UNIQUE,
passHash VARCHAR(255) NOT NULL,
fullName VARCHAR(100) NOT NULL,
isActive BOOLEAN NOT NULL DEFAULT TRUE,
phone VARCHAR(20) UNIQUE,
role role_enum NOT NULL,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
/* ----------------------
Student profile (1-to-1 mandatory)
studentId references users.userId
---------------------- */
CREATE TABLE student_profile (
studentId INT PRIMARY KEY REFERENCES users(userId)
ON DELETE CASCADE
ON UPDATE CASCADE,
gender gender_enum NOT NULL,
birthdate DATE NOT NULL,
quraan_level quraan_level_enum NOT NULL
);
/* ----------------------
Sheikh profile (1-to-1 mandatory)
---------------------- */
CREATE TABLE sheikh_profile (
sheikhId INT PRIMARY KEY REFERENCES users(userId)
ON DELETE CASCADE
ON UPDATE CASCADE,
bio TEXT NOT NULL,
specialization specialization_enum NOT NULL,
yearsOfExperience INT NOT NULL,
certificatesURL VARCHAR(255),
hourlyRate DECIMAL(10,2) NOT NULL,
ratingAVG DECIMAL(3,2) DEFAULT 0,
liveStatus live_status_enum NOT NULL DEFAULT 'offline',
lastActiveAt TIMESTAMP
);
/* ----------------------
Availability
---------------------- */
CREATE TABLE availability (
availabilityId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
dayOfWeek day_of_week_enum NOT NULL,
startTime TIME NOT NULL,
endTime TIME NOT NULL,
sheikhId INT NOT NULL REFERENCES users(userId)
ON DELETE CASCADE
ON UPDATE CASCADE
);
/* ----------------------
Recitations
---------------------- */
CREATE TABLE recitations (
recitationId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
surahStart VARCHAR(25) NOT NULL,
ayahtStart INT NOT NULL,
surahEnd VARCHAR(25) NOT NULL,
ayahtEnd INT NOT NULL,
mistakesReport JSONB NOT NULL,
tajweedScore DECIMAL(4,2) NOT NULL,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
studentId INT NOT NULL REFERENCES users(userId)
ON DELETE CASCADE
ON UPDATE CASCADE
);
/* ----------------------
Session
---------------------- */
CREATE TABLE session (
sessionId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
scheduledStart TIMESTAMP NOT NULL,
scheduledEnd TIMESTAMP NOT NULL,
actualStart TIMESTAMP,
actualEnd TIMESTAMP,
meetingLink VARCHAR(255),
status session_status_enum NOT NULL DEFAULT 'scheduled',
sessionNotes TEXT,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
studentId INT NOT NULL REFERENCES users(userId)
ON DELETE RESTRICT
ON UPDATE CASCADE,
sheikhId INT NOT NULL REFERENCES users(userId)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
-- trigger to auto-update updatedAt on session
CREATE TRIGGER trg_session_updated_at
BEFORE UPDATE ON session
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
/* ----------------------
Payment (1-to-1 with session)
Note: corrected typo "shiekhId" -> "sheikhId"
---------------------- */
CREATE TABLE payment (
paymentId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
currency VARCHAR(10) NOT NULL,
paymentMethod payment_method_enum NOT NULL,
paymentStatus payment_status_enum NOT NULL DEFAULT 'pending',
transaction_id VARCHAR(100) UNIQUE,
paymentDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
sheikhId INT NOT NULL REFERENCES users(userId)
ON DELETE RESTRICT
ON UPDATE CASCADE,
studentId INT NOT NULL REFERENCES users(userId)
ON DELETE RESTRICT
ON UPDATE CASCADE,
sessionId INT NOT NULL UNIQUE REFERENCES session(sessionId)
ON DELETE CASCADE
ON UPDATE CASCADE
);
/* ----------------------
Review (1-to-1 with session)
---------------------- */
CREATE TABLE review (
reviewId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
rate INT NOT NULL CHECK (rate BETWEEN 1 AND 5),
comment TEXT,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
sessionId INT NOT NULL UNIQUE REFERENCES session(sessionId)
ON DELETE CASCADE
ON UPDATE CASCADE,
studentId INT NOT NULL REFERENCES users(userId)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
/* ----------------------
Wallet (0–1 optional)
---------------------- */
CREATE TABLE wallet (
walletId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
balance DECIMAL(10,2) NOT NULL DEFAULT 0,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
userId INT UNIQUE REFERENCES users(userId)
ON DELETE SET NULL
ON UPDATE CASCADE
);
-- trigger to auto-update updatedAt on wallet
CREATE TRIGGER trg_wallet_updated_at
BEFORE UPDATE ON wallet
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
/* ----------------------
Wallet Transaction (1-to-many with wallet)
---------------------- */
CREATE TABLE wallet_transaction (
transactionId INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL,
transactionType transaction_type_enum NOT NULL,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
paymentId INT REFERENCES payment(paymentId)
ON DELETE SET NULL
ON UPDATE CASCADE,
sessionId INT REFERENCES session(sessionId)
ON DELETE SET NULL
ON UPDATE CASCADE,
walletId INT NOT NULL REFERENCES wallet(walletId)
ON DELETE CASCADE
ON UPDATE CASCADE
);
/* ----------------------
End of schema
---------------------- */
|