-- Migration: create users and admins tables
-- Run this in your MySQL client or via phpMyAdmin

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `gender` VARCHAR(20) DEFAULT NULL,
  `age` TINYINT UNSIGNED DEFAULT NULL,
  `status` ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_users_email` (`email`),
  UNIQUE KEY `uk_users_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `role_name` VARCHAR(50) NOT NULL DEFAULT 'admin',
  `permissions` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_admins_user` (`user_id`),
  CONSTRAINT `fk_admins_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Example: create a user and make them an admin
-- INSERT INTO `users` (username, email, password_hash) VALUES ('adminuser', 'admin@example.com', '<hash>');
-- SET @uid = LAST_INSERT_ID();
-- INSERT INTO `admins` (user_id, role_name) VALUES (@uid, 'superadmin');

-- Notes:
-- 1) Store password hashes using PHP's password_hash().
-- 2) Authentication checks should query `users` (for login) and then
--    check `admins` for privileges (e.g. SELECT * FROM admins WHERE user_id = ?).
