Popular Enterprise - Pickles & Spices Business Website

-- Categories (Pickles, Spices, etc.)
CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- Products with SEO fields
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category_id INTEGER,
    description TEXT,
    main_image TEXT,
    seo_title TEXT,
    seo_description TEXT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Product variants (size + price)
CREATE TABLE product_variants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER,
    size TEXT,
    price REAL,
    cost_price REAL,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Quote requests from customers
CREATE TABLE quotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT NOT NULL,
    product_id INTEGER,
    variant_id INTEGER,
    quantity INTEGER,
    address TEXT,
    status TEXT DEFAULT 'New',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Sales records
CREATE TABLE sales_records (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT DEFAULT CURRENT_TIMESTAMP,
    product_id INTEGER,
    variant_id INTEGER,
    customer_name TEXT,
    quantity INTEGER,
    unit_price REAL,
    total_price REAL,
    quote_id INTEGER
);

-- Contact form submissions
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    message TEXT,
    status TEXT DEFAULT 'New'
);

-- Customer feedback
CREATE TABLE feedbacks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    rating INTEGER,
    comment TEXT,
    status TEXT DEFAULT 'Pending'
);

-- Compliance documents (certificates)
CREATE TABLE compliance_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    file_path TEXT NOT NULL,
    expiry_date TEXT,
    is_public BOOLEAN DEFAULT 1
);

-- Admin users
CREATE TABLE admin_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT DEFAULT 'Admin'
);

-- Audit logs
CREATE TABLE audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_id INTEGER,
    action TEXT NOT NULL,
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Quote contact logs
CREATE TABLE quote_contact_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    quote_id INTEGER,
    admin_id INTEGER,
    note TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

// Quote Request
router.post('/quote', async (req, res) => {
    const { name, phone, product_id, variant_id, quantity, address } = req.body;
    // Insert quote, send Telegram notification
});

// Contact Form
router.post('/contact', async (req, res) => {
    const { name, email, phone, message } = req.body;
    // Insert contact, send Telegram notification
});

// Feedback
router.post('/feedback', async (req, res) => {
    const { name, rating, comment } = req.body;
    // Insert feedback, send Telegram notification
});

// Product Management
router.get('/admin/products/:id', ...);     // Get single product
router.post('/admin/products', ...);        // Add product with variants
router.put('/admin/products/:id', ...);     // Update product
router.delete('/admin/products/:id', ...);   // Delete product

// Quote to Sale Conversion
router.post('/admin/convert-to-sale', ...); // Convert quote to sales record

// Bulk Sales Import
router.post('/admin/sales/import', ...);     // CSV bulk import

// Contact Management
router.delete('/admin/contacts/:id', ...);   // Delete contact

// services/telegram.js
const TelegramBot = require('node-telegram-bot-api');
const bot = new TelegramBot(process.env.TELEGRAM_TOKEN, { polling: false });

async function sendNotification(message) {
    try {
        await bot.sendMessage(process.env.TELEGRAM_CHAT_ID, message, {
            parse_mode: 'Markdown'
        });
    } catch (err) {
        console.error('Telegram notification failed:', err);
    }
}

// Quote notification format
const message = `
📦 *NEW QUOTE REQUEST*

👤 *Name:* ${name}
📞 *Phone:* ${phone}
🛒 *Product:* ${product.name}
⚖ *Size:* ${variant.size}
🔢 *Quantity:* ${quantity}
📍 *Address:* ${address || 'N/A'}

🕒 *Time:* ${new Date().toLocaleString()}
`.trim();

Customer browses products
        ↓
Selects product, chooses variant and quantity
        ↓
Fills quote form (name, phone, address)
        ↓
Quote saved to database
        ↓
Telegram notification sent instantly to owner
        ↓
Owner views quote in admin dashboard
        ↓
Owner contacts customer via phone
        ↓
If sale completed: Owner marks quote as "Completed"
        ↓
System auto-creates sales record
        ↓
Sales reflected in reports and KPIs

// Session-based auth with bcrypt
const bcrypt = require('bcryptjs');

// Password comparison
if (user && await bcrypt.compare(password, user.password)) {
    req.session.adminId = user.id;
    req.session.role = user.role;
}

app.use(helmet({
    contentSecurityPolicy: {
        directives: {
            script-src: ["'self'", "'unsafe-inline'", "https://cdn.jsdelivr.net"],
            style-src: ["'self'", "'unsafe-inline'", "https://fonts.googleapis.com"],
            img-src: ["'self'", "data:", "https://*"],
            font-src: ["'self'", "https://fonts.gstatic.com"]
        }
    }
}));

app.use(session({
    store: new SQLiteStore({ db: 'sessions.db' }),
    secret: process.env.SESSION_SECRET,
    resave: false,
    saveUninitialized: false,
    cookie: {
        maxAge: 30 * 24 * 60 * 60 * 1000, // 30 days
        httpOnly: true
    }
}));

// scripts/optimize-images.js
const sharp = require('sharp');
const glob = require('glob');
const path = require('path');

async function optimizeImages() {
    const files = glob.sync('public/assets/**/*.{png,jpg,jpeg}');
    
    for (const file of files) {
        const outputPath = file.replace('/assets/', '/assets/optimized/');
        await sharp(file)
            .webp({ quality: 80 })
            .toFile(outputPath.replace(/\.[^.]+$/, '.webp'));
    }
}

npm run build:css
# Generates optimized CSS from Tailwind

npm run lhci
# Runs Lighthouse audits with CI

PORT=3000
SESSION_SECRET=your_secret_key
TELEGRAM_TOKEN=your_telegram_bot_token
TELEGRAM_CHAT_ID=your_chat_id

# Install dependencies
npm install

# Initialize database
node database/init.js

# Start server
npm start

# Optimize images
npm run images:optimize

# Build CSS
npm run build:css

# Run Lighthouse audit
npm run lhci