-- Schema base para AppGestor
-- MySQL 8+/MariaDB, collation utf8mb4_unicode_ci

CREATE DATABASE IF NOT EXISTS gestor CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gestor;

DROP TABLE IF EXISTS acessos_recentes;
DROP TABLE IF EXISTS timeline_loteamentos;
DROP TABLE IF EXISTS fila_operacional;
DROP TABLE IF EXISTS tarefas;
DROP TABLE IF EXISTS revisoes;
DROP TABLE IF EXISTS prazos;
DROP TABLE IF EXISTS auditorias;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS document_templates;
DROP TABLE IF EXISTS confrontantes;
DROP TABLE IF EXISTS documentos;
DROP TABLE IF EXISTS onus;
DROP TABLE IF EXISTS user_loteamentos;
DROP TABLE IF EXISTS serventias;
DROP TABLE IF EXISTS tributo_pagamentos;
DROP TABLE IF EXISTS tributo_anexos;
DROP TABLE IF EXISTS tributos;
DROP TABLE IF EXISTS ocr_metrics;
DROP TABLE IF EXISTS imoveis;
DROP TABLE IF EXISTS loteamentos;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS users;

CREATE TABLE companies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    cnpj VARCHAR(20) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    razao_social VARCHAR(255) NULL,
    nome_fantasia VARCHAR(255) NULL,
    endereco VARCHAR(255) NULL,
    email VARCHAR(255) NULL,
    telefone VARCHAR(40) NULL,
    responsavel_nome VARCHAR(255) NULL,
    responsavel_email VARCHAR(255) NULL,
    responsavel_telefone VARCHAR(40) NULL,
    cep VARCHAR(10) NULL,
    logradouro VARCHAR(255) NULL,
    numero VARCHAR(40) NULL,
    complemento VARCHAR(255) NULL,
    bairro VARCHAR(120) NULL,
    cidade VARCHAR(120) NULL,
    estado CHAR(2) NULL,
    brand_name VARCHAR(255) NULL,
    brand_theme VARCHAR(40) NULL,
    brand_logo VARCHAR(255) NULL,
    operation_mode ENUM('loteamentos','construtora','hibrido') NOT NULL DEFAULT 'loteamentos',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('user','gestor','administrador','master') NOT NULL DEFAULT 'user',
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loteamentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    nome VARCHAR(255) NOT NULL,
    cidade VARCHAR(120) NULL,
    status ENUM('ativo','inativo','em_validacao') NOT NULL DEFAULT 'ativo',
    poligonal_json JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_loteamentos_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE imoveis (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    loteamento_id INT UNSIGNED NULL,
    matricula VARCHAR(64) NOT NULL,
    descricao TEXT NULL,
    quadra VARCHAR(80) NULL,
    lote VARCHAR(80) NULL,
    cep VARCHAR(10) NULL,
    estado CHAR(2) NULL,
    cidade VARCHAR(120) NULL,
    bairro VARCHAR(120) NULL,
    endereco VARCHAR(255) NULL,
    numero VARCHAR(40) NULL,
    complemento VARCHAR(255) NULL,
    observacao TEXT NULL,
    tipo ENUM('residencial','comercial','rural','industrial','outro') DEFAULT 'outro',
    tipo_unidade ENUM('apartamento','studio','cobertura','casa','sala','loja','galpao','outro') DEFAULT 'outro',
    status ENUM('ativo','inativo','em_analise') DEFAULT 'em_analise',
    status_comercial ENUM('disponivel','reservado','vendido','bloqueado','escriturado','entregue','distratado') DEFAULT 'disponivel',
    andar VARCHAR(30) NULL,
    dormitorios TINYINT UNSIGNED NULL,
    suites TINYINT UNSIGNED NULL,
    banheiros TINYINT UNSIGNED NULL,
    vagas TINYINT UNSIGNED NULL,
    area_m2 DECIMAL(12,2) NULL,
    area_privativa_m2 DECIMAL(12,2) NULL,
    area_total_m2 DECIMAL(12,2) NULL,
    ultima_atualizacao DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_imoveis_matricula UNIQUE (company_id, matricula),
    CONSTRAINT fk_imoveis_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_imoveis_loteamento FOREIGN KEY (loteamento_id) REFERENCES loteamentos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE documentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    imovel_id INT UNSIGNED NULL,
    titulo VARCHAR(255) NOT NULL,
    status ENUM('em_processamento','pendente_validacao','aprovado','reprovado') DEFAULT 'em_processamento',
    hash_integridade VARCHAR(128) NULL,
    versao INT UNSIGNED NOT NULL DEFAULT 1,
    caminho VARCHAR(255) NULL,
    processed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_documentos_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_documentos_imovel FOREIGN KEY (imovel_id) REFERENCES imoveis(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(60) NOT NULL,
    description VARCHAR(255) NULL,
    subject VARCHAR(255) NULL,
    body MEDIUMTEXT NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_templates_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
    INDEX idx_templates_company (company_id),
    INDEX idx_templates_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE confrontantes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    imovel_id INT UNSIGNED NOT NULL,
    nome VARCHAR(255) NOT NULL,
    documento VARCHAR(30) NULL,
    contato VARCHAR(120) NULL,
    lado ENUM('norte','sul','leste','oeste','frente','fundos','outro') DEFAULT 'outro',
    status ENUM('pendente','validado','inconsistente') DEFAULT 'pendente',
    observacoes TEXT NULL,
    confirmado_por INT UNSIGNED NULL,
    confirmado_em DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_confrontantes_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_confrontantes_imovel FOREIGN KEY (imovel_id) REFERENCES imoveis(id) ON DELETE CASCADE,
    CONSTRAINT fk_confrontantes_user FOREIGN KEY (confirmado_por) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_confrontantes_status (status),
    INDEX idx_confrontantes_imovel (imovel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE onus (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    imovel_id INT UNSIGNED NULL,
    tipo VARCHAR(120) NOT NULL,
    descricao TEXT NULL,
    status ENUM('ativo','baixado','em_analise') DEFAULT 'em_analise',
    origem VARCHAR(120) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_onus_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_onus_imovel FOREIGN KEY (imovel_id) REFERENCES imoveis(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_loteamentos (
    user_id INT UNSIGNED NOT NULL,
    loteamento_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, loteamento_id),
    CONSTRAINT fk_ul_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ul_loteamento FOREIGN KEY (loteamento_id) REFERENCES loteamentos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE serventias (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    nome VARCHAR(255) NOT NULL,
    cidade VARCHAR(120) NOT NULL,
    uf CHAR(2) NOT NULL,
    contato VARCHAR(255) NULL,
    numero_registro VARCHAR(120) NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_serventias_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_serventias_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tributos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    imovel_id INT UNSIGNED NOT NULL,
    tipo VARCHAR(80) NOT NULL,
    competencia_inicio DATE NULL,
    competencia_fim DATE NULL,
    vencimento DATE NOT NULL,
    valor DECIMAL(12,2) NOT NULL,
    status ENUM('a_vencer','pago','vencido','suspenso','parcial') DEFAULT 'a_vencer',
    forma_pagamento VARCHAR(40) NULL,
    centro_custo VARCHAR(120) NULL,
    observacoes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT fk_tributos_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_tributos_imovel FOREIGN KEY (imovel_id) REFERENCES imoveis(id) ON DELETE CASCADE,
    INDEX idx_tributos_imovel (imovel_id),
    INDEX idx_tributos_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tributo_pagamentos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tributo_id INT UNSIGNED NOT NULL,
    data_pagamento DATE NOT NULL,
    valor_pago DECIMAL(12,2) NOT NULL,
    juros DECIMAL(12,2) NOT NULL DEFAULT 0,
    multa DECIMAL(12,2) NOT NULL DEFAULT 0,
    desconto DECIMAL(12,2) NOT NULL DEFAULT 0,
    forma_pagamento VARCHAR(40) NOT NULL,
    comprovante_path VARCHAR(255) NULL,
    observacao TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tp_tributo FOREIGN KEY (tributo_id) REFERENCES tributos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tributo_anexos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tributo_id INT UNSIGNED NOT NULL,
    tipo VARCHAR(40) NOT NULL,
    caminho VARCHAR(255) NOT NULL,
    nome_original VARCHAR(255) NOT NULL,
    tamanho INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ta_tributo FOREIGN KEY (tributo_id) REFERENCES tributos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE password_resets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    token VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE auditorias (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    entity VARCHAR(120) NOT NULL,
    action VARCHAR(120) NOT NULL,
    ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    before_state JSON NULL,
    after_state JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_auditorias_entity (entity),
    INDEX idx_auditorias_user (user_id),
    CONSTRAINT fk_auditorias_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tarefas (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    responsavel_id INT UNSIGNED NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    descricao TEXT NULL,
    status ENUM('pendente','em_andamento','concluida','cancelada') DEFAULT 'pendente',
    sensivel TINYINT(1) NOT NULL DEFAULT 0,
    data_limite DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tarefas_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_tarefas_user FOREIGN KEY (responsavel_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE revisoes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    usuario_id INT UNSIGNED NOT NULL,
    documento_id INT UNSIGNED NULL,
    status ENUM('em_andamento','aprovado','reprovado') DEFAULT 'em_andamento',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_revisoes_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_revisoes_user FOREIGN KEY (usuario_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_revisoes_doc FOREIGN KEY (documento_id) REFERENCES documentos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE prazos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT UNSIGNED NOT NULL,
    titulo VARCHAR(255) NOT NULL,
    data_limite DATE NOT NULL,
    status ENUM('aberto','concluido','vencido') DEFAULT 'aberto',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_prazos_user FOREIGN KEY (usuario_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fila_operacional (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    loteamento_id INT UNSIGNED NOT NULL,
    tipo VARCHAR(80) NOT NULL,
    total INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_fila_loteamento FOREIGN KEY (loteamento_id) REFERENCES loteamentos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE timeline_loteamentos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    loteamento_id INT UNSIGNED NOT NULL,
    evento VARCHAR(255) NOT NULL,
    descricao TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_timeline_loteamento FOREIGN KEY (loteamento_id) REFERENCES loteamentos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE acessos_recentes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT UNSIGNED NOT NULL,
    entidade VARCHAR(120) NOT NULL,
    referencia VARCHAR(255) NOT NULL,
    atualizado_em DATETIME NOT NULL,
    CONSTRAINT fk_acessos_user FOREIGN KEY (usuario_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_acessos_user (usuario_id, atualizado_em DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ocr_metrics (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    processed_today INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATE NOT NULL,
    CONSTRAINT fk_ocr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;