-- ============================================================
--  Social Community App — MySQL / MariaDB schema
--  Pure PHP + MySQL (no framework).  Import this file first.
--  Charset utf8mb4 so emoji + Arabic work everywhere.
-- ============================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS admin_audit_log, admins, settings, push_campaigns, notifications,
  post_hashtags, hashtags, reports, messages, conversation_participants, conversations,
  group_members, `groups`, saves, post_tags, post_shares, comment_likes, post_comments,
  post_likes, post_media, posts, blocks, follows, friendships, otp_codes, auth_tokens, users;

-- ---------------- users & auth ----------------
CREATE TABLE users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(80)  NOT NULL,
  username      VARCHAR(30)  NOT NULL,
  email         VARCHAR(160) NOT NULL,
  phone         VARCHAR(30)  DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  avatar_path   VARCHAR(255) DEFAULT NULL,
  cover_path    VARCHAR(255) DEFAULT NULL,
  bio           VARCHAR(300) DEFAULT NULL,
  link          VARCHAR(200) DEFAULT NULL,
  gender        ENUM('male','female','other','private') DEFAULT 'private',
  birth_date    DATE DEFAULT NULL,
  country       VARCHAR(80)  DEFAULT NULL,
  city          VARCHAR(80)  DEFAULT NULL,
  status        ENUM('active','pending','banned','deactivated') NOT NULL DEFAULT 'active',
  is_verified   TINYINT(1)   NOT NULL DEFAULT 0,
  role          ENUM('user','moderator','admin') NOT NULL DEFAULT 'user',
  privacy_profile ENUM('public','friends','private') NOT NULL DEFAULT 'public',
  strikes       TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_seen_at  DATETIME DEFAULT NULL,
  fcm_token     VARCHAR(255) DEFAULT NULL,
  email_verified_at DATETIME DEFAULT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_username (username),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_status (status),
  KEY idx_users_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE auth_tokens (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,               -- sha256 of the bearer token
  device     VARCHAR(120) DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_token_hash (token_hash),
  KEY idx_token_user (user_id),
  CONSTRAINT fk_token_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE otp_codes (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(160) NOT NULL,
  code       CHAR(6) NOT NULL,
  purpose    ENUM('verify_email','reset_password') NOT NULL,
  expires_at DATETIME NOT NULL,
  attempts   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  used       TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_otp_email (email, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- social graph ----------------
CREATE TABLE friendships (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  requester_id INT UNSIGNED NOT NULL,
  receiver_id  INT UNSIGNED NOT NULL,
  status      ENUM('pending','accepted','blocked') NOT NULL DEFAULT 'pending',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_friend_pair (requester_id, receiver_id),
  KEY idx_friend_receiver (receiver_id, status),
  CONSTRAINT fk_fr_req FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_fr_rec FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE follows (
  follower_id INT UNSIGNED NOT NULL,
  followed_id INT UNSIGNED NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (follower_id, followed_id),
  CONSTRAINT fk_fl_f FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_fl_d FOREIGN KEY (followed_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE blocks (
  blocker_id INT UNSIGNED NOT NULL,
  blocked_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (blocker_id, blocked_id),
  CONSTRAINT fk_bk_a FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_bk_b FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- posts & feed ----------------
CREATE TABLE posts (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id       INT UNSIGNED NOT NULL,
  group_id      INT UNSIGNED DEFAULT NULL,
  audience      ENUM('public','friends','group','only_me') NOT NULL DEFAULT 'public',
  body          TEXT,
  shared_post_id INT UNSIGNED DEFAULT NULL,
  status        ENUM('published','hidden','removed') NOT NULL DEFAULT 'published',
  is_pinned     TINYINT(1) NOT NULL DEFAULT 0,
  comments_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_posts_user (user_id, created_at),
  KEY idx_posts_group (group_id, created_at),
  KEY idx_posts_status (status, created_at),
  CONSTRAINT fk_post_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_post_share FOREIGN KEY (shared_post_id) REFERENCES posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post_media (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id  INT UNSIGNED NOT NULL,
  type     ENUM('image','video') NOT NULL,
  path     VARCHAR(255) NOT NULL,
  thumb_path VARCHAR(255) DEFAULT NULL,
  duration SMALLINT UNSIGNED DEFAULT NULL,
  width    SMALLINT UNSIGNED DEFAULT NULL,
  height   SMALLINT UNSIGNED DEFAULT NULL,
  order_no TINYINT UNSIGNED NOT NULL DEFAULT 0,
  KEY idx_media_post (post_id),
  CONSTRAINT fk_media_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post_likes (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id  INT UNSIGNED NOT NULL,
  user_id  INT UNSIGNED NOT NULL,
  reaction ENUM('like','love','wow','haha','sad','angry') NOT NULL DEFAULT 'like',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_like (post_id, user_id),
  KEY idx_like_user (user_id),
  CONSTRAINT fk_like_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_like_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post_comments (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id  INT UNSIGNED NOT NULL,
  user_id  INT UNSIGNED NOT NULL,
  parent_id INT UNSIGNED DEFAULT NULL,
  body     TEXT NOT NULL,
  media_path VARCHAR(255) DEFAULT NULL,
  status   ENUM('published','hidden','removed') NOT NULL DEFAULT 'published',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_comments_post (post_id, created_at),
  CONSTRAINT fk_cmt_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_cmt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_cmt_parent FOREIGN KEY (parent_id) REFERENCES post_comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE comment_likes (
  comment_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (comment_id, user_id),
  CONSTRAINT fk_cl_c FOREIGN KEY (comment_id) REFERENCES post_comments(id) ON DELETE CASCADE,
  CONSTRAINT fk_cl_u FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post_shares (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id  INT UNSIGNED NOT NULL,
  user_id  INT UNSIGNED NOT NULL,
  quote    VARCHAR(400) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_share_post (post_id),
  CONSTRAINT fk_sh_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_sh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post_tags (
  post_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, user_id),
  CONSTRAINT fk_pt_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_pt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE saves (
  user_id    INT UNSIGNED NOT NULL,
  post_id    INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, post_id),
  CONSTRAINT fk_sv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_sv_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- groups ----------------
CREATE TABLE `groups` (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(120) NOT NULL,
  description TEXT,
  cover_path  VARCHAR(255) DEFAULT NULL,
  avatar_path VARCHAR(255) DEFAULT NULL,
  privacy     ENUM('public','private','secret') NOT NULL DEFAULT 'public',
  join_mode   ENUM('open','approval','invite') NOT NULL DEFAULT 'open',
  post_permission ENUM('all_members','admins') NOT NULL DEFAULT 'all_members',
  rules       TEXT,
  owner_id    INT UNSIGNED NOT NULL,
  member_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_verified TINYINT(1) NOT NULL DEFAULT 0,
  status      ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active',
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_group_slug (slug),
  KEY idx_group_privacy (privacy, status),
  CONSTRAINT fk_grp_owner FOREIGN KEY (owner_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE group_members (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id  INT UNSIGNED NOT NULL,
  user_id   INT UNSIGNED NOT NULL,
  role      ENUM('owner','moderator','member') NOT NULL DEFAULT 'member',
  status    ENUM('pending','active','banned','left') NOT NULL DEFAULT 'active',
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_member (group_id, user_id),
  KEY idx_member_user (user_id),
  CONSTRAINT fk_gm_group FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
  CONSTRAINT fk_gm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- chat ----------------
CREATE TABLE conversations (
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type           ENUM('direct','group') NOT NULL DEFAULT 'direct',
  title          VARCHAR(100) DEFAULT NULL,
  avatar_path    VARCHAR(255) DEFAULT NULL,
  creator_id     INT UNSIGNED DEFAULT NULL,
  last_message_at DATETIME DEFAULT NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_conv_last (last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE conversation_participants (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT UNSIGNED NOT NULL,
  user_id         INT UNSIGNED NOT NULL,
  role            ENUM('owner','member') NOT NULL DEFAULT 'member',
  last_read_at    DATETIME DEFAULT NULL,
  muted           TINYINT(1) NOT NULL DEFAULT 0,
  joined_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_participant (conversation_id, user_id),
  KEY idx_part_user (user_id),
  CONSTRAINT fk_cp_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_cp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE messages (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT UNSIGNED NOT NULL,
  sender_id       INT UNSIGNED NOT NULL,
  type            ENUM('text','image','video','file','voice','system') NOT NULL DEFAULT 'text',
  body            TEXT,
  attachment_path VARCHAR(255) DEFAULT NULL,
  duration        SMALLINT UNSIGNED DEFAULT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at      DATETIME DEFAULT NULL,
  KEY idx_msg_conv (conversation_id, id),
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
  CONSTRAINT fk_msg_user FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- moderation & discovery ----------------
CREATE TABLE reports (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  target_type ENUM('post','comment','user','group') NOT NULL,
  target_id  INT UNSIGNED NOT NULL,
  reporter_id INT UNSIGNED NOT NULL,
  reason     VARCHAR(80) NOT NULL,
  details    VARCHAR(500) DEFAULT NULL,
  status     ENUM('open','reviewed','actioned','dismissed') NOT NULL DEFAULT 'open',
  handled_by INT UNSIGNED DEFAULT NULL,
  handled_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_report_status (status, created_at),
  KEY idx_report_target (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE hashtags (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(60) NOT NULL,
  posts_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_trending TINYINT(1) NOT NULL DEFAULT 0,
  is_banned   TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_hashtag (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE post_hashtags (
  post_id    INT UNSIGNED NOT NULL,
  hashtag_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (post_id, hashtag_id),
  CONSTRAINT fk_ph_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  CONSTRAINT fk_ph_tag  FOREIGN KEY (hashtag_id) REFERENCES hashtags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE banned_words (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  word       VARCHAR(80) NOT NULL,
  action     ENUM('queue','hide','remove') NOT NULL DEFAULT 'queue',
  scope      ENUM('posts','comments','messages','all') NOT NULL DEFAULT 'all',
  hits       INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_banned_word (word)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notifications (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id   INT UNSIGNED NOT NULL,
  actor_id  INT UNSIGNED DEFAULT NULL,
  type      VARCHAR(40) NOT NULL,             -- like, comment, friend_request, group_post...
  title     VARCHAR(120) DEFAULT NULL,
  body      VARCHAR(300) DEFAULT NULL,
  data_json VARCHAR(500) DEFAULT NULL,        -- {"post_id":12} deep-link payload
  read_at   DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_notif_user (user_id, read_at, created_at),
  CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE push_campaigns (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title     VARCHAR(120) NOT NULL,
  body      VARCHAR(300) NOT NULL,
  image_path VARCHAR(255) DEFAULT NULL,
  audience  ENUM('all','country','group','users') NOT NULL DEFAULT 'all',
  target    VARCHAR(120) DEFAULT NULL,        -- country name / group id / csv of user ids
  sent_at   DATETIME DEFAULT NULL,
  sent_by   INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  `key`   VARCHAR(60) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------- admin ----------------
CREATE TABLE admins (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(80) NOT NULL,
  email         VARCHAR(160) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('super','moderator','editor','support') NOT NULL DEFAULT 'moderator',
  status        ENUM('active','disabled') NOT NULL DEFAULT 'active',
  last_login_at DATETIME DEFAULT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_audit_log (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id  INT UNSIGNED DEFAULT NULL,
  action    VARCHAR(60) NOT NULL,
  entity    VARCHAR(40) DEFAULT NULL,
  entity_id INT UNSIGNED DEFAULT NULL,
  details   VARCHAR(400) DEFAULT NULL,
  ip        VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;