PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  phone TEXT,
  password TEXT NOT NULL,
  role TEXT NOT NULL CHECK(role IN ('admin','customer')),
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive')),
  email_verified BOOLEAN DEFAULT 0,
  business_type TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS services (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  service_name TEXT NOT NULL,
  slug TEXT UNIQUE,
  cac_fee REAL NOT NULL DEFAULT 0,
  service_fee REAL NOT NULL DEFAULT 0,
  base_price REAL NOT NULL DEFAULT 0,
  commission_percent REAL DEFAULT 10.0,
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive')),
  created_at TEXT NOT NULL
);

-- Seed Services
INSERT OR IGNORE INTO services (service_name, slug, cac_fee, service_fee, base_price, created_at) VALUES
('Business Name Registration', 'business_name', 10000, 15000, 25000, datetime('now')),
('Company Registration (LTD)', 'limited_company', 25000, 35000, 60000, datetime('now')),
('NGO / Foundation', 'ngo', 50000, 70000, 120000, datetime('now')),
('Trademark Registration', 'trademark', 30000, 70000, 100000, datetime('now')),
('TIN Registration', 'tin', 0, 15000, 15000, datetime('now')),
('SCUML Registration', 'scuml', 0, 30000, 30000, datetime('now'));


CREATE TABLE IF NOT EXISTS packages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  discount_type TEXT NOT NULL DEFAULT 'none',
  discount_value REAL NOT NULL DEFAULT 0,
  discount_tiers_json TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS package_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  package_id INTEGER NOT NULL,
  service_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  UNIQUE(package_id, service_id),
  FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE CASCADE,
  FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS applications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  service_id INTEGER NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('draft','submitted','processing','approved','rejected')),
  total_price REAL DEFAULT 0,
  cac_fee REAL DEFAULT 0,
  service_fee REAL DEFAULT 0,
  admin_notes TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(service_id) REFERENCES services(id)
);

CREATE TABLE IF NOT EXISTS application_details (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  application_id INTEGER NOT NULL UNIQUE,
  proposed_name_1 TEXT,
  proposed_name_2 TEXT,
  nature_of_business TEXT,
  business_address TEXT,
  date_of_birth TEXT,
  nationality TEXT,
  id_type TEXT,
  id_number TEXT,
  id_document_path TEXT,
  passport_path TEXT,
  full_name TEXT,
  email TEXT,
  phone TEXT,
  FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS application_addons (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  application_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  price REAL NOT NULL,
  FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  application_id INTEGER NOT NULL,
  application_type TEXT,
  amount REAL NOT NULL,
  payment_reference TEXT,
  receipt_path TEXT,
  currency TEXT DEFAULT 'NGN',
  payment_method TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','confirmed','rejected')),
  created_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS certificates (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  application_id INTEGER NOT NULL UNIQUE,
  certificate_path TEXT NOT NULL,
  uploaded_at TEXT NOT NULL,
  FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS notifications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  type TEXT NOT NULL,
  title TEXT,
  body TEXT,
  data_json TEXT,
  status TEXT NOT NULL DEFAULT 'unread',
  created_at TEXT NOT NULL,
  read_at TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS notification_prefs (
  user_id INTEGER PRIMARY KEY,
  inapp_enabled INTEGER NOT NULL DEFAULT 1,
  email_enabled INTEGER NOT NULL DEFAULT 1,
  types_json TEXT,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  actor_user_id INTEGER,
  action TEXT NOT NULL,
  entity TEXT,
  entity_id INTEGER,
  payload TEXT,
  ip TEXT,
  created_at TEXT NOT NULL,
  FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT,
  category TEXT DEFAULT 'general',
  type TEXT DEFAULT 'string',
  description TEXT,
  updated_at TEXT
);

CREATE TABLE IF NOT EXISTS user_profiles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL UNIQUE,
  full_name TEXT,
  email TEXT,
  phone TEXT,
  date_of_birth TEXT,
  nationality TEXT DEFAULT 'Nigerian',
  gender TEXT,
  business_name TEXT,
  business_address TEXT,
  nature_of_business TEXT,
  id_type TEXT,
  id_number TEXT,
  id_document_path TEXT,
  passport_path TEXT,
  signature_path TEXT,
  residential_address TEXT,
  city TEXT,
  state TEXT,
  country TEXT DEFAULT 'Nigeria',
  next_of_kin_name TEXT,
  next_of_kin_phone TEXT,
  next_of_kin_relationship TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- SEO System
CREATE TABLE IF NOT EXISTS locations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  state TEXT,
  status TEXT DEFAULT 'active',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS seo_landings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  service_id INTEGER NOT NULL,
  location_id INTEGER NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  title TEXT,
  description TEXT,
  h1_title TEXT,
  content_json TEXT,
  status TEXT DEFAULT 'active',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
);

-- Add SEO columns to services if they don't exist (handled by the app during init usually, but good for schema)
ALTER TABLE services ADD COLUMN seo_title TEXT;
ALTER TABLE services ADD COLUMN seo_description TEXT;
ALTER TABLE services ADD COLUMN seo_keywords TEXT;

-- Seed initial locations
INSERT OR IGNORE INTO locations (name, slug, state) VALUES
('Lagos', 'lagos', 'Lagos'),
('Abuja', 'abuja', 'FCT'),
('Port Harcourt', 'port-harcourt', 'Rivers'),
('Ibadan', 'ibadan', 'Oyo'),
('Kano', 'kano', 'Kano'),
('Enugu', 'enugu', 'Enugu'),
('Benin City', 'benin-city', 'Edo'),
('Abeokuta', 'abeokuta', 'Ogun');

-- Chat System
CREATE TABLE IF NOT EXISTS chat_threads (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    guest_id TEXT UNIQUE,
    last_message_at TEXT DEFAULT (datetime('now')),
    last_active_at TEXT DEFAULT (datetime('now')),
    status TEXT DEFAULT 'active',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(user_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id INTEGER NOT NULL,
    sender_id INTEGER,
    guest_id TEXT,
    is_admin INTEGER DEFAULT 0,
    body TEXT NOT NULL,
    attachment_path TEXT,
    status TEXT DEFAULT 'sent',
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    read_at TEXT,
    FOREIGN KEY (thread_id) REFERENCES chat_threads(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS chat_typing (
    user_id INTEGER UNIQUE,
    guest_id TEXT UNIQUE,
    is_typing INTEGER DEFAULT 0,
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
