-- Estructura de la base de datos
CREATE DATABASE IF NOT EXISTS `fzdcxdef_demov1`;
USE `fzdcxdef_demov1`;

-- Tabla categories
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla products
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `price` DECIMAL(10,2) NOT NULL,
  `category_id` INT,
  `image_url` TEXT,
  `available` BOOLEAN DEFAULT true,
  `stock` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`)
);

-- Tabla orders
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `customer_name` VARCHAR(100),
  `customer_email` VARCHAR(100),
  `status` VARCHAR(20) DEFAULT 'pending',
  `total` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla order_items
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`),
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
);

-- Procedimiento para respaldar datos
DELIMITER //
CREATE PROCEDURE IF NOT EXISTS ExportData()
BEGIN
  -- Exportar datos de categories
  SELECT 'INSERT INTO categories (name) VALUES' AS '';
  SELECT CONCAT('("', name, '"),') AS ''
  FROM categories;

  -- Exportar datos de products
  SELECT 'INSERT INTO products (name, description, price, category_id, image_url, available, stock) VALUES' AS '';
  SELECT CONCAT('("', name, '", "', IFNULL(description, ''), '", ', price, ', ', 
         IFNULL(category_id, 'NULL'), ', "', IFNULL(image_url, ''), '", ', 
         IF(available, 'true', 'false'), ', ', stock, '),') AS ''
  FROM products;

  -- Exportar datos de orders
  SELECT 'INSERT INTO orders (customer_name, customer_email, status, total) VALUES' AS '';
  SELECT CONCAT('("', IFNULL(customer_name, ''), '", "', IFNULL(customer_email, ''), '", "', 
         status, '", ', total, '),') AS ''
  FROM orders;

  -- Exportar datos de order_items
  SELECT 'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES' AS '';
  SELECT CONCAT('(', order_id, ', ', product_id, ', ', quantity, ', ', price, '),') AS ''
  FROM order_items;
END //
DELIMITER ;