-- -----------------------------------------------------------------------------
-- Criação Completa das Tabelas do Módulo de Lotes
-- -----------------------------------------------------------------------------

-- Tabela: lotes
CREATE TABLE IF NOT EXISTS lotes (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255) ,
    tipo VARCHAR(100) ,
    referencia_tipo VARCHAR(100),      -- Tipo da entidade agrupadora (romaneio, pedido, null = genérico)
    referencia_id INTEGER,             -- ID da entidade agrupadora
    ativo BOOLEAN DEFAULT true,
    regras JSONB DEFAULT '{}'::jsonb,
    status VARCHAR(255),
    data_cadastro TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    block_filial_id INTEGER,
    block_usuario_id INTEGER,
    usuario_cadastro VARCHAR(255),
    data_atualizacao TIMESTAMP WITHOUT TIME ZONE,
    usuario_atualizacao VARCHAR(255),
    block_data TIMESTAMP WITHOUT TIME ZONE,
    block_quem VARCHAR(255),
    block INTEGER,
    block_empresa_id INTEGER
);

-- Tabela: lotes_entidades
CREATE TABLE IF NOT EXISTS lotes_entidades (
    id SERIAL PRIMARY KEY,
    lote_id INTEGER REFERENCES lotes(id) ON DELETE CASCADE,
    pedido_id integer,
    romaneio_id integer,
    entidade_tipo VARCHAR(100) ,
    entidade_id INTEGER ,
    quantidade NUMERIC(10,4),
    status VARCHAR(50)  DEFAULT 'pendente',
    referencia_origem_tipo VARCHAR(100),
    referencia_origem_id INTEGER,
    observacao TEXT,
    inserido_por INTEGER,
    consumido_por INTEGER,
    data_consumo TIMESTAMP,
        data_cadastro TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    block_filial_id INTEGER,
    block_usuario_id INTEGER,
    usuario_cadastro VARCHAR(255),
    data_atualizacao TIMESTAMP WITHOUT TIME ZONE,
    usuario_atualizacao VARCHAR(255),
    block_data TIMESTAMP WITHOUT TIME ZONE,
    block_quem VARCHAR(255),
    block INTEGER,
    block_empresa_id INTEGER 
);

-- Tabela: lotes_movimentacoes
CREATE TABLE IF NOT EXISTS lotes_movimentacoes (
    id SERIAL PRIMARY KEY,
    lote_entidade_id INTEGER REFERENCES lotes_entidades(id) ON DELETE CASCADE,
    tipo_movimentacao VARCHAR(50) ,
    quantidade_movimentada NUMERIC(10,4) ,
    quantidade_saldo_anterior NUMERIC(10,4) ,
    quantidade_saldo_novo NUMERIC(10,4) ,
    usuario_id INTEGER,
    origem_api VARCHAR(100),
    observacao TEXT,
    
    -- Colunas de Controle Galaxia Padrão
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    block_filial_id INTEGER ,
    block_usuario_id INTEGER,
    usuario_cadastro INTEGER,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    usuario_atualizacao INTEGER,
    block_data TIMESTAMP,
    block_quem TEXT,
    block INTEGER ,
    block_empresa_id INTEGER ,
    block_cliente_id INTEGER 
);

-- Índices de Otimização e Busca
CREATE INDEX IF NOT EXISTS idx_lotes_tipo ON lotes(tipo);
CREATE INDEX IF NOT EXISTS idx_lotes_nome ON lotes(nome);
CREATE INDEX IF NOT EXISTS idx_lotes_tipo_referencia ON lotes(tipo, referencia_tipo, referencia_id);
CREATE INDEX IF NOT EXISTS idx_lotes_entidades_lote_id ON lotes_entidades(lote_id);
CREATE INDEX IF NOT EXISTS idx_lotes_entidades_referencia ON lotes_entidades(referencia_origem_tipo, referencia_origem_id);
CREATE INDEX IF NOT EXISTS idx_lotes_movimentacoes_entidade_id ON lotes_movimentacoes(lote_entidade_id);
CREATE INDEX IF NOT EXISTS idx_lotes_entidades_pedido ON lotes_entidades(pedido_id);
CREATE INDEX IF NOT EXISTS idx_lotes_entidades_romaneio ON lotes_entidades(romaneio_id);

-- Migration: Adicionar colunas de referência na tabela lotes (se já existir)
ALTER TABLE lotes ADD COLUMN IF NOT EXISTS referencia_tipo VARCHAR(100);
ALTER TABLE lotes ADD COLUMN IF NOT EXISTS referencia_id INTEGER;
CREATE INDEX IF NOT EXISTS idx_lotes_tipo_referencia ON lotes(tipo, referencia_tipo, referencia_id);
