-- =====================================================
-- HandBrakeCars Database Schema
-- handbrakecars.com
-- =====================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- =====================================================
-- 1. ADMINS
-- =====================================================
CREATE TABLE `admins` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(100) NOT NULL,
  `email`      VARCHAR(150) NOT NULL UNIQUE,
  `password`   VARCHAR(255) NOT NULL,
  `is_super`   TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin: admin@handbrakecars.com / Admin@1234
INSERT INTO `admins` (`name`, `email`, `password`, `is_super`)
VALUES ('Super Admin', 'admin@handbrakecars.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

-- =====================================================
-- 2. OWNERS (ملاك العربيات)
-- =====================================================
CREATE TABLE `owners` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`             VARCHAR(100) NOT NULL,
  `email`            VARCHAR(150) NOT NULL UNIQUE,
  `phone`            VARCHAR(20) NOT NULL,
  `password`         VARCHAR(255) NOT NULL,
  `img_car_license`  VARCHAR(255) DEFAULT NULL,
  `img_insurance`    VARCHAR(255) DEFAULT NULL,
  `bank_name`        VARCHAR(100) DEFAULT NULL,
  `bank_account`     VARCHAR(100) DEFAULT NULL,
  `vodafone_cash`    VARCHAR(20)  DEFAULT NULL,
  `commission_rate`  DECIMAL(5,2) DEFAULT 10.00,
  `status`           ENUM('pending','active','suspended') DEFAULT 'pending',
  `verified`         TINYINT(1) DEFAULT 0,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 3. USERS (العملاء)
-- =====================================================
CREATE TABLE `users` (
  `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`             VARCHAR(100) NOT NULL,
  `email`            VARCHAR(150) NOT NULL UNIQUE,
  `phone`            VARCHAR(20) NOT NULL,
  `password`         VARCHAR(255) NOT NULL,
  `address`          VARCHAR(255) DEFAULT NULL,
  `national_id_no`   VARCHAR(20)  DEFAULT NULL,
  `img_national_id`  VARCHAR(255) DEFAULT NULL,
  `img_license`      VARCHAR(255) DEFAULT NULL,
  `img_selfie`       VARCHAR(255) DEFAULT NULL,
  `status`           ENUM('pending','active','suspended') DEFAULT 'active',
  `verified`         TINYINT(1) DEFAULT 0,
  `created_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 4. CARS (العربيات)
-- =====================================================
CREATE TABLE `cars` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `owner_id`     INT UNSIGNED NOT NULL,
  `added_by`     ENUM('owner','admin') DEFAULT 'owner',
  `brand`        VARCHAR(100) NOT NULL,
  `model`        VARCHAR(100) NOT NULL,
  `year`         YEAR NOT NULL,
  `type`         ENUM('sedan','suv','luxury','van','truck','other') NOT NULL,
  `color`        VARCHAR(50) DEFAULT NULL,
  `plate_no`     VARCHAR(30) DEFAULT NULL,
  `seats`        TINYINT DEFAULT 5,
  `fuel`         ENUM('petrol','diesel','electric','hybrid') DEFAULT 'petrol',
  `transmission` ENUM('automatic','manual') DEFAULT 'automatic',
  `price_day`    DECIMAL(10,2) NOT NULL,
  `price_week`   DECIMAL(10,2) DEFAULT NULL,
  `price_month`  DECIMAL(10,2) DEFAULT NULL,
  `location`     VARCHAR(150) DEFAULT NULL,
  `description`  TEXT DEFAULT NULL,
  `img_main`     VARCHAR(255) DEFAULT NULL,
  `img_2`        VARCHAR(255) DEFAULT NULL,
  `img_3`        VARCHAR(255) DEFAULT NULL,
  `img_4`        VARCHAR(255) DEFAULT NULL,
  `status`       ENUM('available','rented','maintenance') DEFAULT 'available',
  `approved`     TINYINT(1) DEFAULT 0,
  `views`        INT DEFAULT 0,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `cars`
  ADD CONSTRAINT `fk_cars_owner`
  FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON DELETE CASCADE;

-- =====================================================
-- 5. BOOKINGS (الحجوزات)
-- =====================================================
CREATE TABLE `bookings` (
  `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `booking_ref`    VARCHAR(20) NOT NULL UNIQUE,
  `user_id`        INT UNSIGNED NOT NULL,
  `car_id`         INT UNSIGNED NOT NULL,
  `owner_id`       INT UNSIGNED NOT NULL,
  `pickup_date`    DATE NOT NULL,
  `return_date`    DATE NOT NULL,
  `total_days`     INT NOT NULL,
  `price_per_day`  DECIMAL(10,2) NOT NULL,
  `subtotal`       DECIMAL(10,2) NOT NULL,
  `commission_pct` DECIMAL(5,2)  NOT NULL DEFAULT 10.00,
  `commission_amt` DECIMAL(10,2) NOT NULL,
  `owner_earning`  DECIMAL(10,2) NOT NULL,
  `payment_method` ENUM('stripe','paypal','cash') DEFAULT 'cash',
  `payment_status` ENUM('pending','paid','refunded') DEFAULT 'pending',
  `status`         ENUM('pending','active','completed','cancelled') DEFAULT 'pending',
  `notes`          TEXT DEFAULT NULL,
  `created_at`     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `bookings`
  ADD CONSTRAINT `fk_bookings_user`  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`)  ON DELETE CASCADE,
  ADD CONSTRAINT `fk_bookings_car`   FOREIGN KEY (`car_id`)   REFERENCES `cars`(`id`)   ON DELETE CASCADE,
  ADD CONSTRAINT `fk_bookings_owner` FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON DELETE CASCADE;

-- =====================================================
-- 6. BRANCHES (الفروع)
-- =====================================================
CREATE TABLE `branches` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`       VARCHAR(150) NOT NULL,
  `address`    VARCHAR(255) DEFAULT NULL,
  `city`       VARCHAR(100) DEFAULT NULL,
  `phone`      VARCHAR(20)  DEFAULT NULL,
  `lat`        DECIMAL(10,7) DEFAULT NULL,
  `lng`        DECIMAL(10,7) DEFAULT NULL,
  `hours`      VARCHAR(100) DEFAULT NULL,
  `status`     ENUM('active','inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `branches` (`name`, `address`, `city`, `hours`) VALUES
('Cairo — Nasr City', 'Mostafa El-Nahas St, Nasr City', 'Cairo', '9am - 10pm'),
('Cairo — Maadi',     'Road 9, Maadi',                  'Cairo', '8am - 11pm'),
('Alexandria',        'El-Corniche, Sidi Gaber',         'Alex',  '9am - 9pm'),
('Giza — 6th October','Central Axis, 6th October',       'Giza',  '10am - 8pm');

-- =====================================================
-- 7. SETTINGS (إعدادات الموقع)
-- =====================================================
CREATE TABLE `settings` (
  `id`           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key`  VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name',        'HandBrakeCars'),
('commission_rate',  '10'),
('currency',         'EGP'),
('contact_email',    'info@handbrakecars.com'),
('contact_phone',    '+20 100 000 0000');

-- =====================================================
-- 8. NOTIFICATIONS (الإشعارات)
-- =====================================================
CREATE TABLE `notifications` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `to_type`    ENUM('admin','owner','user') NOT NULL,
  `to_id`      INT UNSIGNED NOT NULL,
  `message`    TEXT NOT NULL,
  `link`       VARCHAR(255) DEFAULT NULL,
  `is_read`    TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- INDEXES للسرعة
-- =====================================================
ALTER TABLE `users`     ADD INDEX `idx_email`   (`email`);
ALTER TABLE `users`     ADD INDEX `idx_phone`   (`phone`);
ALTER TABLE `owners`    ADD INDEX `idx_email`   (`email`);
ALTER TABLE `cars`      ADD INDEX `idx_status`  (`status`, `approved`);
ALTER TABLE `cars`      ADD INDEX `idx_type`    (`type`);
ALTER TABLE `bookings`  ADD INDEX `idx_user`    (`user_id`);
ALTER TABLE `bookings`  ADD INDEX `idx_status`  (`status`);
ALTER TABLE `bookings`  ADD INDEX `idx_ref`     (`booking_ref`);