-- ============================================================ -- Khoopuram Silks - Database Schema + Seed Data -- Import this file via cPanel > phpMyAdmin into your database -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; SET NAMES utf8mb4; -- ---------- Admin users ---------- CREATE TABLE IF NOT EXISTS admins ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(160) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Default login -> email: admin@khoopuramsilks.lk password: admin123 -- IMPORTANT: change this immediately after first login. INSERT INTO admins (name, email, password_hash) VALUES ('Store Admin', 'admin@khoopuramsilks.lk', '$2b$10$LEvys4ORDhLaqq2n1GdARuTf3349R8z6OUt9.i4Xt9aJkYVSZvPjq'); -- ---------- Categories ---------- CREATE TABLE IF NOT EXISTS categories ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, slug VARCHAR(140) NOT NULL UNIQUE, tagline VARCHAR(200) DEFAULT NULL, sort_order INT DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO categories (name, slug, tagline, sort_order) VALUES ('Kanchipuram Silk', 'kanchipuram-silk', 'Temple-bordered pure silk woven with zari', 1), ('Soft Silk', 'soft-silk', 'Lightweight drape with a lustrous finish', 2), ('Bridal Collection','bridal-collection','Heirloom sarees for the wedding mandap', 3), ('Cotton Sarees', 'cotton-sarees', 'Breathable everyday elegance', 4), ('Designer & Party', 'designer-party', 'Contemporary weaves and embellished work', 5), ('Half Sarees', 'half-sarees', 'For pubertal functions and celebrations', 6); -- ---------- Products ---------- CREATE TABLE IF NOT EXISTS products ( id INT AUTO_INCREMENT PRIMARY KEY, category_id INT NOT NULL, name VARCHAR(180) NOT NULL, slug VARCHAR(200) NOT NULL UNIQUE, sku VARCHAR(60) DEFAULT NULL, price DECIMAL(12,2) NOT NULL, sale_price DECIMAL(12,2) DEFAULT NULL, cost_price DECIMAL(12,2) DEFAULT 0, inbound_shipping DECIMAL(12,2) DEFAULT 0, fabric VARCHAR(120) DEFAULT NULL, color VARCHAR(80) DEFAULT NULL, occasion VARCHAR(120) DEFAULT NULL, blouse VARCHAR(120) DEFAULT 'Running blouse included', description TEXT, stock INT DEFAULT 0, image VARCHAR(255) DEFAULT NULL, featured TINYINT(1) DEFAULT 0, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO products (category_id, name, slug, sku, price, sale_price, fabric, color, occasion, description, stock, image, featured) VALUES (1, 'Maroon Temple-Border Kanchipuram', 'maroon-temple-border-kanchipuram', 'KS-KAN-001', 48500, 42500, 'Pure mulberry silk', 'Maroon & Gold', 'Wedding, Temple', 'Handwoven pure Kanchipuram silk with a traditional temple border and rich gold zari pallu. A timeless heirloom drape.', 6, NULL, 1), (1, 'Peacock Blue Zari Kanchipuram', 'peacock-blue-zari-kanchipuram', 'KS-KAN-002', 52000, NULL, 'Pure mulberry silk', 'Peacock Blue', 'Wedding, Festive', 'Lustrous peacock-blue body with contrast mustard border and an intricate zari pallu featuring annapakshi motifs.', 4, NULL, 1), (2, 'Ivory Soft Silk with Gold Buttas', 'ivory-soft-silk-gold-buttas', 'KS-SS-001', 18900, 16500, 'Soft silk', 'Ivory', 'Reception, Party', 'Featherlight soft silk in ivory, scattered with delicate gold buttas. Drapes beautifully for evening functions.', 10, NULL, 1), (2, 'Rani Pink Soft Silk', 'rani-pink-soft-silk', 'KS-SS-002', 17500, NULL, 'Soft silk', 'Rani Pink', 'Festive', 'Vibrant rani-pink soft silk with a slim contrast border. Easy to carry, hard to forget.', 12, NULL, 0), (3, 'Bridal Red Kanchipuram Grand', 'bridal-red-kanchipuram-grand', 'KS-BR-001', 96000, 89000, 'Pure heavy silk', 'Bridal Red', 'Wedding Mandap', 'A grand bridal Kanchipuram in classic kumkum red, woven with broad gold zari and a heavily detailed pallu. The centrepiece of your wedding.', 3, NULL, 1), (3, 'Mango-Yellow Bridal Tissue', 'mango-yellow-bridal-tissue', 'KS-BR-002', 74000, NULL, 'Silk tissue', 'Mango Yellow', 'Muhurtham', 'Radiant mango-yellow tissue silk with antique gold work, made for the muhurtham ceremony.', 5, NULL, 0), (4, 'Handloom Cotton Stripes', 'handloom-cotton-stripes', 'KS-CT-001', 4200, 3600, 'Handloom cotton', 'Teal & Cream', 'Daily, Office', 'Crisp handloom cotton with woven teal stripes and a contrast border. Cool, comfortable, and endlessly wearable.', 25, NULL, 0), (4, 'Mustard Cotton with Kalamkari Border', 'mustard-cotton-kalamkari-border', 'KS-CT-002', 5400, NULL, 'Handloom cotton', 'Mustard', 'Casual, Office', 'Earthy mustard cotton finished with a printed kalamkari border. Effortless everyday grace.', 18, NULL, 0), (5, 'Wine Georgette Designer Saree', 'wine-georgette-designer-saree', 'KS-DG-001', 12800, 10900, 'Georgette', 'Wine', 'Party, Cocktail', 'Flowing wine georgette with sequin and bead embellishment along the pallu. A modern showstopper.', 9, NULL, 1), (5, 'Emerald Organza with Floral Print', 'emerald-organza-floral-print', 'KS-DG-002', 9900, NULL, 'Organza', 'Emerald', 'Party, Reception', 'Sheer emerald organza printed with delicate florals and edged with a fine gold piping.', 11, NULL, 0), (6, 'Half Saree Set - Magenta & Green', 'half-saree-set-magenta-green', 'KS-HS-001', 21000, 18500, 'Art silk', 'Magenta & Green', 'Function, Celebration', 'Traditional half-saree set in magenta and green with zari work, lovely for coming-of-age functions.', 7, NULL, 0), (6, 'Half Saree Set - Sky & Gold', 'half-saree-set-sky-gold', 'KS-HS-002', 19500, NULL, 'Art silk', 'Sky Blue & Gold', 'Function', 'Gentle sky-blue half-saree set with gold detailing and a richly woven dupatta.', 6, NULL, 0); -- ---------- Orders ---------- CREATE TABLE IF NOT EXISTS orders ( id INT AUTO_INCREMENT PRIMARY KEY, order_code VARCHAR(30) NOT NULL UNIQUE, customer_name VARCHAR(160) NOT NULL, phone VARCHAR(40) NOT NULL, email VARCHAR(160) DEFAULT NULL, address TEXT NOT NULL, city VARCHAR(120) DEFAULT NULL, note TEXT, payment_method VARCHAR(60) DEFAULT 'Cash on Delivery', subtotal DECIMAL(12,2) NOT NULL, shipping DECIMAL(12,2) DEFAULT 0, total DECIMAL(12,2) NOT NULL, status VARCHAR(40) DEFAULT 'Pending', source VARCHAR(20) DEFAULT 'online', delivery_cost DECIMAL(12,2) DEFAULT 0, items_cost DECIMAL(12,2) DEFAULT 0, invoice_token VARCHAR(24) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS order_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT DEFAULT NULL, product_name VARCHAR(180) NOT NULL, unit_price DECIMAL(12,2) NOT NULL, quantity INT NOT NULL, line_total DECIMAL(12,2) NOT NULL, cost_price DECIMAL(12,2) DEFAULT 0, is_free TINYINT(1) DEFAULT 0, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Product colour variants ---------- CREATE TABLE IF NOT EXISTS product_colors ( id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, name VARCHAR(60) NOT NULL, hex VARCHAR(20) DEFAULT NULL, image VARCHAR(255) DEFAULT NULL, sort_order INT DEFAULT 0, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Newsletter / enquiries ---------- CREATE TABLE IF NOT EXISTS enquiries ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, email VARCHAR(160) DEFAULT NULL, phone VARCHAR(40) DEFAULT NULL, message TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Settings (editable from admin) ---------- CREATE TABLE IF NOT EXISTS settings ( skey VARCHAR(60) PRIMARY KEY, svalue TEXT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO settings (skey, svalue) VALUES ('brand_name', 'Khoopuram'), ('brand_sub', 'Silks'), ('site_name', 'Khoopuram Silks'), ('site_tagline', 'Heirloom sarees, woven with tradition'), ('currency', 'Rs.'), ('logo', ''), ('hero_image', ''), ('store_phone', '+94 77 000 0000'), ('store_whatsapp', '94770000000'), ('store_email', 'hello@khoopuramsilks.lk'), ('store_address', 'No. 12, Temple Road, Jaffna, Sri Lanka'), ('schema_v', '3'), ('shipping_flat', '350'), ('free_ship_over', '25000'), ('bank_details', ''), ('topbar_text', ''), ('show_brand_text','0'), ('footer_about', 'Each saree in our collection is chosen for its weave, its colour, and the story it carries to the wearer.'); SET FOREIGN_KEY_CHECKS = 1;