-- ================================================================ -- NumisVault — Full Database Schema -- MySQL 5.7+ / MariaDB 10.3+ -- -- Step 1: Create database "numivault" in phpMyAdmin -- Step 2: Select that database -- Step 3: Click Import → choose this file → Go -- ================================================================ SET NAMES utf8mb4; SET foreign_key_checks = 0; -- ── 1. USERS ───────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS users ( id INT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(100), email VARCHAR(150), role ENUM('admin','editor','viewer') DEFAULT 'admin', is_active TINYINT(1) DEFAULT 1, last_login DATETIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Default login: admin / admin123 INSERT INTO users (username, password_hash, display_name, role) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'admin'); -- ── 2. CATEGORIES ──────────────────────────────────────────── CREATE TABLE IF NOT EXISTS categories ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL UNIQUE, description TEXT, sort_order INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO categories (name, description, sort_order) VALUES ('Ancient', 'Greek, Roman, Byzantine — coins from antiquity', 1), ('Medieval', 'Islamic, European medieval (5th–15th century)', 2), ('Early Modern', 'Renaissance through 18th-century coinage', 3), ('Modern', '19th–20th century national coinage', 4), ('Contemporary', '21st-century and current issues', 5), ('Bullion', 'Investment-grade gold, silver, platinum coins', 6), ('Commemorative', 'Special issues and limited editions', 7), ('Trade Coinage', 'Coins minted for international trade', 8); -- ── 3. COINS ───────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS coins ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(200) NOT NULL, country VARCHAR(100), year INT, denomination VARCHAR(100), metal_type ENUM('Gold','Silver','Bronze','Copper','Platinum','Bimetallic','Other') DEFAULT 'Other', grade VARCHAR(80), category_id INT, emoji VARCHAR(10) DEFAULT '🪙', purchase_price DECIMAL(12,2) DEFAULT 0.00, market_price DECIMAL(12,2) NOT NULL DEFAULT 0.00, status ENUM('Available','Reserved','Sold') DEFAULT 'Available', weight_g DECIMAL(10,3), diameter_mm DECIMAL(7,2), mintage BIGINT, description TEXT, notes TEXT, image_path VARCHAR(500), added_by INT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL, FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_metal (metal_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 4. COIN IMAGES ─────────────────────────────────────────── CREATE TABLE IF NOT EXISTS coin_images ( id INT NOT NULL AUTO_INCREMENT, coin_id INT NOT NULL, image_path VARCHAR(500) NOT NULL, side ENUM('obverse','reverse','edge','detail','other') DEFAULT 'obverse', is_primary TINYINT(1) DEFAULT 0, uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE CASCADE, INDEX idx_coin_primary (coin_id, is_primary) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 5. CUSTOMERS ───────────────────────────────────────────── CREATE TABLE IF NOT EXISTS customers ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(150) NOT NULL, email VARCHAR(150) NOT NULL, phone VARCHAR(50), address TEXT, city VARCHAR(100), country_name VARCHAR(100), inquiry_count INT DEFAULT 0, purchase_count INT DEFAULT 0, total_spent DECIMAL(12,2) DEFAULT 0.00, first_seen DATETIME DEFAULT CURRENT_TIMESTAMP, last_contact DATETIME DEFAULT CURRENT_TIMESTAMP, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_email (email), INDEX idx_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 6. INQUIRIES ───────────────────────────────────────────── CREATE TABLE IF NOT EXISTS inquiries ( id VARCHAR(20) NOT NULL, customer_id INT, customer_name VARCHAR(150) NOT NULL, customer_phone VARCHAR(50), customer_email VARCHAR(150) NOT NULL, customer_address TEXT, coin_id INT, coin_name VARCHAR(200), coin_price DECIMAL(12,2), coin_emoji VARCHAR(10), quantity VARCHAR(20) DEFAULT '1', budget_range VARCHAR(50), message TEXT, status ENUM('New','Seen','Replied','Closed') DEFAULT 'New', submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP, seen_at DATETIME, PRIMARY KEY (id), FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL, FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_submitted (submitted_at DESC), INDEX idx_cust_inq (customer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 7. ADMIN REPLIES ───────────────────────────────────────── CREATE TABLE IF NOT EXISTS admin_replies ( id INT NOT NULL AUTO_INCREMENT, inquiry_id VARCHAR(20) NOT NULL, replied_by INT, message TEXT NOT NULL, is_internal TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (inquiry_id) REFERENCES inquiries(id) ON DELETE CASCADE, FOREIGN KEY (replied_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_inquiry_reply (inquiry_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 8. SALES ───────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS sales ( id INT NOT NULL AUTO_INCREMENT, coin_id INT NOT NULL, customer_id INT, inquiry_id VARCHAR(20), sold_by INT, buyer_name VARCHAR(150), buyer_email VARCHAR(150), buyer_phone VARCHAR(50), buyer_address TEXT, sale_price DECIMAL(12,2) NOT NULL, purchase_price DECIMAL(12,2) DEFAULT 0.00, shipping_cost DECIMAL(8,2) DEFAULT 0.00, payment_method ENUM('Cash','Bank Transfer','Online','Cheque','bKash','Nagad','Crypto','Other') DEFAULT 'Cash', platform VARCHAR(100), sale_date DATE NOT NULL, receipt_number VARCHAR(100), notes TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (coin_id) REFERENCES coins(id) ON DELETE RESTRICT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL, FOREIGN KEY (sold_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_sale_date (sale_date DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── 9. AUDIT LOG ───────────────────────────────────────────── CREATE TABLE IF NOT EXISTS audit_log ( id BIGINT NOT NULL AUTO_INCREMENT, user_id INT, action VARCHAR(50) NOT NULL, table_name VARCHAR(50), record_id VARCHAR(50), description TEXT, ip_address VARCHAR(45), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_audit_date (created_at DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ── SEED: Sample coins ──────────────────────────────────────── INSERT INTO coins (name, country, year, denomination, metal_type, grade, category_id, emoji, purchase_price, market_price, status, weight_g, diameter_mm, mintage, description) VALUES ('Morgan Silver Dollar', 'United States', 1921, '1 Dollar', 'Silver', 'EF-40', 4, '🦅', 45.00, 120.00, 'Available', 26.730, 38.10, 86730000, 'The Morgan dollar, designed by George T. Morgan. Minted 1878–1904 and again in 1921. One of the most popular US coins among collectors worldwide.'), ('British Gold Sovereign', 'United Kingdom',1895, '1 Pound', 'Gold', 'AU-50', 4, '👑', 380.00, 520.00, 'Available', 7.988, 22.05, NULL, 'Contains 0.2354 troy oz fine gold. Features the iconic St. George and Dragon reverse by Benedetto Pistrucci.'), ('Roman Silver Denarius', 'Roman Empire', 211, 'Denarius', 'Silver', 'F-12', 1, '🏛️', 150.00, 280.00, 'Sold', 2.800, 18.00, NULL, 'Issued under Emperor Caracalla. Remarkable ancient numismatic artifact from the height of the Roman Empire.'), ('French Napoléon 20F', 'France', 1811, '20 Francs', 'Gold', 'MS-60', 3, '⚜️', 290.00, 420.00, 'Available', 6.452, 21.00, NULL, 'Gold coin from the First French Empire. Napoleon Bonaparte portrait on obverse.'), ('Walking Liberty Half', 'United States', 1943, '50 Cents', 'Silver', 'MS-65', 4, '🗽', 28.00, 75.00, 'Available', 12.500, 30.61, 53190000, 'Adolph Weinman iconic design minted during WWII. One of the most beautiful US coins ever produced.'), ('Chinese Gold Panda', 'China', 2022, '10 Yuan', 'Gold', 'MS-70', 6, '🐼', 1850.00,2200.00,'Available', 30.000, 32.00, 600000, '30g .999 fine gold. Annual panda design changes make each year a unique collectible.'); -- ── SEED: Sample customer + inquiry ────────────────────────── INSERT INTO customers (name, email, phone, address, inquiry_count) VALUES ('Sarah Johnson', 'sarah.j@email.com', '+1 555 0192', '742 Evergreen Terrace, Springfield, IL, USA', 1); INSERT INTO inquiries (id, customer_id, customer_name, customer_phone, customer_email, customer_address, coin_id, coin_name, coin_price, coin_emoji, quantity, budget_range, message, status) VALUES ('NV-DEMO01', 1, 'Sarah Johnson', '+1 555 0192', 'sarah.j@email.com', '742 Evergreen Terrace, Springfield, IL, USA', 2, 'British Gold Sovereign', 520.00, '👑', '1', '$500 – $2,000', 'I am very interested in this sovereign. Can you tell me more about its condition and shipping options to the US?', 'New'); INSERT INTO sales (coin_id, buyer_name, buyer_email, sale_price, purchase_price, payment_method, sale_date, notes, sold_by) VALUES (3, 'James Whitmore', 'j.whitmore@email.com', 310.00, 150.00, 'Bank Transfer', '2024-11-05', 'Smooth transaction.', 1); SET foreign_key_checks = 1;