-- =====================================================
-- خليجنا - بنية قاعدة البيانات
-- نفّذ هذا الملف مرة واحدة من phpMyAdmin > استيراد
-- =====================================================

CREATE TABLE IF NOT EXISTS users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  phone       VARCHAR(20)  NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  password    VARCHAR(255) NOT NULL,
  about       VARCHAR(255) DEFAULT 'متاح',
  avatar      VARCHAR(255) DEFAULT NULL,
  token       VARCHAR(64)  DEFAULT NULL,
  last_seen   DATETIME     DEFAULT NULL,
  is_online   TINYINT(1)   DEFAULT 0,
  created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  sender_id    INT NOT NULL,
  receiver_id  INT NOT NULL,
  body         TEXT NOT NULL,
  type         VARCHAR(20) DEFAULT 'text',   -- text | call_log
  status       VARCHAR(20) DEFAULT 'sent',   -- sent | delivered | read
  created_at   DATETIME    DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pair (sender_id, receiver_id),
  INDEX idx_receiver (receiver_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS calls (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  caller_id   INT NOT NULL,
  callee_id   INT NOT NULL,
  status      VARCHAR(20) DEFAULT 'ringing', -- ringing | accepted | rejected | ended | missed
  offer       LONGTEXT DEFAULT NULL,
  answer      LONGTEXT DEFAULT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_callee (callee_id, status),
  INDEX idx_caller (caller_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ice_candidates (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  call_id     INT NOT NULL,
  sender_id   INT NOT NULL,
  candidate   LONGTEXT NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_call (call_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;