CREATE TABLE application_events (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  application_id INTEGER NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
  status         TEXT    NOT NULL,
  message        TEXT,
  actor          TEXT    NOT NULL DEFAULT 'system',
  created_at     TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE applications (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  reference     TEXT    NOT NULL UNIQUE,
  user_id       INTEGER REFERENCES users(id) ON DELETE SET NULL,
  full_name     TEXT    NOT NULL,
  phone         TEXT    NOT NULL,
  email         TEXT    NOT NULL,
  nationality   TEXT,
  travel_date   TEXT,
  package_id    TEXT    NOT NULL,
  package_label TEXT    NOT NULL,
  amount_aed    REAL    NOT NULL DEFAULT 0,
  amount_egp    REAL    NOT NULL DEFAULT 0,
  payment_method TEXT,
  status        TEXT    NOT NULL DEFAULT 'new',
  admin_note    TEXT,
  passport_file TEXT,
  photo_file    TEXT,
  receipt_file  TEXT,
  consent       INTEGER NOT NULL DEFAULT 1,
  created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
  updated_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE faqs (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  question   TEXT    NOT NULL,
  answer     TEXT    NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  active     INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE packages (
  id         TEXT    PRIMARY KEY,
  label      TEXT    NOT NULL,
  duration   TEXT    NOT NULL,
  price_aed  REAL    NOT NULL DEFAULT 0,
  price_egp  REAL    NOT NULL DEFAULT 0,
  note       TEXT,
  accent     INTEGER NOT NULL DEFAULT 0,
  features   TEXT    NOT NULL DEFAULT '[]',
  sort_order INTEGER NOT NULL DEFAULT 0,
  active     INTEGER NOT NULL DEFAULT 1,
  updated_at TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE payment_methods (
  id         TEXT    PRIMARY KEY,
  code       TEXT    NOT NULL UNIQUE,
  title      TEXT    NOT NULL,
  subtitle   TEXT,
  currency   TEXT    NOT NULL DEFAULT 'AED',
  details    TEXT    NOT NULL DEFAULT '{}',
  sort_order INTEGER NOT NULL DEFAULT 0,
  active     INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE sessions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  token_hash TEXT    NOT NULL UNIQUE,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  user_agent TEXT,
  ip         TEXT,
  created_at TEXT    NOT NULL DEFAULT (datetime('now')),
  expires_at TEXT    NOT NULL
);

CREATE TABLE sqlite_sequence(name,seq);

CREATE TABLE users (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  email          TEXT    NOT NULL UNIQUE,
  phone          TEXT    NOT NULL UNIQUE,
  full_name      TEXT    NOT NULL,
  nationality    TEXT,
  password_hash  TEXT    NOT NULL,
  role           TEXT    NOT NULL DEFAULT 'customer',
  phone_verified INTEGER NOT NULL DEFAULT 0,
  email_verified INTEGER NOT NULL DEFAULT 0,
  created_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  updated_at     TEXT    NOT NULL DEFAULT (datetime('now')),
  last_login_at  TEXT
);

CREATE TABLE verification_codes (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  channel     TEXT    NOT NULL,
  target      TEXT    NOT NULL,
  code_hash   TEXT    NOT NULL,
  purpose     TEXT    NOT NULL DEFAULT 'verify',
  expires_at  TEXT    NOT NULL,
  consumed_at TEXT,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_apps_status ON applications(status);

CREATE INDEX idx_apps_user   ON applications(user_id);

CREATE INDEX idx_codes_user ON verification_codes(user_id, channel);

CREATE INDEX idx_events_app ON application_events(application_id);

CREATE INDEX idx_sessions_user ON sessions(user_id);