-- =============================================================================
-- HLM Admin Panel — Full database schema (MySQL / MariaDB)
-- Import this file in phpMyAdmin or: mysql -u USER -p DATABASE < schema.sql
--
-- To wipe and reload everything (mobile app needs users.password_hash +
-- app_access_tokens): use hlm_full_import.sql instead.
--
-- Default Super Admin (change password after first login):
--   Email:    admin@example.com
--   Password: admin
--   Name:     admin
-- Password is stored as PHP password_hash() bcrypt — safe to replace via admin UI.
-- =============================================================================


-- ---------------------------------------------------------------------------
-- Roles (reference)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE
);

INSERT IGNORE INTO roles (name) VALUES
('Super Admin'),
('Church Admin'),
('Media Admin'),
('Finance Admin'),
('Course Admin'),
('Prayer Team Admin');

CREATE TABLE IF NOT EXISTS member_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  member_type VARCHAR(80) NOT NULL DEFAULT 'member',
  ministry_group VARCHAR(120) DEFAULT '',
  notes TEXT,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- YouTube / sermons
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS youtube_playlists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  playlist_id VARCHAR(120) NOT NULL UNIQUE,
  category_tag VARCHAR(80) NOT NULL,
  title VARCHAR(180) DEFAULT '',
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS youtube_videos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  video_id VARCHAR(120) NOT NULL UNIQUE,
  watch_url TEXT NULL,
  playlist_id VARCHAR(120) DEFAULT '',
  title VARCHAR(255) NOT NULL,
  thumbnail TEXT,
  description TEXT,
  duration VARCHAR(24) DEFAULT '',
  published_at DATETIME,
  category_tag VARCHAR(80) DEFAULT '',
  preacher VARCHAR(120) DEFAULT 'HLM',
  series_name VARCHAR(120) DEFAULT '',
  views_count INT NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS live_stream_status (
  id INT AUTO_INCREMENT PRIMARY KEY,
  is_live TINYINT(1) NOT NULL DEFAULT 0,
  video_id VARCHAR(120) DEFAULT '',
  title VARCHAR(255) DEFAULT '',
  thumbnail TEXT,
  start_time DATETIME NULL,
  updated_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS devotionals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  content TEXT NOT NULL,
  published_at DATETIME NULL
);

-- ---------------------------------------------------------------------------
-- Events
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  event_type VARCHAR(80) DEFAULT 'Service',
  start_date DATETIME NOT NULL,
  end_date DATETIME NULL,
  location VARCHAR(180) DEFAULT '',
  banner_url TEXT,
  registration_link TEXT,
  expected_attendance INT NOT NULL DEFAULT 0,
  actual_attendance INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS event_attendees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_id INT NOT NULL,
  user_id INT NOT NULL,
  status VARCHAR(40) DEFAULT 'registered',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- Donations
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS donations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  category VARCHAR(80) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_method VARCHAR(80) DEFAULT '',
  status VARCHAR(40) DEFAULT 'success',
  transaction_ref VARCHAR(140) DEFAULT '',
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS donation_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE
);

INSERT IGNORE INTO donation_categories (name) VALUES
('tithe'), ('offering'), ('seed'), ('partnership'), ('special donations'), ('campaign');

-- ---------------------------------------------------------------------------
-- Prayer
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS prayer_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requester_name VARCHAR(120) DEFAULT 'Anonymous',
  message TEXT NOT NULL,
  category VARCHAR(80) DEFAULT 'General',
  is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
  assigned_to VARCHAR(120) DEFAULT '',
  status VARCHAR(40) DEFAULT 'pending',
  testimony_conversion TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
);

-- ---------------------------------------------------------------------------
-- Courses
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  category VARCHAR(120) DEFAULT '',
  description TEXT,
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS course_lessons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_id INT NOT NULL,
  title VARCHAR(180) NOT NULL,
  lesson_text LONGTEXT,
  video_url TEXT,
  pdf_url TEXT,
  quiz_json LONGTEXT,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS course_enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_id INT NOT NULL,
  user_id INT NOT NULL,
  progress_percent INT NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- ---------------------------------------------------------------------------
-- Notifications
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  body TEXT NOT NULL,
  target_group VARCHAR(120) DEFAULT 'all',
  schedule_at DATETIME NULL,
  status VARCHAR(40) DEFAULT 'queued',
  delivery_report LONGTEXT,
  created_at DATETIME NOT NULL
);

-- ---------------------------------------------------------------------------
-- Attendance
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attendance_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  event_id INT NOT NULL,
  checkin_method VARCHAR(40) NOT NULL DEFAULT 'manual',
  checkin_time DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS app_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value LONGTEXT
);

CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  image_url TEXT NOT NULL,
  target_url TEXT,
  is_active TINYINT(1) NOT NULL DEFAULT 1
);

-- ---------------------------------------------------------------------------
-- App content (support JSON, terms, privacy)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content_key VARCHAR(120) NOT NULL UNIQUE,
  title VARCHAR(180) DEFAULT '',
  content LONGTEXT,
  updated_at DATETIME NULL
);

INSERT IGNORE INTO app_content (content_key, title, content, updated_at) VALUES
('support', 'Support', '{\"emails\":[\"support@heavenliving.org\"],\"phones\":[\"+2348000000000\"],\"contacts\":[\"Main Office\"],\"form_enabled\":true}', NOW()),
('terms', 'Terms of Service', 'Add your terms of service here.', NOW()),
('privacy', 'Privacy Policy', 'Add your privacy policy here.', NOW());

CREATE TABLE IF NOT EXISTS support_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL,
  subject VARCHAR(180) DEFAULT '',
  message TEXT NOT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL
);

