-- ═══════════════════════════════════════════════════════════════════════════
-- AutoGest — Schema completo do banco de dados
-- Banco: autohomo  |  Charset: utf8mb4
-- Gerado a partir de: painel-gerente.html + vitrine-cliente.html
-- ═══════════════════════════════════════════════════════════════════════════

CREATE DATABASE IF NOT EXISTS autohomo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE autohomo;

-- ═══════════════════════════════════════════════════════════════════════════
-- 1. VEICULOS
--    Estoque completo — alimenta painel-gerente e vitrine-cliente
-- ═══════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS veiculos (
  id            VARCHAR(64)    NOT NULL,
  marca         VARCHAR(100)   NOT NULL,
  modelo        VARCHAR(100)   NOT NULL,
  ano           SMALLINT       NOT NULL,
  preco         DECIMAL(12,2)  NOT NULL,
  custo         DECIMAL(12,2)  DEFAULT 0,
  km            INT            DEFAULT 0,
  combustivel   VARCHAR(50)    COMMENT 'Flex | Gasolina | Etanol | Diesel | Elétrico | Híbrido',
  cambio        VARCHAR(50)    COMMENT 'Manual | Automático | CVT | Automatizado',
  cor           VARCHAR(80),
  placa         VARCHAR(20),
  portas        TINYINT        DEFAULT 4,
  status        VARCHAR(30)    NOT NULL DEFAULT 'Disponível'
                               COMMENT 'Disponível | Vendido | Reservado',
  descricao     TEXT           COMMENT 'Versão, opcionais, destaques',
  observacoes   TEXT           COMMENT 'Notas internas (não exibidas na vitrine)',
  fotos         JSON           COMMENT 'Array de URLs das imagens',
  video         VARCHAR(500)   COMMENT 'URL YouTube / Instagram do vídeo',
  unidade       VARCHAR(100)   COMMENT 'Ex: Santos | Praia Grande (filtro da vitrine)',
  criado_em     BIGINT         COMMENT 'timestamp ms — Date.now()',
  modificado_por VARCHAR(150),
  modificado_em  BIGINT,
  vendido_por   VARCHAR(150),
  vendido_em    BIGINT,
  PRIMARY KEY (id),
  INDEX idx_status   (status),
  INDEX idx_marca    (marca),
  INDEX idx_preco    (preco),
  INDEX idx_criado   (criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- 2. FUNCIONARIOS
--    Gerentes e vendedores que acessam o painel
-- ═══════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS funcionarios (
  id            VARCHAR(64)    NOT NULL,
  nome          VARCHAR(150)   NOT NULL,
  codigo        VARCHAR(50)    UNIQUE COMMENT 'Ex: EMP-001',
  cargo         VARCHAR(30)    NOT NULL DEFAULT 'employee'
                               COMMENT 'manager | employee',
  email         VARCHAR(200)   UNIQUE,
  senha         VARCHAR(200)   NOT NULL,
  status        VARCHAR(20)    NOT NULL DEFAULT 'active'
                               COMMENT 'active | inactive',
  ultimo_acesso VARCHAR(50)    COMMENT 'ISO 8601 — ex: 2025-05-20T14:00:00.000Z',
  PRIMARY KEY (id),
  INDEX idx_email  (email),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- 3. ATENDIMENTOS  (carteira de negociações)
--    Funil de vendas: negociando → proposta → análise → aprovado → fechado
-- ═══════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS atendimentos (
  id                  VARCHAR(64)   NOT NULL,

  -- Responsável
  funcionario_id      VARCHAR(64),
  funcionario_nome    VARCHAR(150),
  funcionario_codigo  VARCHAR(50),

  -- Cliente
  cliente_nome        VARCHAR(150)  NOT NULL,
  cliente_telefone    VARCHAR(50),
  cliente_email       VARCHAR(200),
  cliente_cpf         VARCHAR(20),
  cliente_nascimento  VARCHAR(20)   COMMENT 'YYYY-MM-DD',
  cliente_cnh         VARCHAR(50),
  cliente_mae         VARCHAR(150),
  cliente_pai         VARCHAR(150),
  observacoes         TEXT,

  -- Veículo negociado
  veiculo_id          VARCHAR(64)   COMMENT 'FK → veiculos.id (pode ser NULL)',
  veiculo_interesse   VARCHAR(300)  COMMENT 'Texto livre quando não há vínculo com estoque',

  -- Financeiro
  valor_total         DECIMAL(12,2),
  valor_entrada       DECIMAL(12,2),
  parcelas            SMALLINT      DEFAULT 0,
  valor_parcela       DECIMAL(12,2),
  tipo_financiamento  VARCHAR(100)  COMMENT 'Ex: CDC | Leasing | À vista | Consórcio',
  banco               VARCHAR(150),

  -- Funil
  status              VARCHAR(30)   NOT NULL DEFAULT 'negotiating'
                                    COMMENT 'negotiating | proposal | analysis | approved | closed',

  -- Mídia
  fotos               JSON          COMMENT 'Array de URLs de documentos/fotos do cliente',

  -- Datas
  criado_em           BIGINT,
  atualizado_em       BIGINT,

  PRIMARY KEY (id),
  INDEX idx_status        (status),
  INDEX idx_func_id       (funcionario_id),
  INDEX idx_veiculo_id    (veiculo_id),
  INDEX idx_criado        (criado_em)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- 4. LOGS_AUDITORIA
--    Histórico de todas as ações do sistema (até 1000 registros ativos)
-- ═══════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS logs_auditoria (
  id              VARCHAR(64)   NOT NULL,
  ts              DATETIME(3)   NOT NULL        COMMENT 'Precisão em milissegundos',
  usuario_nome    VARCHAR(150),
  usuario_cargo   VARCHAR(30)                   COMMENT 'manager | employee',
  usuario_codigo  VARCHAR(50),
  acao            VARCHAR(50)   NOT NULL
                                COMMENT 'login | logout | create | update | delete | status |
                                         create-emp | update-emp | emp-status |
                                         config | license | wallet-create | wallet-update | wallet-delete',
  detalhe         TEXT,
  veiculo_nome    VARCHAR(200),
  veiculo_placa   VARCHAR(20),
  status_anterior VARCHAR(50)   COMMENT 'Status do veículo antes da alteração',
  PRIMARY KEY (id),
  INDEX idx_ts    (ts),
  INDEX idx_acao  (acao),
  INDEX idx_usuario (usuario_nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- 5. CONFIGURACOES
--    Dados da loja exibidos no painel e na vitrine (1 linha fixa)
-- ═══════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS configuracoes (
  id              INT           NOT NULL DEFAULT 1,
  nome_loja       VARCHAR(150)  DEFAULT 'Minha Loja',
  whatsapp        VARCHAR(30)   COMMENT 'Apenas dígitos (DDD + número)',
  cidade          VARCHAR(100),
  descricao       TEXT          COMMENT 'Slogan / descrição exibido na vitrine',
  email           VARCHAR(200),

  -- Unidade I
  u1_nome         VARCHAR(100)  DEFAULT 'Unidade I',
  u1_tag          VARCHAR(50)   DEFAULT 'Santos',
  u1_endereco     TEXT          COMMENT 'Pode conter \n para quebra de linha',
  u1_horario_sem  VARCHAR(60)   COMMENT 'Seg–Sáb',
  u1_horario_dom  VARCHAR(60)   COMMENT 'Dom e feriados',

  -- Unidade II
  u2_nome         VARCHAR(100)  DEFAULT 'Unidade II',
  u2_tag          VARCHAR(50)   DEFAULT 'Praia Grande',
  u2_endereco     TEXT,
  u2_horario_sem  VARCHAR(60),
  u2_horario_dom  VARCHAR(60),

  -- Administrativo
  horario_admin   VARCHAR(60)   COMMENT 'Seg–Sex',

  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ═══════════════════════════════════════════════════════════════════════════
-- 6. LICENCA
--    Controle de plano/validade do SaaS (1 linha fixa)
-- ═══════════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS licenca (
  id          INT          NOT NULL DEFAULT 1,
  nome        VARCHAR(150),
  email       VARCHAR(200),
  chave       VARCHAR(50)  COMMENT 'Ex: DEMO-PRO0-0000-TEST',
  plano       VARCHAR(50)  COMMENT 'Starter | Pro | Vitalício',
  dias        INT          COMMENT '99999 = vitalício',
  ativada_em  VARCHAR(50)  COMMENT 'ISO 8601',
  expira_em   VARCHAR(50)  COMMENT 'ISO 8601 | NULL = sem expiração',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════════
-- DADOS INICIAIS
-- ═══════════════════════════════════════════════════════════════════════════

-- Configuração padrão da loja
INSERT INTO configuracoes
  (id, nome_loja, whatsapp, cidade, descricao, email,
   u1_nome, u1_tag, u1_endereco, u1_horario_sem, u1_horario_dom,
   u2_nome, u2_tag, u2_endereco, u2_horario_sem, u2_horario_dom,
   horario_admin)
VALUES
  (1, 'AutoGest', '', 'Santos, SP',
   'Especializada em seminovos de qualidade com garantia e procedência comprovada.',
   '',
   'Unidade I',    'Santos',      'Av. Washington Luis, 238\nSantos / SP',      '9h às 19h', '9h às 16h',
   'Unidade II',   'Praia Grande','Av. Pres. Kennedy, 3113\nPraia Grande / SP', '9h às 20h', '9h às 16h',
   '9h às 18h')
ON DUPLICATE KEY UPDATE id = id;

-- Funcionários demo
INSERT INTO funcionarios (id, nome, codigo, cargo, email, senha, status, ultimo_acesso)
VALUES
  ('e1', 'Maria Souza', 'EMP-001', 'employee', 'maria@loja.com',  '123456',   'active', NULL),
  ('e2', 'Carlos Lima',  'EMP-002', 'manager',  'carlos@loja.com', 'senha123', 'active', NULL)
ON DUPLICATE KEY UPDATE id = id;

-- Veículos demo (14 veículos — os mesmos do seed JavaScript)
INSERT INTO veiculos
  (id, marca, modelo, ano, preco, custo, km, combustivel, cambio, cor, placa, portas,
   status, descricao, fotos, video, criado_em, modificado_por, modificado_em, vendido_por, vendido_em)
VALUES
  ('v1',  'Toyota',     'Corolla',  2022, 142000, 118000, 28000, 'Flex', 'Automático', 'Prata',           'ABC-1234', 4, 'Disponível', 'Multimídia 9", câmera de ré, bancos de couro',        '[]', '', UNIX_TIMESTAMP(NOW() -  INTERVAL 5  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() -  INTERVAL 5  DAY) * 1000, NULL, NULL),
  ('v2',  'Honda',      'Civic',    2023, 165000, 138000, 12000, 'Flex', 'CVT',        'Preto',           'DEF-5678', 4, 'Disponível', 'Teto solar, piloto automático adaptativo',            '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 10  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() - INTERVAL 10  DAY) * 1000, NULL, NULL),
  ('v3',  'Jeep',       'Renegade', 2021,  98000,  79000, 47000, 'Flex', 'Automático', 'Branco',          'GHI-9012', 4, 'Vendido',    '4x4, controle de descida',                            '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 20  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() - INTERVAL 20  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() - INTERVAL 20  DAY) * 1000),
  ('v4',  'Volkswagen', 'T-Cross',  2023, 128000, 105000,  8000, 'Flex', 'Automático', 'Azul',            'JKL-3456', 4, 'Disponível', 'Composition Color, sensor de estacionamento',         '[]', '', UNIX_TIMESTAMP(NOW() -  INTERVAL 3  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() -  INTERVAL 3  DAY) * 1000, NULL, NULL),
  ('v5',  'Chevrolet',  'Onix',     2022,  72000,  61000, 35000, 'Flex', 'Manual',     'Vermelho',        'MNO-7890', 4, 'Reservado',  'MyLink, ar digital',                                  '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 15  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() - INTERVAL 15  DAY) * 1000, NULL, NULL),
  ('v6',  'Fiat',       'Pulse',    2023,  88000,  74000, 15000, 'Flex', 'CVT',        'Cinza',           'PQR-1234', 4, 'Vendido',    'Turbo 200, teto solar panorâmico',                    '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 35  DAY) * 1000, 'Carlos Lima', UNIX_TIMESTAMP(NOW() - INTERVAL 35  DAY) * 1000, 'Carlos Lima', UNIX_TIMESTAMP(NOW() - INTERVAL 35  DAY) * 1000),
  ('v7',  'Honda',      'HR-V',     2022, 135000, 112000, 22000, 'Flex', 'CVT',        'Branco Perolado', 'STU-2345', 4, 'Vendido',    'Sensing, câmera 360°',                                '[]', '', UNIX_TIMESTAMP(NOW() -  INTERVAL 8  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() -  INTERVAL 8  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() -  INTERVAL 8  DAY) * 1000),
  ('v8',  'Toyota',     'Yaris',    2023,  92000,  78000,  5000, 'Flex', 'Automático', 'Cinza Grafite',   'VWX-6789', 4, 'Vendido',    'Design 1.5, air bags laterais',                       '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 12  DAY) * 1000, 'Carlos Lima', UNIX_TIMESTAMP(NOW() - INTERVAL 12  DAY) * 1000, 'Carlos Lima', UNIX_TIMESTAMP(NOW() - INTERVAL 12  DAY) * 1000),
  ('v9',  'Fiat',       'Argo',     2022,  68000,  56000, 38000, 'Flex', 'Manual',     'Vermelho',        'YZA-0123', 4, 'Vendido',    'Drive, ar-condicionado, central multimídia',          '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 25  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() - INTERVAL 25  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() - INTERVAL 25  DAY) * 1000),
  ('v10', 'Chevrolet',  'Cruze',    2021, 115000,  95000, 45000, 'Flex', 'Automático', 'Preto',           'BCD-4567', 4, 'Vendido',    'Sport LTZ, Mylink 8"',                                '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 18  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() - INTERVAL 18  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() - INTERVAL 18  DAY) * 1000),
  ('v11', 'Hyundai',    'HB20',     2023,  76000,  64000,  9000, 'Flex', 'Manual',     'Prata',           'EFG-8901', 4, 'Vendido',    'Vision Plus, bluetooth, câmera de ré',                '[]', '', UNIX_TIMESTAMP(NOW() -  INTERVAL 4  DAY) * 1000, 'Carlos Lima', UNIX_TIMESTAMP(NOW() -  INTERVAL 4  DAY) * 1000, 'Carlos Lima', UNIX_TIMESTAMP(NOW() -  INTERVAL 4  DAY) * 1000),
  ('v12', 'Volkswagen', 'Polo',     2022,  89000,  74000, 18000, 'Flex', 'Automático', 'Branco',          'HIJ-2345', 4, 'Disponível', 'Highline, teto solar, sensor de chuva',               '[]', '', UNIX_TIMESTAMP(NOW() -  INTERVAL 2  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() -  INTERVAL 2  DAY) * 1000, NULL, NULL),
  ('v13', 'Renault',    'Kwid',     2023,  58000,  48000,  6000, 'Flex', 'Manual',     'Laranja',         'KLM-3456', 4, 'Vendido',    'Intense, central multimídia 8"',                      '[]', '', UNIX_TIMESTAMP(NOW() -  INTERVAL 6  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() -  INTERVAL 6  DAY) * 1000, 'Maria Souza', UNIX_TIMESTAMP(NOW() -  INTERVAL 6  DAY) * 1000),
  ('v14', 'Nissan',     'Kicks',    2022, 118000,  99000, 31000, 'Flex', 'CVT',        'Azul',            'NOP-7890', 4, 'Vendido',    'Advance, ProPilot, 360°',                             '[]', '', UNIX_TIMESTAMP(NOW() - INTERVAL 14  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() - INTERVAL 14  DAY) * 1000, 'Gerente',     UNIX_TIMESTAMP(NOW() - INTERVAL 14  DAY) * 1000)
ON DUPLICATE KEY UPDATE id = id;


-- ═══════════════════════════════════════════════════════════════════════════
-- CONSULTAS ÚTEIS
-- ═══════════════════════════════════════════════════════════════════════════

-- Estoque disponível (vitrine-cliente)
-- SELECT id, marca, modelo, ano, preco, km, combustivel, cambio, cor, placa, status, descricao, fotos, video, unidade
--   FROM veiculos WHERE status = 'Disponível' ORDER BY criado_em DESC;

-- Dashboard: contagens por status
-- SELECT status, COUNT(*) AS total FROM veiculos GROUP BY status;

-- Vendas do mês com lucro bruto
-- SELECT marca, modelo, ano, placa, preco, custo, (preco - custo) AS lucro, vendido_por, FROM_UNIXTIME(vendido_em/1000) AS data_venda
--   FROM veiculos WHERE status = 'Vendido'
--     AND vendido_em >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-01')) * 1000;

-- Top vendedores (período livre)
-- SELECT vendido_por AS vendedor, COUNT(*) AS vendas, SUM(preco) AS receita, SUM(preco - custo) AS lucro
--   FROM veiculos WHERE status = 'Vendido' GROUP BY vendido_por ORDER BY vendas DESC;

-- Funil de negociações ativo
-- SELECT status, COUNT(*) AS total, SUM(valor_total) AS volume
--   FROM atendimentos GROUP BY status ORDER BY FIELD(status,'negotiating','proposal','analysis','approved','closed');

-- Últimos logs de auditoria
-- SELECT ts, usuario_nome, usuario_cargo, acao, detalhe, veiculo_nome FROM logs_auditoria ORDER BY ts DESC LIMIT 50;