-- =============================================
-- ParcelFlow Shop Database Schema
-- Generated: April 14, 2026
-- =============================================

-- -----------------------------------------
-- Users & Authentication
-- -----------------------------------------

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `uuid` VARCHAR(50) NOT NULL,
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `name` VARCHAR(200) NOT NULL,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `hear_about_us` VARCHAR(255) DEFAULT '',
    `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
    `deleted` ENUM('Yes', 'No') DEFAULT 'No',
    `last_login` DATETIME DEFAULT NULL,
    `date_created` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `login_sessions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_type` VARCHAR(50) NOT NULL DEFAULT 'User',
    `user_id` INT NOT NULL,
    `device_id` VARCHAR(255) DEFAULT NULL,
    `device_name` VARCHAR(255) DEFAULT NULL,
    `token` VARCHAR(255) NOT NULL,
    `user_ses_id` VARCHAR(255) NOT NULL,
    `mode` VARCHAR(50) DEFAULT 'app',
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `login_date_time` DATETIME NOT NULL,
    `logout_date_time` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `verification_otp` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(150) NOT NULL,
    `code` VARCHAR(10) NOT NULL,
    `valid` ENUM('Yes', 'No') DEFAULT 'Yes',
    `expiration_date_time` DATETIME NOT NULL,
    `date_time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------
-- Customers
-- -----------------------------------------

CREATE TABLE `customers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(100) DEFAULT NULL,
    `last_name` VARCHAR(100) DEFAULT NULL,
    `email` VARCHAR(150) DEFAULT NULL,
    `phone` VARCHAR(30) DEFAULT NULL,
    `store_id` INT DEFAULT NULL,
    `account_type` ENUM('Individual', 'Business') DEFAULT 'Individual',
    `status` ENUM('Active', 'Inactive') DEFAULT 'Active',
    `date_time` DATETIME DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `printers` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `price` DECIMAL(10,2) DEFAULT 0.00,
    `status` ENUM('Active', 'Inactive') DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------
-- Cart Tables
-- -----------------------------------------

CREATE TABLE `themes_cart` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `theme_id` INT NOT NULL,
    `store_id` INT NOT NULL,
    `pricing` ENUM('Free', 'Premium') DEFAULT 'Free'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `printer_cart` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `printer_id` INT NOT NULL,
    `color` VARCHAR(50) NOT NULL,
    `quantity` INT NOT NULL DEFAULT 1,
    `paper` VARCHAR(100) NOT NULL,
    FOREIGN KEY (`printer_id`) REFERENCES `printers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `apps_cart` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `type` ENUM('E-Commerce', 'Logistics', 'Restaurant') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cart` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `themes_cart_id` INT DEFAULT NULL,
    `printer_cart_id` INT DEFAULT NULL,
    `apps_cart_id` INT DEFAULT NULL,
    `user_id` INT DEFAULT NULL,
    `user_ses_id` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('Active', 'Checked Out') DEFAULT 'Active',
    FOREIGN KEY (`themes_cart_id`) REFERENCES `themes_cart`(`id`),
    FOREIGN KEY (`printer_cart_id`) REFERENCES `printer_cart`(`id`),
    FOREIGN KEY (`apps_cart_id`) REFERENCES `apps_cart`(`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------
-- Orders & Delivery
-- -----------------------------------------

CREATE TABLE `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT DEFAULT NULL,
    `user_ses_id` VARCHAR(255) DEFAULT NULL,
    `order_reference` VARCHAR(50) NOT NULL UNIQUE,
    `has_physical_items` ENUM('Yes', 'No') DEFAULT 'No',
    `status` ENUM('Pending', 'Processing', 'Completed', 'Cancelled') DEFAULT 'Pending',
    `payment_status` ENUM('Unpaid', 'Paid') DEFAULT 'Unpaid',
    `date_paid` DATETIME DEFAULT NULL,
    `date_created` DATETIME NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT NOT NULL,
    `themes_cart_id` INT DEFAULT NULL,
    `printer_cart_id` INT DEFAULT NULL,
    `apps_cart_id` INT DEFAULT NULL,
    `date_created` DATETIME NOT NULL,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`),
    FOREIGN KEY (`themes_cart_id`) REFERENCES `themes_cart`(`id`),
    FOREIGN KEY (`printer_cart_id`) REFERENCES `printer_cart`(`id`),
    FOREIGN KEY (`apps_cart_id`) REFERENCES `apps_cart`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `delivery_addresses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT NOT NULL,
    `recipient_name` VARCHAR(200) NOT NULL,
    `phone` VARCHAR(30) NOT NULL,
    `address` VARCHAR(500) NOT NULL,
    `city` VARCHAR(100) NOT NULL,
    `state` VARCHAR(100) NOT NULL,
    `country` VARCHAR(100) NOT NULL,
    `postal_code` VARCHAR(20) DEFAULT NULL,
    `date_created` DATETIME NOT NULL,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------
-- Themes (Editor)
-- -----------------------------------------

CREATE TABLE `log_initial_report` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `report_type` VARCHAR(255) NOT NULL,
    `employee_id` VARCHAR(50) DEFAULT NULL,
    `transaction_id` VARCHAR(255) DEFAULT NULL,
    `server_request` TEXT DEFAULT NULL,
    `date_time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `log_error_report` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `report_type` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `transaction_id` VARCHAR(255) DEFAULT NULL,
    `server_request` TEXT DEFAULT NULL,
    `date_time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `log_successful_report` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `report_type` VARCHAR(255) NOT NULL,
    `employee_id` VARCHAR(50) DEFAULT NULL,
    `transaction_id` VARCHAR(255) DEFAULT NULL UNIQUE,
    `server_request` TEXT DEFAULT NULL,
    `server_response` TEXT DEFAULT NULL,
    `date_time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- -----------------------------------------
-- App Versioning
-- -----------------------------------------

CREATE TABLE `versions_admin_app` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `version` VARCHAR(20) NOT NULL,
    `status` ENUM('Active', 'Inactive') DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;