PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS usuarios (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    usuario TEXT NOT NULL COLLATE NOCASE UNIQUE,
    password_hash TEXT NOT NULL,
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1)),
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS configuracion (
    clave TEXT PRIMARY KEY,
    valor TEXT NOT NULL,
    actualizado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS proveedores (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    telefono TEXT NOT NULL DEFAULT '',
    correo TEXT,
    direccion TEXT,
    observaciones TEXT,
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1)),
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categorias (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL COLLATE NOCASE UNIQUE,
    slug TEXT NOT NULL DEFAULT '',
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1)),
    orden_catalogo INTEGER NOT NULL DEFAULT 0,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS clientes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nombre TEXT NOT NULL,
    telefono TEXT NOT NULL DEFAULT '',
    correo TEXT,
    direccion TEXT,
    notas TEXT,
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1)),
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS productos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    proveedor_id INTEGER NOT NULL,
    categoria_id INTEGER,
    nombre TEXT NOT NULL,
    slug TEXT NOT NULL DEFAULT '',
    sku TEXT NOT NULL COLLATE NOCASE UNIQUE,
    codigo_barras TEXT COLLATE NOCASE UNIQUE,
    descripcion_corta TEXT NOT NULL DEFAULT '',
    descripcion TEXT,
    foto TEXT,
    costo_referencia_centavos INTEGER NOT NULL DEFAULT 0 CHECK (costo_referencia_centavos >= 0),
    precio_venta_centavos INTEGER NOT NULL DEFAULT 0 CHECK (precio_venta_centavos >= 0),
    precio_comparacion_centavos INTEGER CHECK (precio_comparacion_centavos IS NULL OR precio_comparacion_centavos >= 0),
    stock_minimo INTEGER NOT NULL DEFAULT 0 CHECK (stock_minimo >= 0),
    activo INTEGER NOT NULL DEFAULT 1 CHECK (activo IN (0, 1)),
    destacado INTEGER NOT NULL DEFAULT 0 CHECK (destacado IN (0, 1)),
    orden_catalogo INTEGER NOT NULL DEFAULT 0,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (categoria_id) REFERENCES categorias(id)
);

CREATE TABLE IF NOT EXISTS ingresos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    proveedor_id INTEGER NOT NULL,
    fecha TEXT NOT NULL,
    remito TEXT,
    observaciones TEXT,
    estado TEXT NOT NULL DEFAULT 'confirmado' CHECK (estado IN ('confirmado', 'anulado')),
    creado_por INTEGER NOT NULL,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (creado_por) REFERENCES usuarios(id)
);

CREATE TABLE IF NOT EXISTS ingreso_detalles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ingreso_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
    costo_unitario_centavos INTEGER NOT NULL CHECK (costo_unitario_centavos >= 0),
    precio_sugerido_centavos INTEGER NOT NULL CHECK (precio_sugerido_centavos >= 0),
    FOREIGN KEY (ingreso_id) REFERENCES ingresos(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE TABLE IF NOT EXISTS lotes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ingreso_detalle_id INTEGER NOT NULL UNIQUE,
    proveedor_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    fecha_ingreso TEXT NOT NULL,
    cantidad_recibida INTEGER NOT NULL CHECK (cantidad_recibida > 0),
    cantidad_disponible INTEGER NOT NULL CHECK (cantidad_disponible >= 0),
    costo_unitario_centavos INTEGER NOT NULL CHECK (costo_unitario_centavos >= 0),
    precio_sugerido_centavos INTEGER NOT NULL CHECK (precio_sugerido_centavos >= 0),
    estado TEXT NOT NULL DEFAULT 'abierto' CHECK (estado IN ('abierto', 'agotado', 'devuelto', 'anulado')),
    FOREIGN KEY (ingreso_detalle_id) REFERENCES ingreso_detalles(id),
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE TABLE IF NOT EXISTS ventas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    numero TEXT NOT NULL UNIQUE,
    fecha_hora TEXT NOT NULL,
    medio_pago TEXT NOT NULL CHECK (medio_pago IN ('efectivo', 'transferencia', 'debito', 'credito', 'otro')),
    subtotal_centavos INTEGER NOT NULL CHECK (subtotal_centavos >= 0),
    descuento_centavos INTEGER NOT NULL DEFAULT 0 CHECK (descuento_centavos >= 0),
    total_centavos INTEGER NOT NULL CHECK (total_centavos >= 0),
    costo_proveedor_centavos INTEGER NOT NULL CHECK (costo_proveedor_centavos >= 0),
    ganancia_centavos INTEGER NOT NULL,
    observaciones TEXT,
    cliente_id INTEGER,
    estado TEXT NOT NULL DEFAULT 'confirmada' CHECK (estado IN ('confirmada', 'anulada')),
    creado_por INTEGER NOT NULL,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creado_por) REFERENCES usuarios(id),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

