-- Database schema for iRESS Application

CREATE DATABASE IF NOT EXISTS iress_db;
USE iress_db;

-- 1. Orders Table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    company VARCHAR(255),
    address TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    postal_code VARCHAR(50) NOT NULL,
    payment_method VARCHAR(50) NOT NULL DEFAULT 'pod',
    qty INT NOT NULL DEFAULT 1,
    total_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    variant VARCHAR(50) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Contact Messages Table
CREATE TABLE IF NOT EXISTS contact_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('unread', 'read') DEFAULT 'unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Testimonials Table
CREATE TABLE IF NOT EXISTS testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(255),
    message TEXT NOT NULL,
    rating INT DEFAULT 5,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert some dummy testimonials
INSERT INTO testimonials (name, role, message, rating, status) VALUES
('Amaka O.', 'Field Nurse, Enugu', 'I charge my phone and a small lamp on outreach days. No more asking the driver to keep the engine running for power.', 5, 'approved'),
('Tunde A.', 'Site Engineer, Lagos', 'Runs my laptop through a full site inspection. The AC outlet is the difference — most power banks can''t do that.', 5, 'approved'),
('Grace I.', 'University Student, Abuja', 'Reading light and phone charged through every outage this semester. Paid for itself in data and fuel money.', 5, 'approved'),
('Emeka N.', 'Event Sound Tech, PH', 'Ran a small PA system for an outdoor event flawlessly.', 5, 'approved');

-- 4. Distributors Table
CREATE TABLE IF NOT EXISTS distributors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    region VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),
    status ENUM('pending', 'active', 'suspended') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample distributors
INSERT INTO distributors (name, region, email, phone) VALUES
('TechSolar Ltd', 'Lagos', 'contact@techsolar.com.ng', '+234 800 123 4567'),
('Abuja Power Solutions', 'Abuja', 'hello@abujapower.ng', '+234 801 987 6543');

-- 5. Admin Users Table (For login)
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- Hashed password
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default admin: username 'admin', password 'password123'
-- Hash generated using PHP password_hash('password123', PASSWORD_DEFAULT)
INSERT INTO admins (username, password) VALUES 
('admin', '$2y$10$wE9mHhH/4O.92q1X7T.k/OG63vM97Xv29j410T.K1p/mU2yX3E7W.');