File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090038/Shop/schema.sql

Back
-- ============================================================ -- Shopee-Style Auth System — MySQL Schema -- Compatible with phpMyAdmin / XAMPP / Web Hosting (MySQL 5.7+) -- ============================================================ CREATE DATABASE IF NOT EXISTS `sarin_shop` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `sarin_shop`; -- ─── USERS ─────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(50) UNIQUE NOT NULL, `email` VARCHAR(100) UNIQUE NOT NULL, `phone` VARCHAR(20) UNIQUE NULL, `password_hash` VARCHAR(255) NOT NULL, `role` ENUM('buyer','seller','admin') DEFAULT 'buyer', `avatar_url` VARCHAR(255) DEFAULT 'https://ui-avatars.com/api/?background=ee4d2d&color=fff&size=150', `full_name` VARCHAR(100) NULL, `is_email_verified` TINYINT(1) DEFAULT 0, `is_phone_verified` TINYINT(1) DEFAULT 0, `is_active` TINYINT(1) DEFAULT 1, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX `idx_email` (`email`), INDEX `idx_phone` (`phone`), INDEX `idx_username`(`username`), INDEX `idx_role` (`role`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── USER SESSIONS (Refresh Tokens) ────────────────────────── CREATE TABLE IF NOT EXISTS `user_sessions` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `refresh_token` TEXT NOT NULL, `user_agent` VARCHAR(500) NULL, `ip_address` VARCHAR(45) NULL, `expires_at` DATETIME NOT NULL, `remember_me` TINYINT(1) DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_user_id` (`user_id`), INDEX `idx_expires_at`(`expires_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── OTP CODES ─────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS `otp_codes` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `identifier` VARCHAR(100) NOT NULL COMMENT 'Email or Phone number', `code` VARCHAR(6) NOT NULL, `purpose` ENUM('register','login','reset','verify') DEFAULT 'login', `expires_at` DATETIME NOT NULL, `is_used` TINYINT(1) DEFAULT 0, `attempts` TINYINT(1) DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX `idx_identifier`(`identifier`), INDEX `idx_expires_at`(`expires_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── SELLER PROFILES ───────────────────────────────────────── CREATE TABLE IF NOT EXISTS `seller_profiles` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL UNIQUE, `shop_name` VARCHAR(100) NOT NULL, `shop_desc` TEXT NULL, `shop_logo` VARCHAR(255) NULL, `is_approved` TINYINT(1) DEFAULT 0, `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `approved_at` DATETIME NULL, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── LOGIN ATTEMPT TRACKING (Brute Force Prevention) ───────── CREATE TABLE IF NOT EXISTS `login_attempts` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `identifier` VARCHAR(100) NOT NULL COMMENT 'Email, phone, or username attempted', `ip_address` VARCHAR(45) NULL, `success` TINYINT(1) DEFAULT 0, `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX `idx_identifier_time`(`identifier`, `attempted_at`), INDEX `idx_ip_time`(`ip_address`, `attempted_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── PRODUCTS ───────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `seller_id` INT NOT NULL, `name` VARCHAR(200) NOT NULL, `description` TEXT NULL, `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00, `stock` INT NOT NULL DEFAULT 0, `category` VARCHAR(100) NULL, `image_url` VARCHAR(500) NULL, `is_active` TINYINT(1) DEFAULT 1, `sold_count` INT DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_seller_id` (`seller_id`), INDEX `idx_category` (`category`), INDEX `idx_is_active` (`is_active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── ORDERS ─────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `buyer_id` INT NOT NULL, `seller_id` INT NOT NULL, `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00, `status` ENUM('pending','confirmed','shipping','delivered','cancelled') DEFAULT 'pending', `address` TEXT NULL, `note` TEXT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_buyer_id` (`buyer_id`), INDEX `idx_seller_id` (`seller_id`), INDEX `idx_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── ORDER ITEMS ────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `product_id` INT NOT NULL, `qty` INT NOT NULL DEFAULT 1, `unit_price` DECIMAL(10,2) NOT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE, INDEX `idx_order_id` (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ─── MESSAGES (Customer ↔ Seller) ───────────────────────────── CREATE TABLE IF NOT EXISTS `messages` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `seller_id` INT NOT NULL, `buyer_id` INT NOT NULL, `sender_role` ENUM('buyer','seller') NOT NULL, `body` TEXT NOT NULL, `is_read` TINYINT(1) DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_seller_buyer` (`seller_id`, `buyer_id`), INDEX `idx_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

| ver. 1.4 | Github | . | PHP 7.4.33 | Generation time: 0.23 | proxy | phpinfo | Settings