CREATE TABLE IF NOT EXISTS venta_detalles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    venta_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    proveedor_id INTEGER NOT NULL,
    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
    precio_unitario_centavos INTEGER NOT NULL CHECK (precio_unitario_centavos >= 0),
    subtotal_centavos INTEGER NOT NULL CHECK (subtotal_centavos >= 0),
    descuento_centavos INTEGER NOT NULL DEFAULT 0 CHECK (descuento_centavos >= 0),
    total_centavos INTEGER NOT NULL CHECK (total_centavos >= 0),
    costo_proveedor_centavos INTEGER NOT NULL CHECK (costo_proveedor_centavos >= 0),
    ganancia_centavos INTEGER NOT NULL,
    FOREIGN KEY (venta_id) REFERENCES ventas(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id),
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id)
);

CREATE TABLE IF NOT EXISTS venta_asignaciones_lote (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    venta_detalle_id INTEGER NOT NULL,
    lote_id INTEGER NOT NULL,
    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
    cantidad_devuelta INTEGER NOT NULL DEFAULT 0 CHECK (cantidad_devuelta >= 0 AND cantidad_devuelta <= cantidad),
    costo_unitario_centavos INTEGER NOT NULL CHECK (costo_unitario_centavos >= 0),
    FOREIGN KEY (venta_detalle_id) REFERENCES venta_detalles(id),
    FOREIGN KEY (lote_id) REFERENCES lotes(id)
);

CREATE TABLE IF NOT EXISTS devoluciones_clientes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    venta_id INTEGER NOT NULL,
    fecha_hora TEXT NOT NULL,
    motivo TEXT NOT NULL,
    reingresa_stock INTEGER NOT NULL CHECK (reingresa_stock IN (0, 1)),
    devuelve_dinero INTEGER NOT NULL CHECK (devuelve_dinero IN (0, 1)),
    monto_devuelto_centavos INTEGER NOT NULL DEFAULT 0 CHECK (monto_devuelto_centavos >= 0),
    importe_venta_revertido_centavos INTEGER NOT NULL DEFAULT 0 CHECK (importe_venta_revertido_centavos >= 0),
    costo_proveedor_revertido_centavos INTEGER NOT NULL DEFAULT 0 CHECK (costo_proveedor_revertido_centavos >= 0),
    ganancia_revertida_centavos INTEGER NOT NULL DEFAULT 0,
    observaciones TEXT,
    estado TEXT NOT NULL DEFAULT 'confirmada' CHECK (estado IN ('confirmada', 'anulada')),
    creado_por INTEGER NOT NULL,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (venta_id) REFERENCES ventas(id),
    FOREIGN KEY (creado_por) REFERENCES usuarios(id)
);

CREATE TABLE IF NOT EXISTS devolucion_cliente_detalles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    devolucion_id INTEGER NOT NULL,
    venta_detalle_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
    importe_venta_revertido_centavos INTEGER NOT NULL CHECK (importe_venta_revertido_centavos >= 0),
    costo_proveedor_revertido_centavos INTEGER NOT NULL CHECK (costo_proveedor_revertido_centavos >= 0),
    ganancia_revertida_centavos INTEGER NOT NULL,
    FOREIGN KEY (devolucion_id) REFERENCES devoluciones_clientes(id),
    FOREIGN KEY (venta_detalle_id) REFERENCES venta_detalles(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE TABLE IF NOT EXISTS devoluciones_proveedor (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    proveedor_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    lote_id INTEGER NOT NULL,
    fecha TEXT NOT NULL,
    cantidad INTEGER NOT NULL CHECK (cantidad > 0),
    costo_unitario_centavos INTEGER NOT NULL CHECK (costo_unitario_centavos >= 0),
    motivo TEXT NOT NULL,
    estado TEXT NOT NULL DEFAULT 'confirmada' CHECK (estado IN ('confirmada', 'anulada')),
    creado_por INTEGER NOT NULL,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id),
    FOREIGN KEY (lote_id) REFERENCES lotes(id),
    FOREIGN KEY (creado_por) REFERENCES usuarios(id)
);

CREATE TABLE IF NOT EXISTS liquidaciones (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    proveedor_id INTEGER NOT NULL,
    fecha_desde TEXT NOT NULL,
    fecha_hasta TEXT NOT NULL,
    estado TEXT NOT NULL DEFAULT 'abierta' CHECK (estado IN ('abierta', 'parcialmente_pagada', 'pagada', 'cerrada', 'cancelada')),
    total_venta_centavos INTEGER NOT NULL DEFAULT 0,
    costo_proveedor_centavos INTEGER NOT NULL DEFAULT 0,
    entregado_hermana_centavos INTEGER NOT NULL DEFAULT 0,
    saldo_pendiente_centavos INTEGER NOT NULL DEFAULT 0,
    ganancia_centavos INTEGER NOT NULL DEFAULT 0,
    ajustes_centavos INTEGER NOT NULL DEFAULT 0,
    observaciones TEXT,
    creado_por INTEGER NOT NULL,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cerrado_en TEXT,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (creado_por) REFERENCES usuarios(id)
);

