-- Sanitized development baseline for legacy PHP module
-- Synthetic data only. Safe for local development and version control.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `legacy_module_dev` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `legacy_module_dev`;

DROP TABLE IF EXISTS `oc_product_queue`;
DROP TABLE IF EXISTS `oc_product_description`;
DROP TABLE IF EXISTS `oc_product`;
DROP TABLE IF EXISTS `oc_attribute_description`;
DROP TABLE IF EXISTS `oc_tax_class`;
DROP TABLE IF EXISTS `oc_url_alias`;
DROP TABLE IF EXISTS `oc_manufacturer_to_store`;
DROP TABLE IF EXISTS `oc_manufacturer`;
DROP TABLE IF EXISTS `oc_category_to_store`;
DROP TABLE IF EXISTS `oc_category_description`;
DROP TABLE IF EXISTS `oc_category`;

CREATE TABLE `oc_category` (
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_category_description` (
  `category_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`category_id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_category_to_store` (
  `category_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_manufacturer` (
  `manufacturer_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(3) NOT NULL DEFAULT 0,
  PRIMARY KEY (`manufacturer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_manufacturer_to_store` (
  `manufacturer_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  PRIMARY KEY (`manufacturer_id`,`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_url_alias` (
  `url_alias_id` int(11) NOT NULL AUTO_INCREMENT,
  `query` varchar(255) NOT NULL,
  `keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`url_alias_id`),
  UNIQUE KEY `uq_keyword` (`keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_tax_class` (
  `tax_class_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) NOT NULL,
  PRIMARY KEY (`tax_class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_attribute_description` (
  `attribute_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`attribute_id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `ean` varchar(14) NOT NULL DEFAULT '',
  `image` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_product_description` (
  `product_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `meta_description` varchar(255) NOT NULL DEFAULT '',
  `meta_keyword` varchar(255) NOT NULL DEFAULT '',
  `tag` text NOT NULL,
  `u_title` varchar(255) NOT NULL DEFAULT '',
  `u_h1` varchar(255) NOT NULL DEFAULT '',
  `u_h2` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`product_id`,`language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `oc_product_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `processed` tinyint(1) DEFAULT 0,
  `processed_at` datetime DEFAULT NULL,
  `result_es` tinyint(1) DEFAULT 0,
  `result_en` tinyint(1) DEFAULT 0,
  `needs_verify` tinyint(1) DEFAULT 0,
  `log` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `oc_category` (`category_id`) VALUES
(1),
(2),
(3);

INSERT INTO `oc_category_description` (`category_id`, `language_id`, `name`) VALUES
(1, 4, 'Despensa ecológica'),
(2, 4, 'Suplementos naturales'),
(3, 4, 'Cuidado personal');

INSERT INTO `oc_category_to_store` (`category_id`, `store_id`) VALUES
(1, 0),
(2, 0),
(3, 0);

INSERT INTO `oc_manufacturer` (`manufacturer_id`, `name`, `image`, `sort_order`) VALUES
(1, 'Herbolario Demo', '', 0),
(2, 'Bio Sample', '', 0),
(3, 'Natural Test Labs', '', 0);

INSERT INTO `oc_manufacturer_to_store` (`manufacturer_id`, `store_id`) VALUES
(1, 0),
(2, 0),
(3, 0);

INSERT INTO `oc_url_alias` (`url_alias_id`, `query`, `keyword`) VALUES
(1, 'manufacturer_id=1', 'herbolario-demo'),
(2, 'manufacturer_id=2', 'bio-sample'),
(3, 'manufacturer_id=3', 'natural-test-labs');

INSERT INTO `oc_tax_class` (`tax_class_id`, `title`) VALUES
(1, 'IVA 4%'),
(2, 'IVA 10%'),
(3, 'IVA 21%');

INSERT INTO `oc_attribute_description` (`attribute_id`, `language_id`, `name`) VALUES
(1, 4, 'bio'),
(2, 4, 'vegano'),
(3, 4, 'sin-gluten'),
(4, 4, 'sin-azucar'),
(5, 4, 'rico-en-fibra'),
(6, 4, 'sin-lactosa'),
(7, 4, 'cruelty-free'),
(8, 4, 'apto-infantil');

INSERT INTO `oc_product` (`product_id`, `ean`, `image`, `status`) VALUES
(1001, '8437000000011', 'catalog/demo/te-verde-demo.jpg', 1),
(1002, '8437000000028', 'catalog/demo/magnesio-demo.jpg', 1),
(1003, '8437000000035', 'catalog/demo/jabon-demo.jpg', 1);

INSERT INTO `oc_product_description` (`product_id`, `language_id`, `name`, `description`, `meta_description`, `meta_keyword`, `tag`, `u_title`, `u_h1`, `u_h2`) VALUES
(1001, 4, 'Té verde ecológico demo', '<p>Infusión demo para pruebas locales.</p>', 'Producto demo para entorno local.', '', '', 'Té verde ecológico demo', 'Té verde ecológico demo', 'Beneficios del té verde demo'),
(1001, 1, 'Demo organic green tea', '<p>Demo infusion for local development.</p>', 'Demo product for local environment.', '', '', 'Demo organic green tea', 'Demo organic green tea', 'Benefits of demo green tea'),
(1002, 4, 'Magnesio natural demo', '<p>Complemento demo para pruebas locales.</p>', 'Suplemento demo para entorno local.', '', '', 'Magnesio natural demo', 'Magnesio natural demo', 'Beneficios del magnesio demo'),
(1002, 1, 'Demo natural magnesium', '<p>Demo supplement for local development.</p>', 'Demo supplement for local environment.', '', '', 'Demo natural magnesium', 'Demo natural magnesium', 'Benefits of demo magnesium'),
(1003, 4, 'Jabón suave demo', '<p>Producto cosmético demo para pruebas locales.</p>', 'Cosmético demo para entorno local.', '', '', 'Jabón suave demo', 'Jabón suave demo', 'Cuidado suave demo'),
(1003, 1, 'Demo gentle soap', '<p>Demo cosmetic product for local development.</p>', 'Demo cosmetic for local environment.', '', '', 'Demo gentle soap', 'Demo gentle soap', 'Gentle care demo');

COMMIT;