-- ============================================
-- BAITARI Platform - MySQL Database Schema
-- Prefix: baitari_
-- Charset: utf8mb4
-- ============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- --------------------------------------------
-- 1. USERS (base table for all roles)
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_users` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(100) NOT NULL,
  `phone`         VARCHAR(20)  NOT NULL UNIQUE,
  `email`         VARCHAR(150) UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role`          ENUM('pet_owner','farm_owner','vet','admin') NOT NULL DEFAULT 'pet_owner',
  `avatar`        VARCHAR(255),
  `wilaya`        VARCHAR(60),
  `commune`       VARCHAR(60),
  `address`       TEXT,
  `lat`           DECIMAL(10,7),
  `lng`           DECIMAL(10,7),
  `lang_pref`     ENUM('ar','fr','en') NOT NULL DEFAULT 'ar',
  `theme_pref`    ENUM('light','dark') NOT NULL DEFAULT 'light',
  `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
  `last_login`    DATETIME,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX idx_role (`role`),
  INDEX idx_wilaya (`wilaya`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 2. PETS (for pet_owner)
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_pets` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `owner_id`      INT UNSIGNED NOT NULL,
  `name`          VARCHAR(80) NOT NULL,
  `species`       ENUM('dog','cat','bird','rabbit','horse','cow','sheep','other') NOT NULL,
  `breed`         VARCHAR(100),
  `gender`        ENUM('male','female','unknown') DEFAULT 'unknown',
  `birth_date`    DATE,
  `weight_kg`     DECIMAL(5,2),
  `color`         VARCHAR(60),
  `microchip`     VARCHAR(60),
  `photo`         VARCHAR(255),
  `is_vaccinated` TINYINT(1) DEFAULT 0,
  `notes`         TEXT,
  `is_active`     TINYINT(1) DEFAULT 1,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`owner_id`) REFERENCES `baitari_users`(`id`) ON DELETE CASCADE,
  INDEX idx_owner (`owner_id`),
  INDEX idx_species (`species`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 3. FARM HERDS (for farm_owner)
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_herds` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `farm_id`       INT UNSIGNED NOT NULL,
  `species`       ENUM('cattle','sheep','goats','horses','poultry','camels','other') NOT NULL,
  `name`          VARCHAR(100) NOT NULL,
  `count`         INT UNSIGNED NOT NULL DEFAULT 0,
  `health_status` ENUM('healthy','needs_attention','critical') DEFAULT 'healthy',
  `location_name` VARCHAR(150),
  `lat`           DECIMAL(10,7),
  `lng`           DECIMAL(10,7),
  `notes`         TEXT,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`farm_id`) REFERENCES `baitari_users`(`id`) ON DELETE CASCADE,
  INDEX idx_farm (`farm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 4. VETERINARIAN PROFILES
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_vet_profiles` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vet_id`          INT UNSIGNED NOT NULL UNIQUE,
  `license_no`      VARCHAR(60) NOT NULL,
  `specialities`    JSON,
  `bio`             TEXT,
  `experience_years`TINYINT UNSIGNED DEFAULT 0,
  `clinic_name`     VARCHAR(150),
  `clinic_address`  TEXT,
  `clinic_lat`      DECIMAL(10,7),
  `clinic_lng`      DECIMAL(10,7),
  `home_visits`     TINYINT(1) DEFAULT 1,
  `teleconsult`     TINYINT(1) DEFAULT 0,
  `fee_clinic`      DECIMAL(8,2),
  `fee_home`        DECIMAL(8,2),
  `rating`          DECIMAL(3,2) DEFAULT 0.00,
  `review_count`    INT UNSIGNED DEFAULT 0,
  `is_verified`     TINYINT(1) DEFAULT 0,
  `is_available`    TINYINT(1) DEFAULT 1,
  `accepts_emergency` TINYINT(1) DEFAULT 0,
  `availability_json` JSON COMMENT 'Weekly schedule JSON',
  `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`vet_id`) REFERENCES `baitari_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 5. APPOINTMENTS
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_appointments` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id`     INT UNSIGNED NOT NULL,
  `vet_id`        INT UNSIGNED NOT NULL,
  `pet_id`        INT UNSIGNED,
  `herd_id`       INT UNSIGNED,
  `visit_type`    ENUM('clinic','home','emergency','teleconsult') NOT NULL DEFAULT 'clinic',
  `scheduled_at`  DATETIME NOT NULL,
  `duration_min`  SMALLINT UNSIGNED DEFAULT 30,
  `status`        ENUM('pending','confirmed','in_progress','completed','cancelled','rejected') DEFAULT 'pending',
  `symptoms`      TEXT,
  `address`       TEXT COMMENT 'For home visits',
  `lat`           DECIMAL(10,7),
  `lng`           DECIMAL(10,7),
  `fee`           DECIMAL(8,2),
  `payment_status`ENUM('unpaid','paid','refunded') DEFAULT 'unpaid',
  `vet_notes`     TEXT,
  `diagnosis`     TEXT,
  `prescription`  TEXT,
  `cancelled_by`  ENUM('client','vet','admin','system'),
  `cancel_reason` VARCHAR(255),
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`client_id`) REFERENCES `baitari_users`(`id`),
  FOREIGN KEY (`vet_id`) REFERENCES `baitari_users`(`id`),
  FOREIGN KEY (`pet_id`) REFERENCES `baitari_pets`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`herd_id`) REFERENCES `baitari_herds`(`id`) ON DELETE SET NULL,
  INDEX idx_client (`client_id`),
  INDEX idx_vet (`vet_id`),
  INDEX idx_scheduled (`scheduled_at`),
  INDEX idx_status (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 6. EMERGENCY REQUESTS
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_emergencies` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id`     INT UNSIGNED NOT NULL,
  `animal_type`   VARCHAR(60) NOT NULL,
  `problem`       TEXT NOT NULL,
  `lat`           DECIMAL(10,7),
  `lng`           DECIMAL(10,7),
  `address`       TEXT,
  `status`        ENUM('pending','assigned','in_progress','resolved','no_vet') DEFAULT 'pending',
  `assigned_vet`  INT UNSIGNED,
  `assigned_at`   DATETIME,
  `resolved_at`   DATETIME,
  `appt_id`       INT UNSIGNED COMMENT 'Created appointment if accepted',
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`client_id`) REFERENCES `baitari_users`(`id`),
  FOREIGN KEY (`assigned_vet`) REFERENCES `baitari_users`(`id`) ON DELETE SET NULL,
  INDEX idx_status (`status`),
  INDEX idx_created (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 7. MEDICAL RECORDS
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_medical_records` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appt_id`       INT UNSIGNED NOT NULL,
  `pet_id`        INT UNSIGNED,
  `herd_id`       INT UNSIGNED,
  `vet_id`        INT UNSIGNED NOT NULL,
  `record_date`   DATE NOT NULL,
  `visit_reason`  TEXT,
  `diagnosis`     TEXT,
  `treatment`     TEXT,
  `prescription`  TEXT,
  `next_visit`    DATE,
  `attachments`   JSON COMMENT 'Array of file paths',
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`appt_id`) REFERENCES `baitari_appointments`(`id`),
  FOREIGN KEY (`pet_id`) REFERENCES `baitari_pets`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`vet_id`) REFERENCES `baitari_users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 8. VACCINATIONS
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_vaccinations` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pet_id`        INT UNSIGNED,
  `herd_id`       INT UNSIGNED,
  `vet_id`        INT UNSIGNED,
  `vaccine_name`  VARCHAR(150) NOT NULL,
  `given_date`    DATE NOT NULL,
  `next_due`      DATE,
  `batch_no`      VARCHAR(60),
  `notes`         TEXT,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`pet_id`) REFERENCES `baitari_pets`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`herd_id`) REFERENCES `baitari_herds`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 9. REVIEWS
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_reviews` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `appt_id`       INT UNSIGNED NOT NULL UNIQUE,
  `client_id`     INT UNSIGNED NOT NULL,
  `vet_id`        INT UNSIGNED NOT NULL,
  `rating`        TINYINT UNSIGNED NOT NULL CHECK (`rating` BETWEEN 1 AND 5),
  `comment`       TEXT,
  `is_visible`    TINYINT(1) DEFAULT 1,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`appt_id`) REFERENCES `baitari_appointments`(`id`),
  FOREIGN KEY (`client_id`) REFERENCES `baitari_users`(`id`),
  FOREIGN KEY (`vet_id`) REFERENCES `baitari_users`(`id`),
  INDEX idx_vet_rating (`vet_id`, `rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 10. NOTIFICATIONS
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_notifications` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`       INT UNSIGNED NOT NULL,
  `type`          ENUM('appointment','emergency','reminder','system','review') NOT NULL,
  `title`         VARCHAR(200) NOT NULL,
  `body`          TEXT,
  `data_json`     JSON,
  `is_read`       TINYINT(1) DEFAULT 0,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `baitari_users`(`id`) ON DELETE CASCADE,
  INDEX idx_user_unread (`user_id`, `is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- 11. SESSIONS (PHP session store)
-- --------------------------------------------
CREATE TABLE IF NOT EXISTS `baitari_sessions` (
  `id`          VARCHAR(128) NOT NULL,
  `user_id`     INT UNSIGNED,
  `ip`          VARCHAR(45),
  `user_agent`  VARCHAR(255),
  `payload`     TEXT,
  `last_active` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX idx_user (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------
-- SAMPLE DATA
-- --------------------------------------------
INSERT INTO `baitari_users` (`name`,`phone`,`email`,`password_hash`,`role`,`wilaya`,`commune`,`lat`,`lng`) VALUES
('أمين بلقاسم','0550001122','admin@baitari.dz','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','admin','Alger','Alger Centre',36.7538,3.0588),
('سارة بن علي','0661112233','sara@example.dz','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','pet_owner','Alger','Bab El Oued',36.7692,3.0596),
('محمد فرحات','0770003344','farm@example.dz','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','farm_owner','Blida','Boufarik',36.5711,2.9203),
('د. كريم حداد','0550554455','vet@example.dz','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','vet','Alger','Hydra',36.7436,3.0453);

SET FOREIGN_KEY_CHECKS = 1;
