-- ================================================================
--  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 foreign_key_checks = 0;


-- ── 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;
