/home/awneajlw/public_html/codestechvista.com/database_setup.sql
-- Eye Clinic Website Database Setup
CREATE DATABASE IF NOT EXISTS `eye clinic website` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `eye clinic website`;
-- Users table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20) NOT NULL,
password VARCHAR(255) NOT NULL,
role ENUM('user', 'admin', 'shop_owner') DEFAULT 'user',
email_verified BOOLEAN DEFAULT FALSE,
registration_step INT DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Shops table
CREATE TABLE shops (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
shop_name VARCHAR(200) NOT NULL,
shop_category VARCHAR(100),
shop_address TEXT,
shop_city VARCHAR(100),
shop_phone VARCHAR(20),
shop_email VARCHAR(100),
shop_description TEXT,
shop_logo VARCHAR(255),
status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- OTP verification table
CREATE TABLE otp_verifications (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) NOT NULL,
otp_code VARCHAR(6) NOT NULL,
expires_at TIMESTAMP NOT NULL,
is_used BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Services table
CREATE TABLE services (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2),
image VARCHAR(255),
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Appointments table
CREATE TABLE appointments (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
service_id INT,
appointment_date DATE NOT NULL,
appointment_time TIME NOT NULL,
status ENUM('pending', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending',
notes TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Doctors table
CREATE TABLE doctors (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
specialization VARCHAR(100) NOT NULL,
qualification TEXT,
experience INT,
image VARCHAR(255),
bio TEXT,
status ENUM('active', 'inactive') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Contact messages table
CREATE TABLE contact_messages (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
subject VARCHAR(200),
message TEXT NOT NULL,
status ENUM('unread', 'read', 'replied') DEFAULT 'unread',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Insert default admin
INSERT INTO users (name, email, phone, password, role) VALUES
('Admin', 'admin@eyeclinic.com', '03001234567', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');
-- Insert sample services
INSERT INTO services (title, description, price, image) VALUES
('Eye Examination', 'Comprehensive eye examination with modern equipment', 5000.00, 'images/services/eye-exam.jpg'),
('Cataract Surgery', 'Advanced cataract surgery with premium lenses', 150000.00, 'images/services/cataract.jpg'),
('Glaucoma Treatment', 'Complete glaucoma diagnosis and treatment', 25000.00, 'images/services/glaucoma.jpg'),
('LASIK Surgery', 'Laser vision correction surgery', 200000.00, 'images/services/lasik.jpg'),
('Retina Treatment', 'Specialized retina and vitreous treatment', 35000.00, 'images/services/retina.jpg'),
('Pediatric Eye Care', 'Specialized eye care for children', 3000.00, 'images/services/pediatric.jpg');
-- Insert sample doctors
INSERT INTO doctors (name, specialization, qualification, experience, bio) VALUES
('Dr. Ahmed Ali', 'Ophthalmologist', 'MBBS, FCPS (Ophthalmology)', 15, 'Expert in cataract and refractive surgery'),
('Dr. Fatima Khan', 'Pediatric Ophthalmologist', 'MBBS, FCPS (Ophthalmology), Fellowship in Pediatric Ophthalmology', 12, 'Specialized in children eye care and strabismus treatment'),
('Dr. Muhammad Hassan', 'Retina Specialist', 'MBBS, FCPS (Ophthalmology), Fellowship in Retina', 10, 'Expert in retinal diseases and vitreous surgery'),
('Dr. Ayesha Malik', 'Glaucoma Specialist', 'MBBS, FCPS (Ophthalmology), Fellowship in Glaucoma', 8, 'Specialized in glaucoma diagnosis and treatment');