-- Ativar extensões necessárias
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Banco de Dados - Primeira Parte (Clientes, Endereços e Contatos)

-- Tabela de Clientes
CREATE TABLE clientes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tipo_pessoa VARCHAR(10) NOT NULL, -- 'CPF' ou 'CNPJ'
    cpf_cnpj VARCHAR(20) NOT NULL UNIQUE,
    inscricao_estadual VARCHAR(30),
    nome_razao_social VARCHAR(255) NOT NULL,
    nome_fantasia VARCHAR(255),
    email_principal VARCHAR(255),
    telefone_principal VARCHAR(30),
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de Endereços dos Clientes
CREATE TABLE enderecos_cliente (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cliente_id UUID REFERENCES clientes(id) ON DELETE CASCADE,
    tipo_endereco VARCHAR(20) NOT NULL, -- 'obra', 'faturamento'
    cep VARCHAR(10) NOT NULL,
    logradouro VARCHAR(255) NOT NULL,
    numero VARCHAR(20) NOT NULL,
    complemento VARCHAR(255),
    bairro VARCHAR(100) NOT NULL,
    cidade VARCHAR(100) NOT NULL,
    estado VARCHAR(50) NOT NULL,
    pais VARCHAR(50) DEFAULT 'Brasil',
    referencia VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de Contatos dos Clientes
CREATE TABLE contatos_cliente (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cliente_id UUID REFERENCES clientes(id) ON DELETE CASCADE,
    tipo_contato VARCHAR(20) NOT NULL, -- 'obra', 'faturamento', 'geral', 'outros'
    nome VARCHAR(255) NOT NULL,
    cargo VARCHAR(100),
    email VARCHAR(255),
    telefone VARCHAR(30),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de Caminhões
CREATE TABLE trucks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_empresa UUID REFERENCES empresas(id),
    placa VARCHAR(10) NOT NULL UNIQUE,
    modelo VARCHAR(100) NOT NULL,
    capacidade_m3 DECIMAL(5,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ativo',
    motorista_atual_id UUID REFERENCES employees(id),
    localizacao_atual VARCHAR(255),
    observacoes TEXT,
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de Manutenção de Caminhões
CREATE TABLE truck_maintenance (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_truck UUID REFERENCES trucks(id) NOT NULL,
    tipo VARCHAR(20) NOT NULL,
    descricao TEXT NOT NULL,
    fornecedor_id UUID REFERENCES suppliers(id),
    valor_custo DECIMAL(10,2) NOT NULL,
    data_manutencao DATE NOT NULL,
    data_pagamento DATE,
    observacoes TEXT,
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de Apólices de Seguro
CREATE TABLE insurance_policies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tipo VARCHAR(20) NOT NULL,
    id_relacionado UUID,
    seguradora VARCHAR(255) NOT NULL,
    cobertura TEXT,
    valor_total DECIMAL(10,2),
    valor_mensal DECIMAL(10,2),
    data_inicio DATE NOT NULL,
    data_fim DATE,
    status VARCHAR(20) NOT NULL,
    observacoes TEXT,
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de Contas Bancárias
CREATE TABLE bank_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nome_banco VARCHAR(100) NOT NULL,
    tipo_conta VARCHAR(20) NOT NULL, -- Ex: 'corrente', 'poupança', 'pagamento'
    agencia VARCHAR(20),
    numero_conta VARCHAR(30),
    titular VARCHAR(100),
    documento_titular VARCHAR(20),
    saldo_inicial DECIMAL(12,2) DEFAULT 0.00,
    saldo_atual DECIMAL(12,2) DEFAULT 0.00,
    data_abertura DATE,
    status VARCHAR(20) DEFAULT 'ativa', -- ativa, inativa, encerrada
    observacoes TEXT,
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- 1. Tabela de Contas Financeiras (caixa, bancos, cartão, etc)
CREATE TABLE IF NOT EXISTS financial_accounts (
  id            UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  nome          VARCHAR(100) NOT NULL,
  tipo          VARCHAR(20)  NOT NULL, -- 'caixa', 'banco', 'cartao'
  saldo_inicial DECIMAL(12,2) DEFAULT 0,
  criado_em     TIMESTAMP DEFAULT now()
);

-- 2. Tabela de Categorias de Despesa
CREATE TABLE IF NOT EXISTS expense_categories (
  id        UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  nome      VARCHAR(100) NOT NULL,
  criado_em TIMESTAMP DEFAULT now()
);

-- 3. Tabela de Receitas
CREATE TABLE IF NOT EXISTS revenues (
  id                    UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  tipo                  VARCHAR(20) NOT NULL,   -- 'locacao', 'venda_reciclaveis', 'outros'
  referencia_id         UUID,                    -- id da locação ou venda
  descricao             TEXT,
  valor                 DECIMAL(12,2) NOT NULL,
  data_recebimento      DATE      NOT NULL,
  conta_financeira_id   UUID      REFERENCES financial_accounts(id),
  status                VARCHAR(20) DEFAULT 'pendente', -- 'pendente','recebido','protestado'
  num_documento         VARCHAR(50),
  criado_em             TIMESTAMP DEFAULT now()
);

-- 4. Tabela de Despesas
CREATE TABLE IF NOT EXISTS expenses (
  id                    UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  categoria_id          UUID      REFERENCES expense_categories(id),
  fornecedor_id         UUID      REFERENCES suppliers(id),
  descricao             TEXT,
  valor                 DECIMAL(12,2) NOT NULL,
  data_servico          DATE      NOT NULL,
  data_vencimento       DATE,
  data_pagamento        DATE,
  forma_pagamento       VARCHAR(20),            -- 'dinheiro','pix','cartao','boleto'
  conta_financeira_id   UUID      REFERENCES financial_accounts(id),
  num_documento         VARCHAR(50),
  status                VARCHAR(20) DEFAULT 'pendente', -- 'pendente','pago'
  criado_em             TIMESTAMP DEFAULT now()
);

-- 5. Automatizar baixa de protestos
ALTER TABLE revenues
  ADD COLUMN IF NOT EXISTS num_protesto VARCHAR(50),
  ADD COLUMN IF NOT EXISTS data_protesto DATE,
  ADD COLUMN IF NOT EXISTS protestado_em TIMESTAMP;

-- 6. Exemplo de lançamento automático de despesa de manutenção
-- (quando uma manutenção for inserida em truck_maintenance ou machine_maintenance,
--  um trigger pode inserir na tabela expenses)



-- Tabela de Alertas
CREATE TABLE alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data_alerta DATE NOT NULL,
    tipo TEXT NOT NULL,
    destinatario TEXT NOT NULL,
    observacoes TEXT,
    status TEXT DEFAULT 'pendente',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Registra todas as compras de diesel (ou outro combustível) que chegam ao tanque central
CREATE TABLE fuel_tank_entries (
  id               UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  data_compra      DATE      NOT NULL,
  fornecedor_id    UUID      REFERENCES suppliers(id),
  litros_entregues DECIMAL(10,2) NOT NULL,
  preco_litro      DECIMAL(10,2) NOT NULL,
  valor_total      DECIMAL(12,2) GENERATED ALWAYS AS (litros_entregues * preco_litro) STORED,
  observacoes      TEXT,
  criado_em        TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Registra o ato de abastecer um veículo/máquina a partir do tanque
CREATE TABLE vehicle_refuels (
  id                UUID      PRIMARY KEY DEFAULT gen_random_uuid(),
  id_veiculo        UUID      NOT NULL,            -- pode ser truck_id ou patrimônio genérico
  id_funcionario    UUID      REFERENCES employees(id),
  tipo              VARCHAR(20) NOT NULL,         -- 'galpao', 'viagem', etc.
  data_abastecimento DATE     NOT NULL,
  km_anterior       INTEGER,                       -- kilometragem na última vez
  km_atual          INTEGER,                       -- kilometragem atual
  distancia_km      INTEGER GENERATED ALWAYS AS (km_atual - km_anterior) STORED,
  litros            DECIMAL(6,2) NOT NULL,
  consumo_medio     DECIMAL(6,2) GENERATED ALWAYS AS (
                       CASE 
                         WHEN (km_atual - km_anterior) > 0 
                         THEN (litros / (km_atual - km_anterior) * 100) 
                         ELSE NULL 
                       END
                     ) STORED,                    -- L/100 km
  preco_litro       DECIMAL(6,2),
  valor_total       DECIMAL(10,2) GENERATED ALWAYS AS (litros * preco_litro) STORED,
  fornecedor_id     UUID      REFERENCES suppliers(id),
  observacoes       TEXT,
  criado_em         TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE patrimonios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tipo_bem VARCHAR(50) NOT NULL, -- Ex: Caminhão, Esteira, Impressora...
    nome_identificacao VARCHAR(100) NOT NULL, -- Nome ou apelido interno do bem
    descricao TEXT,
    valor_pago DECIMAL(12,2),
    financiado BOOLEAN DEFAULT FALSE,
    valor_parcela DECIMAL(12,2),
    possui_seguro BOOLEAN DEFAULT FALSE,
    seguradora_id UUID REFERENCES suppliers(id),
    data_aquisicao DATE,
    status VARCHAR(20) DEFAULT 'ativo',
    observacoes TEXT,
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE truck_maintenance RENAME TO manutencoes;
ALTER TABLE manutencoes
RENAME COLUMN id_truck TO id_patrimonio;

ALTER TABLE manutencoes
ADD CONSTRAINT fk_patrimonio FOREIGN KEY (id_patrimonio) REFERENCES patrimonios(id) ON DELETE CASCADE;

-- Exemplo de entrada
INSERT INTO insurance_policies (
    tipo, id_relacionado, seguradora, cobertura, valor_total, valor_mensal,
    data_inicio, data_fim, status, observacoes
) VALUES (
    'patrimonio', 'UUID_DO_PATRIMONIO', 'Porto Seguro', 'Cobertura Total', 50000.00, 1200.00,
    '2024-01-01', '2025-01-01', 'ativo', 'Seguro de empilhadeira'
);

CREATE TABLE employee_payroll (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_employee UUID REFERENCES employees(id),
    competencia VARCHAR(7) NOT NULL, -- Ex: '04/2025'
    salario_base DECIMAL(10,2),
    horas_extras DECIMAL(5,2),
    adicional_periculosidade DECIMAL(10,2),
    adicional_insalubridade DECIMAL(10,2),
    descontos_inss DECIMAL(10,2),
    descontos_fgts DECIMAL(10,2),
    descontos_irrf DECIMAL(10,2),
    outros_descontos DECIMAL(10,2),
    beneficios DECIMAL(10,2),
    salario_liquido DECIMAL(10,2),
    data_pagamento DATE,
    status_pagamento VARCHAR(20) DEFAULT 'pendente',
    observacoes TEXT,
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ponto_digital (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  id_funcionario UUID NOT NULL REFERENCES employees(id),
  tipo VARCHAR(20) NOT NULL, -- entrada, saída, intervalo, etc.
  data_hora TIMESTAMP NOT NULL,
  latitude DECIMAL(9,6),
  longitude DECIMAL(9,6),
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE employee_ponto (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_funcionario UUID REFERENCES employees(id) NOT NULL,
    tipo_registro VARCHAR(20) CHECK (tipo_registro IN ('entrada', 'almoco_saida', 'almoco_volta', 'saida_final')) NOT NULL,
    data_registro DATE NOT NULL,
    hora_registro TIME NOT NULL,
    ip TEXT,
    localizacao TEXT,
    observacoes TEXT,
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);