CREATE TABLE IF NOT EXISTS liquidacion_detalles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    liquidacion_id INTEGER NOT NULL,
    producto_id INTEGER NOT NULL,
    unidades_recibidas INTEGER NOT NULL DEFAULT 0,
    unidades_vendidas INTEGER NOT NULL DEFAULT 0,
    unidades_devueltas_cliente INTEGER NOT NULL DEFAULT 0,
    unidades_devueltas_proveedor INTEGER NOT NULL DEFAULT 0,
    stock_restante INTEGER NOT NULL DEFAULT 0,
    total_venta_centavos INTEGER NOT NULL DEFAULT 0,
    costo_proveedor_centavos INTEGER NOT NULL DEFAULT 0,
    ganancia_centavos INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (liquidacion_id) REFERENCES liquidaciones(id),
    FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE TABLE IF NOT EXISTS entregas_hermana (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    proveedor_id INTEGER NOT NULL,
    liquidacion_id INTEGER,
    fecha TEXT NOT NULL,
    monto_centavos INTEGER NOT NULL CHECK (monto_centavos > 0),
    medio TEXT NOT NULL CHECK (medio IN ('efectivo', 'transferencia', 'otro')),
    comprobante TEXT,
    observaciones TEXT,
    estado TEXT NOT NULL DEFAULT 'confirmada' CHECK (estado IN ('confirmada', 'anulada')),
    creado_por INTEGER NOT NULL,
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (proveedor_id) REFERENCES proveedores(id),
    FOREIGN KEY (liquidacion_id) REFERENCES liquidaciones(id),
    FOREIGN KEY (creado_por) REFERENCES usuarios(id)
);

CREATE TABLE IF NOT EXISTS auditoria (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fecha_hora TEXT NOT NULL,
    operacion TEXT NOT NULL,
    entidad TEXT NOT NULL,
    entidad_id INTEGER,
    datos_principales TEXT NOT NULL,
    usuario_id INTEGER,
    usuario_nombre TEXT NOT NULL,
    ip TEXT,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
);

CREATE TABLE IF NOT EXISTS migraciones (
    id TEXT PRIMARY KEY,
    aplicada_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS eventos_pedido (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    total_centavos INTEGER NOT NULL CHECK (total_centavos >= 0),
    items_json TEXT NOT NULL,
    origen TEXT NOT NULL DEFAULT 'web_whatsapp',
    creado_en TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_productos_proveedor ON productos(proveedor_id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_categorias_slug ON categorias(slug) WHERE slug != '';
CREATE UNIQUE INDEX IF NOT EXISTS uq_productos_slug ON productos(slug) WHERE slug != '';
CREATE INDEX IF NOT EXISTS idx_categorias_catalogo ON categorias(activo, orden_catalogo, nombre);
CREATE INDEX IF NOT EXISTS idx_productos_catalogo ON productos(activo, destacado, orden_catalogo, nombre);
CREATE INDEX IF NOT EXISTS idx_eventos_pedido_fecha ON eventos_pedido(creado_en);
CREATE INDEX IF NOT EXISTS idx_eventos_pedido_session ON eventos_pedido(session_id);
CREATE INDEX IF NOT EXISTS idx_clientes_nombre ON clientes(nombre COLLATE NOCASE);
CREATE INDEX IF NOT EXISTS idx_clientes_activo ON clientes(activo);
CREATE INDEX IF NOT EXISTS idx_lotes_producto_disponible ON lotes(producto_id, cantidad_disponible, fecha_ingreso);
CREATE INDEX IF NOT EXISTS idx_ventas_fecha ON ventas(fecha_hora);
CREATE INDEX IF NOT EXISTS idx_ventas_cliente_fecha ON ventas(cliente_id, fecha_hora);
CREATE INDEX IF NOT EXISTS idx_venta_detalles_proveedor ON venta_detalles(proveedor_id);
CREATE INDEX IF NOT EXISTS idx_entregas_proveedor_fecha ON entregas_hermana(proveedor_id, fecha);
CREATE INDEX IF NOT EXISTS idx_liquidaciones_proveedor_fecha ON liquidaciones(proveedor_id, fecha_desde, fecha_hasta);
CREATE INDEX IF NOT EXISTS idx_auditoria_fecha ON auditoria(fecha_hora);
