-- MARIMAR DIGITAL · Esquema base v0.1 · MySQL 8/MariaDB 10.4+
CREATE DATABASE IF NOT EXISTS marimar_digital CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE marimar_digital;

CREATE TABLE IF NOT EXISTS users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 phone VARCHAR(40) NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('cliente','admin','propietario','preparador','caja','delivery') NOT NULL DEFAULT 'cliente',
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS categories (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(90) NOT NULL,
 slug VARCHAR(100) NOT NULL UNIQUE,
 emoji VARCHAR(12) NOT NULL DEFAULT '🛒',
 sort_order INT NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS products (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 category_id INT UNSIGNED NOT NULL,
 sku VARCHAR(60) NOT NULL UNIQUE,
 name VARCHAR(180) NOT NULL,
 description TEXT NULL,
 image_path VARCHAR(255) NULL,
 unit_type ENUM('unidad','peso') NOT NULL DEFAULT 'unidad',
 -- Precio en centésimos por UNIDAD o por KILO.
 price_cents INT UNSIGNED NOT NULL,
 -- Existencia en unidades para 'unidad', en GRAMOS para 'peso'.
 stock_qty INT UNSIGNED NOT NULL DEFAULT 0,
 min_qty INT UNSIGNED NOT NULL DEFAULT 1,
 step_qty INT UNSIGNED NOT NULL DEFAULT 1,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_products_category (category_id,active),
 CONSTRAINT fk_products_categories FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_code VARCHAR(35) NULL UNIQUE,
 user_id INT UNSIGNED NOT NULL,
 delivery_method ENUM('retiro','delivery') NOT NULL,
 delivery_address TEXT NULL,
 pickup_at DATETIME NULL,
 notes VARCHAR(500) NULL,
 substitution_policy ENUM('contactar','autorizar','no_sustituir') NOT NULL DEFAULT 'contactar',
 payment_method ENUM('al_recibir') NOT NULL DEFAULT 'al_recibir',
 payment_status ENUM('pendiente','pagado') NOT NULL DEFAULT 'pendiente',
 status ENUM('nuevo','preparando','listo','en_camino','entregado','cancelado') NOT NULL DEFAULT 'nuevo',
 subtotal_cents BIGINT UNSIGNED NOT NULL,
 total_cents BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_orders_user (user_id,created_at),
 KEY idx_orders_status (status,created_at),
 CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

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,
 product_name VARCHAR(180) NOT NULL,
 unit_type ENUM('unidad','peso') NOT NULL,
 qty INT UNSIGNED NOT NULL,
 unit_price_cents INT UNSIGNED NOT NULL,
 line_total_cents BIGINT UNSIGNED NOT NULL,
 notes VARCHAR(250) NULL,
 KEY idx_order_items_order (order_id),
 CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id),
 CONSTRAINT fk_order_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS order_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id INT UNSIGNED NOT NULL,
 actor_user_id INT UNSIGNED NULL,
 previous_status VARCHAR(30) NULL,
 new_status VARCHAR(30) NOT NULL,
 detail VARCHAR(250) NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_order_history_order (order_id,created_at),
 CONSTRAINT fk_order_history_order FOREIGN KEY (order_id) REFERENCES orders(id),
 CONSTRAINT fk_order_history_actor FOREIGN KEY (actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS stock_movements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id INT UNSIGNED NOT NULL,
 delta_qty INT NOT NULL,
 balance_qty INT UNSIGNED NOT NULL,
 reason ENUM('pedido','cancelacion','ajuste') NOT NULL,
 order_id INT UNSIGNED NULL,
 actor_user_id INT UNSIGNED NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_stock_movements_product (product_id,created_at),
 CONSTRAINT fk_stock_movements_product FOREIGN KEY (product_id) REFERENCES products(id),
 CONSTRAINT fk_stock_movements_order FOREIGN KEY (order_id) REFERENCES orders(id),
 CONSTRAINT fk_stock_movements_actor FOREIGN KEY (actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB;