-- Sistema de Supermercados y Tiendas V3.0
-- Script de creación de base de datos

-- Crear base de datos
CREATE DATABASE IF NOT EXISTS tiendabd CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tiendabd;

-- Tabla de clientes
CREATE TABLE IF NOT EXISTS clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cedula_ruc VARCHAR(20) UNIQUE NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    telefono VARCHAR(20),
    ciudad VARCHAR(100),
    fecha_registro DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cedula_ruc (cedula_ruc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de usuarios administrativos
CREATE TABLE IF NOT EXISTS usuarios_admin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    rol ENUM('administrador', 'comercial', 'colaborador') NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    activo BOOLEAN DEFAULT TRUE,
    INDEX idx_username (username),
    INDEX idx_rol (rol)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de productos
CREATE TABLE IF NOT EXISTS productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo_barras VARCHAR(50) UNIQUE NOT NULL,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    precio DECIMAL(12,2) NOT NULL,
    stock INT DEFAULT 0,
    imagen VARCHAR(255),
    categoria VARCHAR(50),
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    creado_por INT,
    activo BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (creado_por) REFERENCES usuarios_admin(id) ON DELETE SET NULL,
    INDEX idx_codigo_barras (codigo_barras),
    INDEX idx_nombre (nombre),
    INDEX idx_categoria (categoria)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de compras
CREATE TABLE IF NOT EXISTS compras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    fecha_compra DATETIME DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    estado ENUM('activa', 'finalizada') DEFAULT 'activa',
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
    INDEX idx_cliente_id (cliente_id),
    INDEX idx_fecha_compra (fecha_compra),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de detalle de compras
CREATE TABLE IF NOT EXISTS detalle_compras (
    id INT AUTO_INCREMENT PRIMARY KEY,
    compra_id INT NOT NULL,
    producto_id INT NOT NULL,
    cantidad INT NOT NULL DEFAULT 1,
    precio_unitario DECIMAL(12,2) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (compra_id) REFERENCES compras(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    INDEX idx_compra_id (compra_id),
    INDEX idx_producto_id (producto_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de promociones
CREATE TABLE IF NOT EXISTS promociones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT,
    imagen VARCHAR(255),
    fecha_inicio DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_fin DATETIME,
    activo BOOLEAN DEFAULT TRUE,
    creado_por INT,
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creado_por) REFERENCES usuarios_admin(id) ON DELETE SET NULL,
    INDEX idx_activo (activo),
    INDEX idx_fecha_inicio (fecha_inicio),
    INDEX idx_fecha_fin (fecha_fin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de mensajes emergentes
CREATE TABLE IF NOT EXISTS mensajes_emergentes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mensaje TEXT NOT NULL,
    tipo ENUM('oferta', 'promocion', 'anuncio', 'alerta') DEFAULT 'anuncio',
    fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_expiracion DATETIME,
    activo BOOLEAN DEFAULT TRUE,
    creado_por INT,
    FOREIGN KEY (creado_por) REFERENCES usuarios_admin(id) ON DELETE SET NULL,
    INDEX idx_activo (activo),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de sesiones de clientes (para tracking de compras activas)
CREATE TABLE IF NOT EXISTS sesiones_clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    compra_id INT,
    fecha_inicio DATETIME DEFAULT CURRENT_TIMESTAMP,
    fecha_ultimo_acceso DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    activa BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE,
    FOREIGN KEY (compra_id) REFERENCES compras(id) ON DELETE SET NULL,
    INDEX idx_cliente_id (cliente_id),
    INDEX idx_activa (activa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar usuario administrador por defecto
-- Contraseña: 2025 (hasheada con password_hash)
INSERT INTO usuarios_admin (username, password, rol, nombre, activo) 
VALUES ('Tiendas', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'administrador', 'Administrador Principal', TRUE);

-- Insertar algunos productos de ejemplo
INSERT INTO productos (codigo_barras, nombre, descripcion, precio, stock, categoria, creado_por) VALUES
('7891234567890', 'Arroz Blanco 1kg', 'Arroz de primera calidad', 12500.00, 100, 'Alimentos', 1),
('7891234567891', 'Aceite Vegetal 900ml', 'Aceite vegetal comestible', 18900.00, 50, 'Alimentos', 1),
('7891234567892', 'Azúcar Refinada 1kg', 'Azúcar blanca refinada', 8500.00, 80, 'Alimentos', 1),
('7891234567893', 'Leche Entera 1L', 'Leche entera pasteurizada', 9800.00, 60, 'Lácteos', 1),
('7891234567894', 'Pan Blanco 500g', 'Pan blanco fresco', 6500.00, 40, 'Panadería', 1),
('7891234567895', 'Fideos 500g', 'Fideos tipo tallarín', 7200.00, 90, 'Alimentos', 1),
('7891234567896', 'Sal Fina 1kg', 'Sal de mesa refinada', 3500.00, 120, 'Condimentos', 1),
('7891234567897', 'Jabón en Barra 200g', 'Jabón de tocador', 4800.00, 70, 'Higiene', 1),
('7891234567898', 'Detergente 500ml', 'Detergente líquido para ropa', 15600.00, 45, 'Limpieza', 1),
('7891234567899', 'Papel Higiénico x4', 'Papel higiénico doble hoja', 11200.00, 85, 'Higiene', 1);

-- Insertar una promoción de ejemplo
INSERT INTO promociones (titulo, descripcion, activo, creado_por) VALUES
('¡Bienvenido a nuestra tienda!', 'Aprovecha nuestras ofertas especiales todos los días', TRUE, 1);

-- Insertar un mensaje emergente de ejemplo
INSERT INTO mensajes_emergentes (mensaje, tipo, activo, creado_por) VALUES
('¡Ofertas del día! Descuentos especiales en productos seleccionados', 'oferta', TRUE, 1);

-- Crear usuario de base de datos
-- Nota: Este comando debe ejecutarse con privilegios de root
CREATE USER IF NOT EXISTS 'tiendas2025'@'localhost' IDENTIFIED BY 'product@s.py';
GRANT ALL PRIVILEGES ON tiendabd.* TO 'tiendas2025'@'localhost';
FLUSH PRIVILEGES;

-- Mostrar resumen de tablas creadas
SHOW TABLES;
