-- ============================================================
--  TAHWISSA v2 — MySQL Schema
--  Compatible with NameHero / cPanel shared hosting (MySQL 5.7+)
--
--  HOW TO IMPORT ON NAMEHERO:
--  1. Login to cPanel → MySQL Databases
--  2. Create a new database  (e.g. user_tahwissa)
--  3. Create a DB user and assign ALL PRIVILEGES to that database
--  4. Open phpMyAdmin, select that database from the left panel
--  5. Click "Import" tab → choose this file → Go
--
--  NOTE: Do NOT use "Create new database" inside phpMyAdmin —
--  shared hosts don't allow that. Use cPanel → MySQL Databases.
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

-- ── Admins ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `admins` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `username`      VARCHAR(60)   NOT NULL,
  `password_hash` VARCHAR(255)  NOT NULL,
  `last_login`    TIMESTAMP     NULL DEFAULT NULL,
  `created_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admin_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Users (tourists & providers) ─────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `username`      VARCHAR(60)   NOT NULL,
  `email`         VARCHAR(255)  NOT NULL,
  `password_hash` VARCHAR(255)  NOT NULL,
  `role`          ENUM('tourist','provider') NOT NULL DEFAULT 'tourist',
  `status`        ENUM('active','suspended') NOT NULL DEFAULT 'active',
  `created_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`),
  UNIQUE KEY `uq_email`    (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Activity providers ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS `providers` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `user_id`       INT UNSIGNED  NOT NULL,
  `business_name` VARCHAR(200)  NOT NULL,
  `activity_type` VARCHAR(60)   NOT NULL,
  `email`         VARCHAR(255)  NOT NULL,
  `phone`         VARCHAR(30)   NOT NULL,
  `address`       VARCHAR(300)  DEFAULT NULL,
  `lat`           DECIMAL(10,7) DEFAULT NULL,
  `lng`           DECIMAL(10,7) DEFAULT NULL,
  `image`         VARCHAR(300)  DEFAULT NULL,
  `description`   TEXT          DEFAULT NULL,
  `status`        ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_provider_user`   (`user_id`),
  KEY `idx_provider_status` (`status`),
  CONSTRAINT `fk_provider_user` FOREIGN KEY (`user_id`)
    REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Famous places (admin managed) ────────────────────────────
