-- Garante que o script seja executado no banco de dados correto
\c auditto_db;

-- Cria a tabela para armazenar as sessões do FGTS Digital
CREATE TABLE IF NOT EXISTS companies (
    id SERIAL PRIMARY KEY,
    cnpj VARCHAR(14) NOT NULL UNIQUE,
    name VARCHAR(255),
    -- Armazena o cookie completo de forma segura (criptografado)
    cookie TEXT NOT NULL,
    -- Data e hora de expiração do token, com fuso horário
    expires_on TIMESTAMPTZ NOT NULL,
    -- Status da sessão: 'ativo', 'inativo', 'com_erro'
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    -- Data e hora da última renovação bem-sucedida
    updated_at TIMESTAMPTZ,
    -- Data e hora de criação do registro
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Campo para logs de erro ou observações
    details TEXT
);

-- Cria índices para otimizar as consultas do worker e da api
CREATE INDEX IF NOT EXISTS idx_companies_expires_on ON companies (expires_on);
CREATE INDEX IF NOT EXISTS idx_companies_status ON companies (status);

-- Adiciona comentários às colunas para melhor entendimento
COMMENT ON COLUMN companies.cookie IS 'String completa de cookies, criptografada usando Fernet.';
COMMENT ON COLUMN companies.expires_on IS 'Timestamp de expiração extraído do JWT (fgts_token), armazenado em UTC.';

-- Cria a tabela para armazenar as sessões dos clientes em procuração
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    -- Referência à contabilidade procuradora
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,     
    -- CNPJ do cliente
    cnpj VARCHAR(14) NOT NULL,
    -- Nome da empresa cliente
    name VARCHAR(255),
    -- Armazena o cookie completo de forma segura (criptografado)
    cookie TEXT NOT NULL,
    -- Data e hora de expiração do token, com fuso horário
    expires_on TIMESTAMPTZ NOT NULL,
    -- Status da sessão: 'ativo', 'inativo', 'com_erro'
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    -- Data e hora da última renovação bem-sucedida
    updated_at TIMESTAMPTZ,
    -- Data e hora de criação do registro
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Campo para logs de erro ou observações
    details TEXT,
    -- Garante que cada cliente tenha apenas uma sessão por contabilidade
    UNIQUE(company_id, cnpj)
);

-- Cria índices para otimizar as consultas dos clientes
CREATE INDEX IF NOT EXISTS idx_customers_expires_on ON customers (expires_on);
CREATE INDEX IF NOT EXISTS idx_customers_status ON customers (status);
CREATE INDEX IF NOT EXISTS idx_customers_company_id ON customers (company_id);
CREATE INDEX IF NOT EXISTS idx_customers_cnpj ON customers (cnpj);

-- Adiciona comentários às colunas da nova tabela
COMMENT ON COLUMN customers.company_id IS 'ID da contabilidade procuradora que possui a procuração deste cliente.';
COMMENT ON COLUMN customers.cookie IS 'String completa de cookies do cliente, criptografada usando Fernet.';
COMMENT ON COLUMN customers.expires_on IS 'Timestamp de expiração extraído do JWT do cliente, armazenado em UTC.';