-- ============================================================
-- MySQL Migration Script for XavieriPreOrder
-- Run this on your live shared hosting MySQL database
-- ============================================================

-- 1. USERS TABLE
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    address TEXT NULL,
    role ENUM('customer', 'admin') DEFAULT 'customer',
    wallet_balance DECIMAL(15,2) DEFAULT 0,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO users (name, email, password, phone, role, created_at, updated_at) VALUES
('Admin', 'admin@preorder.com', '$2y$12$VhLWl7TS13dC4TPXJQm5UuXMSwKKUQhTGy.OVkoDfmbYc2M970M0m', '+2348000000000', 'admin', NOW(), NOW()),
('John Doe', 'john@example.com', '$2y$12$4J4jNGI0eqRj8y/rqFu52.ISHC/kLeiFeGp0pJaMnSifoxN0ziL6i', '+2348012345678', 'customer', NOW(), NOW()),
('Jane Smith', 'jane@example.com', '$2y$12$7n3aLmalSpbFRif14i9HOO3JFkA9ORUehPO4PBBbGOyP5ANjKUnQa', '+2348012345679', 'customer', NOW(), NOW());

-- 2. PRODUCTS TABLE
CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    base_cost DECIMAL(12,2) NOT NULL,
    markup_price DECIMAL(12,2) NOT NULL,
    shipping_type ENUM('AIR', 'SEA') DEFAULT 'AIR',
    estimated_days INT NOT NULL,
    pairing_deadline DATETIME NOT NULL,
    min_quantity INT DEFAULT 1,
    total_paired INT DEFAULT 0,
    status ENUM('active', 'paired', 'ordered', 'shipped', 'delivered', 'closed') DEFAULT 'active',
    total_shipping_cost DECIMAL(12,2) NULL,
    shipping_per_item DECIMAL(12,2) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO products (name, description, base_cost, markup_price, shipping_type, estimated_days, pairing_deadline, min_quantity, total_paired, status, created_at, updated_at) VALUES
('iPhone 15 Pro Max', 'Latest Apple flagship smartphone with A17 Pro chip, titanium design, and advanced camera system.', 650000, 750000, 'AIR', 14, DATE_ADD(NOW(), INTERVAL 7 DAY), 1, 0, 'active', NOW(), NOW()),
('Samsung Galaxy S24 Ultra', 'Premium Android flagship with S Pen, 200MP camera, and AI features.', 550000, 650000, 'AIR', 14, DATE_ADD(NOW(), INTERVAL 10 DAY), 1, 0, 'active', NOW(), NOW()),
('MacBook Air M3', 'Ultra-thin laptop with M3 chip, perfect for work and creativity.', 680000, 780000, 'AIR', 14, DATE_ADD(NOW(), INTERVAL 5 DAY), 1, 0, 'active', NOW(), NOW()),
('Sony PlayStation 5', 'Next-gen gaming console with 4K gaming, Ray Tracing, and DualSense controller.', 350000, 420000, 'SEA', 70, DATE_ADD(NOW(), INTERVAL 14 DAY), 1, 0, 'active', NOW(), NOW()),
('AirPods Pro 2nd Gen', 'Active Noise Cancellation, Adaptive Audio, and personalized spatial audio.', 95000, 125000, 'AIR', 14, DATE_ADD(NOW(), INTERVAL 3 DAY), 2, 0, 'active', NOW(), NOW());

-- 3. PRODUCT_IMAGES TABLE
CREATE TABLE IF NOT EXISTS product_images (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO product_images (product_id, image_path, sort_order, created_at, updated_at) VALUES
(1, 'products/placeholder-1.jpg', 0, NOW(), NOW()),
(2, 'products/placeholder-2.jpg', 0, NOW(), NOW()),
(3, 'products/placeholder-3.jpg', 0, NOW(), NOW()),
(4, 'products/placeholder-4.jpg', 0, NOW(), NOW()),
(5, 'products/placeholder-5.jpg', 0, NOW(), NOW());

-- 4. PAIRINGS TABLE
CREATE TABLE IF NOT EXISTS pairings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    total_price DECIMAL(12,2) NOT NULL,
    status ENUM('active', 'converted', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. ORDERS TABLE
CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    subtotal DECIMAL(12,2) NOT NULL,
    shipping_cost DECIMAL(12,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,
    paid_amount DECIMAL(12,2) DEFAULT 0,
    payment_status ENUM('pending', 'partial', 'paid') DEFAULT 'pending',
    order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    notes TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. ORDER_ITEMS TABLE
CREATE TABLE IF NOT EXISTS order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    shipping_cost DECIMAL(12,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. PAYMENTS TABLE
CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    payment_method ENUM('bank_transfer') NOT NULL,
    reference_number VARCHAR(100) NULL,
    proof_image VARCHAR(255) NULL,
    amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'rejected') DEFAULT 'pending',
    admin_notes TEXT NULL,
    confirmed_by BIGINT UNSIGNED NULL,
    confirmed_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. SHIPPING_RECORDS TABLE
CREATE TABLE IF NOT EXISTS shipping_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    status ENUM('ordered', 'shipped_from_origin', 'in_transit', 'arrived_nigeria', 'ready_for_pickup', 'delivered') DEFAULT 'ordered',
    tracking_notes TEXT NULL,
    status_updated_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. SETTINGS TABLE
CREATE TABLE IF NOT EXISTS settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) NOT NULL UNIQUE,
    value TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (`key`, value, created_at, updated_at) VALUES
('bank_name', 'First Bank of Nigeria', NOW(), NOW()),
('account_number', '1234567890', NOW(), NOW()),
('account_name', 'PreOrder System Ltd', NOW(), NOW());

-- 10. NOTIFICATIONS TABLE
CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type VARCHAR(50) DEFAULT 'general',
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. PRODUCT_REQUESTS TABLE
CREATE TABLE IF NOT EXISTS product_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    image_path VARCHAR(255) NULL,
    status ENUM('pending', 'fulfilled', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. MIGRATIONS TABLE
CREATE TABLE IF NOT EXISTS migrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    migration VARCHAR(255) NOT NULL,
    batch INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DEFAULT CREDENTIALS
-- ============================================================
-- Admin: admin@preorder.com / password: password
-- Customer: john@example.com / password: password
-- Customer: jane@example.com / password: password
-- ============================================================