-- ============================================================
-- مخطّط قاعدة البيانات (SQLite) لمتجر المنتجات الرقمية
-- ============================================================

CREATE TABLE IF NOT EXISTS settings (
  key   TEXT PRIMARY KEY,
  value TEXT
);

CREATE TABLE IF NOT EXISTS products (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT NOT NULL,
  category    TEXT DEFAULT 'عام',
  price       REAL DEFAULT 0,
  old_price   REAL DEFAULT 0,
  rating      REAL DEFAULT 5,
  featured    INTEGER DEFAULT 0,
  badge       TEXT DEFAULT '',
  emoji       TEXT DEFAULT '📦',
  description TEXT DEFAULT '',
  created_at  TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS services (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT NOT NULL,
  emoji       TEXT DEFAULT '✨',
  description TEXT DEFAULT '',
  created_at  TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS customers (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT NOT NULL,
  email      TEXT UNIQUE,
  phone      TEXT DEFAULT '',
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER,
  total       REAL DEFAULT 0,
  status      TEXT DEFAULT 'جديد',
  items_json  TEXT DEFAULT '[]',
  created_at  TEXT DEFAULT (datetime('now')),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE IF NOT EXISTS messages (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT DEFAULT 'زائر',
  email      TEXT DEFAULT '',
  body       TEXT NOT NULL,
  source     TEXT DEFAULT 'chat',  -- chat | contact
  created_at TEXT DEFAULT (datetime('now'))
);
