CREATE TABLE tb_usuarios (
    id INT(10) AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    senha_hash VARCHAR(255) NOT NULL, -- Armazena o HASH da senha
    nome_completo VARCHAR(100),
    is_admin BOOLEAN NOT NULL DEFAULT TRUE,
    deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')), -- Campo padrão de exclusão lógica
    criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (id)
);

CREATE TABLE tb_municipios (
	id INT(10) AUTO_INCREMENT,
    cod_ibge VARCHAR(7),
    nome VARCHAR(100) NOT NULL,
    uf CHAR(2) NOT NULL,
	deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
	PRIMARY KEY (id)
);

CREATE TABLE tb_planos (
    id INT(10) AUTO_INCREMENT,
    nome VARCHAR(40) NOT NULL,
    descricao VARCHAR(80),
    status CHAR(1) NOT NULL DEFAULT '1' CHECK (status IN ('0', '1')),
    valor DECIMAL(10, 2) NOT NULL,
    recorrencia CHAR(1) NOT NULL CHECK (recorrencia IN ('M', 'S', 'T', 'A')),
    deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
	 PRIMARY KEY (id)
);

CREATE TABLE tb_modelos (
    id INT(10) AUTO_INCREMENT,
    nome VARCHAR(80) NOT NULL UNIQUE,
    provedor VARCHAR(60),
    api_key_hash VARCHAR(255),
    status CHAR(1) NOT NULL DEFAULT '1' CHECK (status IN ('0', '1')),
    deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
	 PRIMARY KEY (id)
);


CREATE TABLE tb_cliente (
    id INT(10) AUTO_INCREMENT,
    tipo CHAR(1) NOT NULL CHECK (tipo IN ('F', 'J')),
    inscricao VARCHAR(14) NOT NULL UNIQUE,
    insc_estadual VARCHAR(20),
    insc_municip VARCHAR(20),
    razao_social VARCHAR(80) NOT NULL,
    nome_fantasia VARCHAR(40),
    cep CHAR(8),
    uf CHAR(2),
    municipio VARCHAR(100),
    cod_mun VARCHAR(7),
    logradouro VARCHAR(80),
    numero VARCHAR(10),
    complemento VARCHAR(100),
    bairro VARCHAR(40),
    api_client_id VARCHAR(20) NOT NULL UNIQUE,
    api_secret_key_hash VARCHAR(255) NOT NULL,
    id_tb_planos INT(10),
    deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
	PRIMARY KEY (id)

   
);

CREATE TABLE tb_rotinas (
    id INT(10) AUTO_INCREMENT,
    nome_rotina VARCHAR(50) NOT NULL UNIQUE,
    dscricao_rotina VARCHAR(150) NOT NULL,
	deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
	 PRIMARY KEY (id)
);

CREATE TABLE tb_configuracoes (
	id INT(10) AUTO_INCREMENT,
    id_plano INT(10) NOT NULL REFERENCES tb_planos(id),
    id_rotina INT(10) NOT NULL REFERENCES tb_rotinas(id),
    id_modelo INT(10) NOT NULL REFERENCES tb_modelos(id),
	deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
	UNIQUE (id_plano, id_rotina),
	 PRIMARY KEY (id)
);

CREATE TABLE tb_configuracoes_clientes (
    id INT(10) AUTO_INCREMENT,
    id_cliente INT(10) NOT NULL REFERENCES tb_cliente(id),
    id_rotina INT(10) NOT NULL REFERENCES tb_rotinas(id),
    id_modelo INT(10) NOT NULL REFERENCES tb_modelos(id),
	deleted CHAR(1) NOT NULL DEFAULT '' CHECK (deleted IN ('', '*')),
    UNIQUE (id_cliente, id_rotina),
	 PRIMARY KEY (id)
);