░▒▓████████████████████████████████▓▒░ ░▒▓█ ▓▒░ ░▒▓█ ~ S W A M P ~ ▓▒░ ░▒▓█ ▓▒░ ░▒▓████████████████████████████████▓▒░
lon105.truehost.cloud
LOCATION:
/home/ilrtztmy/floraandcreations.com/api/database
☗ ROOT
↻ REFRESH
✎ CARVE FLESH
EDITING: schema.sql
-- Flora and Creations — Database Schema -- Execute in your MySQL database (phpMyAdmin / cPanel) SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ────────────────────────────────────────────────────────────── -- 1. users -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, phone VARCHAR(20) DEFAULT NULL, password_hash VARCHAR(255) DEFAULT NULL COMMENT 'nullable for guest checkout', role ENUM('customer','admin','staff') NOT NULL DEFAULT 'customer', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 2. addresses -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS addresses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, label VARCHAR(255) NOT NULL, recipient_name VARCHAR(255) NOT NULL, recipient_phone VARCHAR(20) NOT NULL, area VARCHAR(255) NOT NULL COMMENT 'maps to delivery_zones', street_address TEXT NOT NULL, landmark VARCHAR(255) DEFAULT NULL, CONSTRAINT fk_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 3. categories -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, type ENUM('product_type','occasion') NOT NULL, slug VARCHAR(255) NOT NULL UNIQUE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 4. products -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS products ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL UNIQUE, description TEXT DEFAULT NULL, base_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, status ENUM('active','draft','archived') NOT NULL DEFAULT 'active', featured TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 5. product_categories (pivot) -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS product_categories ( product_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, PRIMARY KEY (product_id, category_id), CONSTRAINT fk_pc_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 6. product_images -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS product_images ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, image_url VARCHAR(512) NOT NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, CONSTRAINT fk_images_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 7. product_variants -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS product_variants ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, size VARCHAR(100) DEFAULT NULL, color VARCHAR(100) DEFAULT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, stock_status ENUM('in_stock','made_to_order','out_of_stock') NOT NULL DEFAULT 'in_stock', sku VARCHAR(100) NOT NULL, quantity INT UNSIGNED NOT NULL DEFAULT 0, reorder_level INT UNSIGNED NOT NULL DEFAULT 5, CONSTRAINT fk_variants_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 8. add_ons -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS add_ons ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, type VARCHAR(100) DEFAULT 'default' COMMENT 'e.g. "gift_card" needs custom message', active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 9. delivery_zones -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS delivery_zones ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, area_name VARCHAR(255) NOT NULL UNIQUE, fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 10. orders -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(20) NOT NULL UNIQUE, user_id INT UNSIGNED DEFAULT NULL COMMENT 'nullable for guest checkout', fulfillment_type ENUM('delivery','pickup') NOT NULL DEFAULT 'delivery', delivery_zone_id INT UNSIGNED DEFAULT NULL, delivery_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00, delivery_address_id INT UNSIGNED DEFAULT NULL, scheduled_date DATE DEFAULT NULL, scheduled_time_slot VARCHAR(100) DEFAULT NULL, gift_note TEXT DEFAULT NULL, subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00, total DECIMAL(10,2) NOT NULL DEFAULT 0.00, payment_method ENUM('bank_transfer') NOT NULL DEFAULT 'bank_transfer', payment_status ENUM('pending','proof_submitted','confirmed','failed') NOT NULL DEFAULT 'pending', order_status ENUM('pending_payment','processing','ready_for_pickup','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'pending_payment', customer_name VARCHAR(255) DEFAULT NULL, customer_email VARCHAR(255) DEFAULT NULL, customer_phone VARCHAR(20) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_orders_zone FOREIGN KEY (delivery_zone_id) REFERENCES delivery_zones(id) ON DELETE SET NULL, CONSTRAINT fk_orders_address FOREIGN KEY (delivery_address_id) REFERENCES addresses(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 11. order_items -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS order_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, variant_id INT UNSIGNED DEFAULT NULL, quantity INT UNSIGNED NOT NULL DEFAULT 1, unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, line_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_oi_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT, CONSTRAINT fk_oi_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 12. order_item_addons (pivot) -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS order_item_addons ( order_item_id INT UNSIGNED NOT NULL, add_on_id INT UNSIGNED NOT NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'snapshot', custom_message TEXT DEFAULT NULL COMMENT 'for gift cards', PRIMARY KEY (order_item_id, add_on_id), CONSTRAINT fk_oia_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE, CONSTRAINT fk_oia_addon FOREIGN KEY (add_on_id) REFERENCES add_ons(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 13. payment_proofs -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS payment_proofs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, image_url VARCHAR(512) NOT NULL, submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, verified_by INT UNSIGNED DEFAULT NULL, verified_at TIMESTAMP DEFAULT NULL, CONSTRAINT fk_proof_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, CONSTRAINT fk_proof_verifier FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 14. wishlists -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS wishlists ( user_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, PRIMARY KEY (user_id, product_id), CONSTRAINT fk_wish_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_wish_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 15. reviews (optional — phase 2) -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS reviews ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, rating TINYINT UNSIGNED NOT NULL COMMENT '1-5', comment TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_rev_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, CONSTRAINT fk_rev_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 16. settings (business contact / bank details / etc.) -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS settings ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(255) NOT NULL UNIQUE, setting_value TEXT DEFAULT NULL, group_name VARCHAR(100) DEFAULT 'general' COMMENT 'bank_details, contact, social, etc.' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 17. blocked_dates (optional — phase 2) -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS blocked_dates ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, blocked_date DATE NOT NULL, time_slot VARCHAR(100) DEFAULT NULL, max_orders INT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1; -- ────────────────────────────────────────────────────────────── -- 18. activity_logs -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS activity_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, staff_id INT UNSIGNED NOT NULL, action VARCHAR(100) NOT NULL, description TEXT, ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_logs_staff FOREIGN KEY (staff_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ────────────────────────────────────────────────────────────── -- 19. stock_logs -- ────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS stock_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, variant_id INT UNSIGNED NOT NULL, quantity INT NOT NULL, movement_type ENUM('addition','subtraction','correction','return','damage') NOT NULL, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_stock_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CANCEL
Name
Type
Size
Modified
Actions
↩ ..
DIR
—
—
📁 migrations/
DIR
—
2026-07-15 09:38
📄 schema.sql
SQL
18.1 KB
2026-07-15 09:17
EDIT
📄 seeds.sql
SQL
5.5 KB
2026-07-15 09:17
EDIT