-- Omani Center OS - Database Schema
-- phpMyAdmin SQL Dump
-- Version 5.0.0
-- Host: localhost
-- Generation Time: Jun 12, 2026 at 12:00 AM
-- Server version: 8.0.0
-- PHP Version: 8.3.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `omani_center`
--
CREATE DATABASE IF NOT EXISTS `omani_center` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `omani_center`;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--
CREATE TABLE `users` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) NOT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--
CREATE TABLE `settings` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` varchar(100) NOT NULL,
  `value` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_key_unique` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `posts` (example)
--
CREATE TABLE `posts` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `user_id` int UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `posts_user_id_foreign` (`user_id`),
  CONSTRAINT `posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Dummy data for testing
--
INSERT INTO `users` (`name`, `email`, `password`) VALUES
(\'مدير النظام\', \'admin@example.com\', \'$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi\'), -- password: secret

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- --------------------------------------------------------

--
-- Table structure for table `services`
--
CREATE TABLE `services` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `icon` varchar(50) DEFAULT 'stethoscope',
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `therapists`
--
CREATE TABLE `therapists` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `specialty` varchar(100) NOT NULL,
  `bio` text,
  `image` varchar(255) DEFAULT 'therapist.jpg',
  `active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `areas`
--
CREATE TABLE `areas` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bookings`
--
CREATE TABLE `bookings` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `booking_number` varchar(20) NOT NULL,
  `user_id` int UNSIGNED NULL,
  `name` varchar(150) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(150) NULL,
  `appointment_type` enum('clinic','home','video') NOT NULL DEFAULT 'clinic',
  `date` date NOT NULL,
  `time` time NOT NULL,
  `services` json NULL,
  `address` text NULL,
  `notes` text NULL,
  `payment_method` enum('cash','card','online') NOT NULL DEFAULT 'cash',
  `status` enum('pending','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bookings_booking_number_unique` (`booking_number`),
  KEY `bookings_user_id_foreign` (`user_id`),
  CONSTRAINT `bookings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------

--
-- Dummy data for table `services`
--
INSERT INTO `services` (`name`, `description`, `icon`) VALUES
('استشارة طبية', 'استشارة شاملة مع أطباء متخصصين في مختلف التخصصات', 'stethoscope'),
('علاج طبيعي', 'برامج علاج طبيعي متقدمة للإصابات والآلام المزمنة', 'spa'),
('فحوصات شاملة', 'فحوصات دورية وشاملة تشمل الدم والصور والقلب', 'clipboard-list'),
('رعاية كبار السن', 'خدمات مخصصة لكبار السن تشمل المتابعة المنزلية والدعم التمريضي', 'user-injury'),
('أسنان وتجميل', 'خدمات طب الأسنان والتجميل مع أحدث التقنيات', 'tooth'),
('جلدية وليزر', 'علاج الأمراض الجلدية وإجراءات الليزر التجميلي', 'spa');

-- --------------------------------------------------------

--
-- Dummy data for table `therapists`
--
INSERT INTO `therapists` (`name`, `specialty`, `bio`, `image`) VALUES
('د. أحمد العلوي', 'طب العائلة', 'خبرة 20 سنة في طب العائلة والرعاية الأولية. حاصل على الزمالة البريطانية في الطب العام.', 'therapist1.jpg'),
('د. ليلى السعيد', 'الجلدية', 'اختصاصية في الأمراض الجلدية والليزر التجميلي. عملت في أفضل العيادات في دبي وأبوظبي.', 'therapist2.jpg'),
('د. محمد الفارسي', 'العلاج الطبيعي', 'متخصص في إعادة التأهيل والعلاج الرياضي. عمل مع فرق رياضية محلية ودولية.', 'therapist3.jpg'),
('د. فاطمة الزهراء', 'الأسنان', 'اختصاصية في طب الأسنان التجميلي وعلاج الجذور.', 'therapist4.jpg'),
('د. Khalid Patel', 'التغذية', 'استشاري تغذية علاجية يساعد المرضى على تحقيق أهدافهم الصحية عبر التغذية المتوازنة.', 'therapist5.jpg');

-- --------------------------------------------------------

--
-- Dummy data for table `areas`
--
INSERT INTO `areas` (`name`) VALUES
('مسقط'),
('صلالة'),
('صحار'),
('نزوى'),
('السويق'),
('بركاء'),
('صور'),
('بدية');

-- --------------------------------------------------------

--
-- Dummy data for table `settings`
--
INSERT INTO `settings` (`key`, `value`) VALUES
('site_name', 'مركز العماني'),
('site_tagline', 'الرعاية الفاخرة التي تستحقها'),
('contact_phone', '+968 2456 7890'),
('contact_email', 'info@omanicenter.om'),
('contact_address', 'مسقط، شارع السلطان Qaboos، بناء العماني، الطابق الأول'),
('working_hours', 'السبت - الخميس: 8:00 ص - 8:00 م'),
('currency_code', 'OMR'),
('currency_symbol', 'ر.ع.');

-- --------------------------------------------------------

-- Add index for better query performance on bookings
ALTER TABLE `bookings` ADD INDEX `idx_date_status` (`date`, `status`);

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- --------------------------------------------------------

--
-- Table structure for table contact_messages
--
CREATE TABLE contact_messages (
  id int UNSIGNED NOT NULL AUTO_INCREMENT,
  
ame varchar(150) NOT NULL,
  email varchar(150) NOT NULL,
  phone varchar(20) NULL,
  subject varchar(200) NULL,
  message text NOT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------

--
-- Table structure for table `contact_messages`
--
DROP TABLE IF EXISTS `contact_messages`;
CREATE TABLE `contact_messages` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `phone` varchar(20) NULL,
  `subject` varchar(200) NULL,
  `message` text NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `work_hours`
--
CREATE TABLE `work_hours` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `day_of_week` tinyint NOT NULL COMMENT '0=Sunday,1=Monday,...,6=Saturday',
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `break_start` time NULL,
  `break_end` time NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_day` (`day_of_week`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `date_overrides`
--
CREATE TABLE `date_overrides` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `override_type` enum('closed','open','period_closed') NOT NULL DEFAULT 'closed',
  `start_time` time NULL,
  `end_time` time NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Dummy data for table `work_hours`
--
INSERT INTO `work_hours` (`day_of_week`, `start_time`, `end_time`, `break_start`, `break_end`, `is_active`) VALUES
(0, '08:00:00', '20:00:00', '13:00:00', '14:00:00', 1), -- Sunday
(1, '08:00:00', '20:00:00', '13:00:00', '14:00:00', 1), -- Monday
(2, '08:00:00', '20:00:00', '13:00:00', '14:00:00', 1), -- Tuesday
(3, '08:00:00', '20:00:00', '13:00:00', '14:00:00', 1), -- Wednesday
(4, '08:00:00', '20:00:00', '13:00:00', '14:00:00', 1), -- Thursday
(5, '08:00:00', '12:00:00', NULL, NULL, 1), -- Friday half day
(6, '00:00:00', '00:00:00', NULL, NULL, 0); -- Saturday closed

-- --------------------------------------------------------

--
-- Dummy data for table `date_overrides` (examples)
--
INSERT INTO `date_overrides` (`date`, `override_type`, `start_time`, `end_time`) VALUES
('2026-06-15', 'closed', NULL, NULL), -- special closed day
('2026-06-16', 'open', '09:00:00', '17:00:00'); -- open with custom hours

COMMIT;

-- Update bookings table to add workflow statuses and invoice_id, services_performed
ALTER TABLE `bookings` MODIFY COLUMN `status` ENUM('pending','confirmed','on_the_way','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'pending';
ALTER TABLE `bookings` ADD COLUMN `invoice_id` INT UNSIGNED NULL AFTER `id`;
ALTER TABLE `bookings` ADD COLUMN `services_performed` JSON NULL AFTER `services`;

-- Invoices table
CREATE TABLE IF NOT EXISTS `invoices` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_number` VARCHAR(20) NOT NULL,
  `booking_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `issue_date` DATE NOT NULL,
  `due_date` DATE NULL,
  `total_amount` DECIMAL(10,2) NOT NULL,
  `paid_amount` DECIMAL(10,2) DEFAULT 0.00,
  `payment_method` ENUM('cash','card','online') NULL,
  `payment_date` DATE NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
  KEY `invoices_booking_id_foreign` (`booking_id`),
  KEY `invoices_user_id_foreign` (`user_id`),
  CONSTRAINT `invoices_booking_id_foreign` FOREIGN KEY (`booking_id`) REFERENCES `bookings` (`id`) ON DELETE CASCADE,
  CONSTRAINT `invoices_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoice items
CREATE TABLE IF NOT EXISTS `invoice_items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` INT UNSIGNED NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `total_price` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `invoice_items_invoice_id_foreign` (`invoice_id`),
  CONSTRAINT `invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Revenues
CREATE TABLE IF NOT EXISTS `revenues` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` INT UNSIGNED NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `revenue_date` DATE NOT NULL,
  `payment_method` ENUM('cash','card','online') NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `revenues_invoice_id_foreign` (`invoice_id`),
  CONSTRAINT `revenues_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expenses
CREATE TABLE IF NOT EXISTS `expenses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `expense_date` DATE NOT NULL,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings (ensure we have some keys)
INSERT IGNORE INTO `settings` (`key`, `value`) VALUES
('site_name', 'مركز العماني'),
('site_tagline', 'الرعاية الفاخرة التي تستحقها'),
('contact_phone', '+968 2456 7890'),
('contact_email', 'info@omanicenter.om'),
('contact_address', 'مسقط، شارع السلطان Qaboos، بناء العماني، الطابق الأول'),
('working_hours', 'السبت - الخميس: 8:00 ص - 8:00 م'),
('currency_code', 'OMR'),
('currency_symbol', 'ر.ع.'),
('telegram_bot_token', ''),
('telegram_chat_id', ''),
('invoice_prefix', 'INV'),
('invoice_next_number', '1');

-- Telegram logs
CREATE TABLE IF NOT EXISTS `telegram_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `message` TEXT NOT NULL,
  `sent_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('success','failed') NOT NULL DEFAULT 'success',
  `error_message` TEXT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reports cache (optional)
CREATE TABLE IF NOT EXISTS `reports_cache` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `report_type` VARCHAR(50) NOT NULL,
  `period_start` DATE NOT NULL,
  `period_end` DATE NOT NULL,
  `data` JSON NOT NULL,
  `generated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_report` (`report_type`,`period_start`,`period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;