CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user', 'admin', 'super_admin') NOT NULL DEFAULT 'user',
  status ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
  email_verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT sessions_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX sessions_user_idx (user_id),
  INDEX sessions_expiry_idx (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT reset_tokens_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX reset_tokens_expiry_idx (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS profiles (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL UNIQUE,
  username VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL DEFAULT '',
  role_title VARCHAR(160) NOT NULL DEFAULT '',
  company VARCHAR(160) NOT NULL DEFAULT '',
  bio TEXT NOT NULL,
  phone VARCHAR(80) NOT NULL DEFAULT '',
  email VARCHAR(190) NOT NULL DEFAULT '',
  website VARCHAR(500) NOT NULL DEFAULT '',
  location VARCHAR(300) NOT NULL DEFAULT '',
  avatar_path VARCHAR(500) NOT NULL DEFAULT '',
  links JSON NOT NULL,
  social_profiles JSON NOT NULL,
  theme ENUM('luxe', 'vivid', 'executive', 'pure', 'motion', 'creator') NOT NULL DEFAULT 'luxe',
  primary_color CHAR(7) NOT NULL DEFAULT '#1e5238',
  font_style ENUM('bengali', 'modern', 'serif') NOT NULL DEFAULT 'bengali',
  button_style ENUM('soft', 'outline', 'pill') NOT NULL DEFAULT 'soft',
  qr_code VARCHAR(40) NOT NULL UNIQUE,
  qr_foreground CHAR(7) NOT NULL DEFAULT '#173d2b',
  qr_background CHAR(7) NOT NULL DEFAULT '#ffffff',
  is_published BOOLEAN NOT NULL DEFAULT TRUE,
  plan ENUM('free', 'premium') NOT NULL DEFAULT 'free',
  premium_until DATETIME NULL,
  moderation_status ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
  preferred_locale ENUM('bn', 'en') NOT NULL DEFAULT 'bn',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT profiles_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX profiles_public_idx (username, is_published, moderation_status),
  INDEX profiles_plan_idx (plan, premium_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  profile_id CHAR(36) NOT NULL,
  event_type ENUM('view', 'link_click', 'contact_click', 'qr_scan') NOT NULL,
  link_id VARCHAR(100) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT analytics_profile_fk FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
  INDEX analytics_profile_created_idx (profile_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS card_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  profile_id CHAR(36) NOT NULL,
  reason ENUM('spam', 'impersonation', 'abuse', 'other') NOT NULL,
  details VARCHAR(500) NOT NULL DEFAULT '',
  status ENUM('open', 'reviewed', 'dismissed') NOT NULL DEFAULT 'open',
  reporter_hash VARCHAR(100) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT reports_profile_fk FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
  INDEX reports_status_created_idx (status, created_at),
  INDEX reports_dedupe_idx (profile_id, reporter_hash, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_settings (
  id TINYINT PRIMARY KEY DEFAULT 1,
  receiver_number VARCHAR(20) NOT NULL,
  premium_price INT UNSIGNED NOT NULL,
  premium_days INT UNSIGNED NOT NULL,
  max_free_links INT UNSIGNED NOT NULL DEFAULT 5,
  receipt_required BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO payment_settings
  (id, receiver_number, premium_price, premium_days, max_free_links, receipt_required)
VALUES (1, '01796997871', 499, 30, 5, TRUE)
ON DUPLICATE KEY UPDATE id = id;

CREATE TABLE IF NOT EXISTS payment_requests (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  profile_id CHAR(36) NOT NULL,
  provider ENUM('bkash', 'nagad', 'rocket') NOT NULL,
  payment_type ENUM('send_money', 'merchant_payment') NOT NULL,
  sender_number VARCHAR(20) NOT NULL,
  transaction_id VARCHAR(80) NOT NULL,
  amount INT UNSIGNED NOT NULL,
  status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  admin_note VARCHAR(300) NOT NULL DEFAULT '',
  receipt_path VARCHAR(500) NULL,
  reviewed_by CHAR(36) NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT payments_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT payments_profile_fk FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
  CONSTRAINT payments_reviewer_fk FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY payments_provider_transaction_unique (provider, transaction_id),
  INDEX payments_user_created_idx (user_id, created_at),
  INDEX payments_status_created_idx (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value JSON NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS backup_history (
  id CHAR(36) PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  backup_type ENUM('database', 'full', 'pre_restore') NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('creating', 'ready', 'failed', 'restored') NOT NULL DEFAULT 'creating',
  created_by CHAR(36) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT backup_creator_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX backup_created_idx (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id CHAR(36) NULL,
  action VARCHAR(100) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id VARCHAR(100) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT audit_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX audit_created_idx (created_at),
  INDEX audit_user_idx (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;