-- SysZap Agenda de Contatos
-- Execute este arquivo no seu MySQL/MariaDB (DB: websindc_syszap)

CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario VARCHAR(80) NOT NULL UNIQUE,
    senha VARCHAR(255) NOT NULL,
    nome VARCHAR(120) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(120) NOT NULL,
    cor VARCHAR(15) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_cat_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contatos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(180) NOT NULL,
    telefone VARCHAR(30) NOT NULL,
    email VARCHAR(180) NULL,
    observacoes TEXT NULL,
    categoria_id INT NULL,
    ativo TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    CONSTRAINT fk_contatos_categoria FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS whatsapp_envios (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    contato_id INT NULL,
    number_to VARCHAR(40) NOT NULL,
    message TEXT NOT NULL,
    footer VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDENTE',
    http_code INT NULL,
    error_text VARCHAR(255) NULL,
    response_text MEDIUMTEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_envio_contato FOREIGN KEY (contato_id) REFERENCES contatos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin padrão em texto puro (login.php aceita texto puro ou hash)
INSERT INTO usuarios (usuario, senha, nome)
SELECT 'admin', 'admin123', 'Administrador'
WHERE NOT EXISTS (SELECT 1 FROM usuarios WHERE usuario='admin');