-- ============================================================
--  ADMIN PANEL SYSTEM — DATABASE SETUP
--  Step 1: Full Schema
--  Compatible with: MySQL 5.7+ / MariaDB 10.3+
-- ============================================================

CREATE DATABASE IF NOT EXISTS admin_panel_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE admin_panel_db;

-- ============================================================
-- TABLE 1: users
-- (Your partner populates this — you manage it from admin side)
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name       VARCHAR(100)        NOT NULL,
  username        VARCHAR(50)         NOT NULL UNIQUE,
  email           VARCHAR(150)        NOT NULL UNIQUE,
  password_hash   VARCHAR(255)        NOT NULL,
  avatar          VARCHAR(255)        DEFAULT 'default_avatar.png',
  phone           VARCHAR(20)         DEFAULT NULL,
  bio             TEXT                DEFAULT NULL,
  status          ENUM('active','inactive','banned','pending')
                                      NOT NULL DEFAULT 'pending',
  email_verified  TINYINT(1)          NOT NULL DEFAULT 0,
  last_login      DATETIME            DEFAULT 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;


-- ============================================================
-- TABLE 2: admins
-- (Your own separate admin accounts table)
-- ============================================================
CREATE TABLE IF NOT EXISTS admins (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name       VARCHAR(100)        NOT NULL,
  username        VARCHAR(50)         NOT NULL UNIQUE,
  email           VARCHAR(150)        NOT NULL UNIQUE,
  password_hash   VARCHAR(255)        NOT NULL,
  avatar          VARCHAR(255)        DEFAULT 'logo.png',
  phone           VARCHAR(20)         DEFAULT NULL,
  role            ENUM('super_admin','admin')
                                      NOT NULL DEFAULT 'admin',
  status          ENUM('active','inactive')
                                      NOT NULL DEFAULT 'active',
  last_login      DATETIME            DEFAULT 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;


-- ============================================================
-- TABLE 3: admin_sessions
-- (Tracks active admin login sessions)
-- ============================================================
CREATE TABLE IF NOT EXISTS admin_sessions (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id        INT UNSIGNED        NOT NULL,
  session_token   VARCHAR(255)        NOT NULL UNIQUE,
  ip_address      VARCHAR(45)         DEFAULT NULL,
  user_agent      TEXT                DEFAULT NULL,
  created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at      DATETIME            NOT NULL,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- TABLE 4: password_reset_tokens
-- (Handles forgot password for both admins and users)
-- ============================================================
CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity_type     ENUM('admin','user') NOT NULL,
  entity_id       INT UNSIGNED         NOT NULL,
  token           VARCHAR(255)         NOT NULL UNIQUE,
  expires_at      DATETIME             NOT NULL,
  used            TINYINT(1)           NOT NULL DEFAULT 0,
  created_at      DATETIME             NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- TABLE 5: activity_log
-- (Audit trail — every admin action is recorded here)
-- ============================================================
CREATE TABLE IF NOT EXISTS activity_log (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id        INT UNSIGNED        NOT NULL,
  action          VARCHAR(100)        NOT NULL,
  -- e.g. 'deleted_user', 'edited_user', 'banned_user', 'reset_password'
  target_type     ENUM('user','admin','system') NOT NULL DEFAULT 'user',
  target_id       INT UNSIGNED        DEFAULT NULL,
  target_name     VARCHAR(150)        DEFAULT NULL,
  -- Human readable snapshot e.g. "john@example.com"
  details         TEXT                DEFAULT NULL,
  -- JSON or plain text with extra context
  ip_address      VARCHAR(45)         DEFAULT NULL,
  created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- TABLE 6: system_settings
-- (Key-value store for admin-controlled site settings)
-- ============================================================
CREATE TABLE IF NOT EXISTS system_settings (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key     VARCHAR(100)        NOT NULL UNIQUE,
  setting_value   TEXT                DEFAULT NULL,
  setting_group   VARCHAR(50)         NOT NULL DEFAULT 'general',
  label           VARCHAR(150)        DEFAULT NULL,
  -- Human-readable label for display
  updated_by      INT UNSIGNED        DEFAULT NULL,
  updated_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP
                                      ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (updated_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- DEFAULT SYSTEM SETTINGS
-- ============================================================
INSERT INTO system_settings (setting_key, setting_value, setting_group, label) VALUES
  ('site_name',           'My Application',  'general',  'Site Name'),
  ('site_email',          'admin@site.com',  'general',  'Contact Email'),
  ('maintenance_mode',    '0',               'general',  'Maintenance Mode'),
  ('allow_registration',  '1',               'general',  'Allow User Registration'),
  ('max_login_attempts',  '5',               'security', 'Max Login Attempts'),
  ('session_lifetime',    '3600',            'security', 'Session Lifetime (seconds)'),
  ('items_per_page',      '20',              'display',  'Records Per Page');


-- ============================================================
-- DEFAULT SUPER ADMIN ACCOUNT
-- Password: Admin@1234  (bcrypt hash — CHANGE THIS IMMEDIATELY)
-- Generate your own: password_hash('yourpassword', PASSWORD_BCRYPT)
-- ============================================================
INSERT INTO admins (full_name, username, email, password_hash, role, status) VALUES (
  'Super Admin',
  'superadmin',
  'superadmin@yourdomain.com',
  '$2y$12$eImiTXuWVxfM37uY4JANjQ==hashed_placeholder_change_me',
  'super_admin',
  'active'
);

-- ⚠️  IMPORTANT: Replace the password_hash above with a real bcrypt hash.
--     In PHP: echo password_hash('YourPassword123!', PASSWORD_BCRYPT);
--     Run that line once, copy the output, paste it here, then re-run INSERT.


-- ============================================================
-- SAMPLE USER DATA (for testing — remove in production)
-- ============================================================
INSERT INTO users (full_name, username, email, password_hash, status, email_verified) VALUES
  ('Alice Johnson',  'alice_j',   'alice@example.com',  '$2y$12$placeholder_hash_1', 'active',   1),
  ('Bob Smith',      'bob_s',     'bob@example.com',    '$2y$12$placeholder_hash_2', 'active',   1),
  ('Carol White',    'carol_w',   'carol@example.com',  '$2y$12$placeholder_hash_3', 'inactive', 0),
  ('David Brown',    'david_b',   'david@example.com',  '$2y$12$placeholder_hash_4', 'banned',   1),
  ('Eve Davis',      'eve_d',     'eve@example.com',    '$2y$12$placeholder_hash_5', 'pending',  0);


-- ============================================================
-- USEFUL INDEXES FOR PERFORMANCE
-- ============================================================
CREATE INDEX idx_users_status       ON users(status);
CREATE INDEX idx_users_created_at   ON users(created_at);
CREATE INDEX idx_activity_admin     ON activity_log(admin_id);
CREATE INDEX idx_activity_created   ON activity_log(created_at);
CREATE INDEX idx_sessions_token     ON admin_sessions(session_token);
CREATE INDEX idx_sessions_expires   ON admin_sessions(expires_at);
CREATE INDEX idx_reset_token        ON password_reset_tokens(token);


-- ============================================================
-- DONE ✓
-- Tables created:
--   1. users              — managed from admin panel
--   2. admins             — your admin accounts
--   3. admin_sessions     — login session tracking
--   4. password_reset_tokens — forgot password flow
--   5. activity_log       — full audit trail
--   6. system_settings    — site configuration
-- ============================================================