CREATE TABLE IF NOT EXISTS `places` (
  `id`          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `name_ar`     VARCHAR(200)  NOT NULL,
  `name_en`     VARCHAR(200)  NOT NULL DEFAULT '',
  `name_fr`     VARCHAR(200)  NOT NULL DEFAULT '',
  `desc_ar`     TEXT          DEFAULT NULL,
  `desc_en`     TEXT          DEFAULT NULL,
  `desc_fr`     TEXT          DEFAULT NULL,
  `category`    ENUM('nature','heritage','oasis','desert','lake','monument','other')
                NOT NULL DEFAULT 'nature',
  `lat`         DECIMAL(10,7) NOT NULL DEFAULT 0.0000000,
  `lng`         DECIMAL(10,7) NOT NULL DEFAULT 0.0000000,
  `image`       VARCHAR(300)  DEFAULT NULL,
  `is_active`   TINYINT(1)    NOT NULL DEFAULT 1,
  `created_at`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contact messages ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `messages` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(150) NOT NULL,
  `email`      VARCHAR(255) NOT NULL,
  `phone`      VARCHAR(30)           DEFAULT NULL,
  `message`    TEXT         NOT NULL,
  `lang`       CHAR(2)      NOT NULL DEFAULT 'ar',
  `is_read`    TINYINT(1)   NOT NULL DEFAULT 0,
  `ip`         VARCHAR(45)           DEFAULT NULL,
  `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_is_read` (`is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Provider ratings ─────────────────────────────────────────
--  rating values 1-5 enforced in PHP (CHECK constraint skipped for MySQL 5.7 compat)
CREATE TABLE IF NOT EXISTS `provider_ratings` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider_id` INT UNSIGNED NOT NULL,
  `user_id`     INT UNSIGNED NOT NULL,
  `rating`      TINYINT      NOT NULL DEFAULT 1,
  `comment`     TEXT         DEFAULT NULL,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_provider`   (`user_id`, `provider_id`),
  KEY `idx_rating_provider`       (`provider_id`),
  CONSTRAINT `fk_rating_provider` FOREIGN KEY (`provider_id`)
    REFERENCES `providers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rating_user`     FOREIGN KEY (`user_id`)
    REFERENCES `users`(`id`)     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Service requests (tourist to provider) ───────────────────
CREATE TABLE IF NOT EXISTS `service_requests` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tourist_id`  INT UNSIGNED NOT NULL,
  `provider_id` INT UNSIGNED NOT NULL,
  `message`     TEXT         NOT NULL,
  `status`      ENUM('pending','accepted','declined') NOT NULL DEFAULT 'pending',
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_req_tourist`    (`tourist_id`),
  KEY `idx_req_provider`   (`provider_id`),
  CONSTRAINT `fk_req_tourist`  FOREIGN KEY (`tourist_id`)
    REFERENCES `users`(`id`)     ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_req_provider` FOREIGN KEY (`provider_id`)
    REFERENCES `providers`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;

-- ── Sample places (El Mghair region) ─────────────────────────
INSERT INTO `places`
  (`name_ar`, `name_en`, `name_fr`, `desc_ar`, `desc_en`, `desc_fr`, `category`, `lat`, `lng`, `is_active`)
VALUES
(
  'بحيرة مليلة',
  'Lake Melila',
  'Lac Melila',
  'بحيرة مالحة رائعة تعكس السماء كالمرآة في قلب الصحراء الجزائرية',
  'A stunning salt lake that mirrors the sky like a perfect glass in the Algerian desert',
  'Un magnifique lac sale qui reflete le ciel comme un miroir parfait',
  'lake', 33.9500000, 5.9200000, 1
),
(
  'واحة المغير',
  'El Mghair Oasis',
  'Oasis El Mghair',
  'واحة خلابة مليئة بأشجار النخيل الباسقة والظل الرائع في وادي ريغ',
  'A stunning oasis filled with tall palm trees in the Oued Righ valley',
  'Une oasis magnifique remplie de grands palmiers dans la vallee de l Oued Righ',
  'oasis', 33.9536000, 5.9256000, 1
),
(
  'كثبان الرمال الذهبية',
  'Golden Sand Dunes',
  'Dunes de Sable Dorees',
  'كثبان رملية ذهبية لا نهاية لها تمتد حتى الأفق مع غروب الشمس الرائع',
  'Endless golden sand dunes stretching to the horizon with breathtaking sunsets',
  'Des dunes de sable dorees infinies s etendant a l horizon',
  'desert', 33.8800000, 5.8500000, 1
),
(
  'قصر المغير التاريخي',
  'El Mghair Historic Kasbah',
  'Kasbah Historique El Mghair',
  'قصر تاريخي عريق يحكي قصص الحضارات المتعاقبة في هذه المنطقة الجميلة',
  'A historic kasbah telling stories of successive civilizations in this beautiful region',
  'Une kasbah historique racontant les histoires des civilisations successives',
  'heritage', 33.9580000, 5.9300000, 1
),
(
  'شط ملغيغ',
  'Chott Melghir',
  'Chott Melghir',
  'أحد أكبر البحيرات المالحة في الجزائر، تجربة فريدة من نوعها',
  'One of the largest salt lakes in Algeria, a unique and unforgettable experience',
  'L un des plus grands lacs sales d Algerie, une experience unique',
  'lake', 34.1800000, 6.3000000, 1
);

-- ── NEXT STEPS ───────────────────────────────────────────────
--  After import, open: https://yourdomain.com/tahwissa/admin/setup.php
--  Create the first admin account, then DELETE setup.php from the server.
-- ─────────────────────────────────────────────────────────────
