-- ============================================================
-- KKA — World Children Organization | Database Schema (SQLite)
-- Back-end: Node.js + Express + sql.js (SQLite WASM engine)
-- File: data/kka.sqlite  (persisted to disk on every write)
-- ============================================================

-- Contact form submissions (contact.html)
CREATE TABLE IF NOT EXISTS contact_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  subject TEXT,
  message TEXT NOT NULL,
  ip TEXT,
  user_agent TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Donations (donate.html) — stored as pledges; payment completed offline/out-of-band
CREATE TABLE IF NOT EXISTS donations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  donation_type TEXT NOT NULL,          -- One-Time | Monthly Recurring | Sponsor a Child | Zakat/Sadaqah | Campaign-Based
  amount_taka REAL NOT NULL,            -- amount in Bangladeshi Taka (৳)
  currency TEXT NOT NULL DEFAULT 'BDT (৳)',
  allocation TEXT,                      -- program allocation (General Fund, Child Education, …)
  payment_method TEXT,                  -- Stripe | PayPal | bKash | Nagad | SSL Commerz
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  country TEXT,
  message TEXT,
  is_anonymous INTEGER NOT NULL DEFAULT 0,
  ip TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Volunteer / Get-Involved registrations (get-involved.html)
CREATE TABLE IF NOT EXISTS volunteers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  location TEXT,
  area_of_interest TEXT,                -- Education & Teaching | Healthcare Support | …
  bio TEXT,
  ip TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Newsletter subscribers (index.html)
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT,
  email TEXT NOT NULL UNIQUE,
  ip TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Admin users (login for /admin dashboard)
CREATE TABLE IF NOT EXISTS admins (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL          -- SHA-256 (demo); upgrade to bcrypt/argon2 in production
);

-- Admin sessions (cookie token)
CREATE TABLE IF NOT EXISTS sessions (
  token TEXT PRIMARY KEY,
  admin_id INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Default admin (seeded on first boot):
--   username: admin
--   password: kka-admin-1976   (CHANGE THIS IN PRODUCTION via env KKA_ADMIN_PASSWORD)


-- v2 additions -------------------------------------------------------------

-- Public member registration (membership.html)
CREATE TABLE IF NOT EXISTS members (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  date_of_birth TEXT,
  gender TEXT,
  membership_type TEXT NOT NULL DEFAULT 'Basic',
  photo_path TEXT,                      -- /uploads/<file> (optional photo upload)
  status TEXT NOT NULL DEFAULT 'pending',  -- pending | approved | rejected
  ip TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Content management (posts / blogs / gallery / media / publications)
CREATE TABLE IF NOT EXISTS posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL, category TEXT NOT NULL DEFAULT 'General',
  body TEXT, image_path TEXT, author TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS blogs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL, excerpt TEXT, body TEXT, image_path TEXT, author TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS gallery_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT, image_path TEXT NOT NULL, caption TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS media_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL, media_type TEXT NOT NULL DEFAULT 'video',
  url TEXT NOT NULL, description TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS publications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL, authors TEXT, year TEXT, file_path TEXT, summary TEXT,
  status TEXT NOT NULL DEFAULT 'published',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- admins gained a role column (super-admin | admin | social-admin)
--   ALTER TABLE admins ADD COLUMN role TEXT NOT NULL DEFAULT 'admin';