-- TPMS Database Schema
-- Tire Pressure Monitoring System for Mining Dump Trucks
--
-- IMPORTANT (cPanel):
--   - Do NOT run `CREATE DATABASE` here. cPanel users can't create databases.
--   - First create the DB in cPanel "MySQL Databases" tool. The name will be
--     prefixed with your cPanel username, e.g. `irashine_tpms_db`.
--   - Then in phpMyAdmin SELECT that database from the left panel, and
--     IMPORT this file. The `USE` statement below is a no-op if you're
--     already in that DB; if you import via CLI use the `-D` flag instead.

USE `irashine_tpms_db`;

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

CREATE TABLE IF NOT EXISTS trucks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  truck_code VARCHAR(20) NOT NULL UNIQUE,
  description VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tpms_readings (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  truck_id INT NOT NULL,

  -- 12 sensor channels: 6 tires x (pressure, temperature)
  -- value1 = Tire 1 Pressure,  value2 = Tire 1 Temperature
  -- value3 = Tire 2 Pressure,  value4 = Tire 2 Temperature
  -- value5 = Tire 3 Pressure,  value6 = Tire 3 Temperature
  -- value7 = Tire 4 Pressure,  value8 = Tire 4 Temperature
  -- value9 = Tire 5 Pressure,  value10 = Tire 5 Temperature
  -- value11 = Tire 6 Pressure, value12 = Tire 6 Temperature
  value1  DECIMAL(6,2) DEFAULT NULL,
  value2  DECIMAL(6,2) DEFAULT NULL,
  value3  DECIMAL(6,2) DEFAULT NULL,
  value4  DECIMAL(6,2) DEFAULT NULL,
  value5  DECIMAL(6,2) DEFAULT NULL,
  value6  DECIMAL(6,2) DEFAULT NULL,
  value7  DECIMAL(6,2) DEFAULT NULL,
  value8  DECIMAL(6,2) DEFAULT NULL,
  value9  DECIMAL(6,2) DEFAULT NULL,
  value10 DECIMAL(6,2) DEFAULT NULL,
  value11 DECIMAL(6,2) DEFAULT NULL,
  value12 DECIMAL(6,2) DEFAULT NULL,

  recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_truck_time (truck_id, recorded_at),
  CONSTRAINT fk_reading_truck FOREIGN KEY (truck_id) REFERENCES trucks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tpms_alerts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  reading_id BIGINT NOT NULL,
  truck_id INT NOT NULL,
  tire_index TINYINT NOT NULL,        -- 1..6
  alert_type ENUM('LOW_PRESSURE','HIGH_PRESSURE','HIGH_TEMP','SENSOR_FAULT') NOT NULL,
  severity ENUM('INFO','WARNING','CRITICAL') NOT NULL DEFAULT 'WARNING',
  message VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_truck_time (truck_id, created_at),
  CONSTRAINT fk_alert_reading FOREIGN KEY (reading_id) REFERENCES tpms_readings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed trucks (matches the reference image: T103)
INSERT INTO trucks (truck_code, description) VALUES
  ('T101', 'دامپتراک ۱۰۱ - معدن ۱'),
  ('T102', 'دامپتراک ۱۰۲ - معدن ۱'),
  ('T103', 'دامپتراک ۱۰۳ - معدن ۲'),
  ('T104', 'دامپتراک ۱۰۴ - معدن ۲'),
  ('T105', 'دامپتراک ۱۰۵ - معدن ۳')
ON DUPLICATE KEY UPDATE description = VALUES(description);