script-sql.sql

-- Tabela para armazenar informações dos Tenants
CREATE TABLE Clinic (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Tabela para armazenar usuários administrativos das clínicas
CREATE TABLE ClinicUser (
    id UUID PRIMARY KEY,
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    username VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'ADMIN', -- ADMIN, RECEPTIONIST, DOCTOR
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Patient (
    id UUID PRIMARY KEY,
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    full_name VARCHAR(255) NOT NULL,
    birth_date DATE NOT NULL,
    cpf VARCHAR(14) UNIQUE, -- CPF com máscara (ex: 123.456.789-00)
    phone VARCHAR(20),
    email VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Tabela para armazenar os templates de formulários de anamnese
CREATE TABLE AnamnesisForm (
    id UUID PRIMARY KEY,
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Tipo ENUM para os tipos de questão
CREATE TYPE QuestionType AS ENUM ('TEXT', 'BOOLEAN', 'NUMBER', 'DATE', 'MULTIPLE_CHOICE');

-- Tabela para armazenar as questões de cada formulário de anamnese
CREATE TABLE AnamnesisQuestion (
    id UUID PRIMARY KEY,
    anamnesis_form_id UUID NOT NULL REFERENCES AnamnesisForm(id),
    question_text TEXT NOT NULL,
    question_type QuestionType NOT NULL,
    -- options JSONB removido
    order_index INTEGER NOT NULL,
    is_required BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- NOVA TABELA para armazenar as opções de questões de múltipla escolha
CREATE TABLE AnamnesisQuestionOption (
    id UUID PRIMARY KEY,
    anamnesis_question_id UUID NOT NULL REFERENCES AnamnesisQuestion(id),
    option_text VARCHAR(255) NOT NULL,
    option_value TEXT, -- Pode ser um valor booleano, numérico, etc., armazenado como texto
    order_index INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (anamnesis_question_id, option_text) -- Garante que não haja opções duplicadas para a mesma questão
);

-- Tabela para registrar cada vez que um paciente inicia/completa um formulário
CREATE TABLE PatientAnamnesisSession (
    id UUID PRIMARY KEY,
    patient_id UUID NOT NULL REFERENCES Patient(id),
    anamnesis_form_id UUID NOT NULL REFERENCES AnamnesisForm(id),
    checkin_token UUID UNIQUE NOT NULL, -- Token para acesso do paciente
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING', -- Ex: PENDING, IN_PROGRESS, COMPLETED
    started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Tabela para armazenar as respostas de um paciente a um formulário
CREATE TABLE AnamnesisAnswer (
    id UUID PRIMARY KEY,
    patient_anamnesis_session_id UUID NOT NULL REFERENCES PatientAnamnesisSession(id),
    anamnesis_question_id UUID NOT NULL REFERENCES AnamnesisQuestion(id),
    answer_text TEXT, -- Resposta para TEXT, NUMBER, DATE
    answer_boolean BOOLEAN, -- Resposta para BOOLEAN
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    -- Uma resposta por questão por sessão (para tipos não-múltipla-escolha)
    UNIQUE (patient_anamnesis_session_id, anamnesis_question_id)
);

-- Tabela de junção: cada resposta de múltipla escolha pode ter várias opções selecionadas
CREATE TABLE AnamnesisAnswerOption (
    id UUID PRIMARY KEY,
    anamnesis_answer_id UUID NOT NULL REFERENCES AnamnesisAnswer(id) ON DELETE CASCADE,
    anamnesis_question_option_id UUID NOT NULL REFERENCES AnamnesisQuestionOption(id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (anamnesis_answer_id, anamnesis_question_option_id) -- Evita opção duplicada na mesma resposta
);

-- Tabela para armazenar os termos de consentimento e política de privacidade
CREATE TABLE ConsentTerm (
    id UUID PRIMARY KEY,
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    version INTEGER NOT NULL DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (clinic_id, title, version)
);

-- Tabela para registrar a assinatura digital do paciente
CREATE TABLE PatientSignature (
    id UUID PRIMARY KEY,
    patient_id UUID NOT NULL REFERENCES Patient(id),
    consent_term_id UUID NOT NULL REFERENCES ConsentTerm(id),
    signature_image_url TEXT NOT NULL,
    signed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (patient_id, consent_term_id)
);

-- Tabela para registrar os pagamentos
CREATE TABLE Payment (
    id UUID PRIMARY KEY,
    patient_id UUID NOT NULL REFERENCES Patient(id),
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    patient_anamnesis_session_id UUID REFERENCES PatientAnamnesisSession(id),
    service_description VARCHAR(255) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'BRL',
    payment_method VARCHAR(50), -- Ex: PIX, CREDIT_CARD
    transaction_id VARCHAR(255) UNIQUE, -- ID do gateway de pagamento
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING', -- Ex: PENDING, APPROVED, DECLINED, REFUNDED
    payment_token UUID UNIQUE NOT NULL, -- Token para acesso do paciente ao pagamento
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de médicos (pertence à clínica, pode ter múltiplos agendamentos)
CREATE TABLE Doctor (
    id UUID PRIMARY KEY,
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    clinic_user_id UUID REFERENCES ClinicUser(id), -- Opcional: vincula médico a um usuário do sistema
    full_name VARCHAR(255) NOT NULL,
    crm VARCHAR(20), -- Registro no Conselho Regional de Medicina
    specialty VARCHAR(100), -- Ex: Clínico Geral, Cardiologia, Dermatologia
    salary DECIMAL(12, 2), -- Salário (pode ser NULL para PJ/autônomos)
    phone VARCHAR(20),
    email VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (clinic_id, crm) -- CRM único por clínica
);

CREATE TYPE AppointmentStatus AS ENUM ('SCHEDULED', 'IN_PROGRESS', 'COMPLETE', 'PAID', 'CANCELED', 'LATE', 'LOSE');

CREATE TABLE Appointment (
    id UUID PRIMARY KEY,
    patient_id UUID NOT NULL REFERENCES Patient(id),
    clinic_id UUID NOT NULL REFERENCES Clinic(id),
    doctor_id UUID NOT NULL REFERENCES Doctor(id),
    payment_id UUID REFERENCES Payment(id),
    scheduled_by UUID REFERENCES ClinicUser(id), -- Usuário que realizou o agendamento
    description VARCHAR(255),
    status AppointmentStatus DEFAULT 'SCHEDULED',
    schedule_to TIMESTAMPTZ NOT NULL,
    scheduled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Índices para otimização de consultas
CREATE INDEX idx_clinic_email ON Clinic(email);
CREATE INDEX idx_clinic_user_clinic_id ON ClinicUser(clinic_id);
CREATE INDEX idx_clinic_user_email ON ClinicUser(email);
CREATE INDEX idx_patient_clinic_id ON Patient(clinic_id);
CREATE INDEX idx_patient_cpf ON Patient(cpf);
CREATE INDEX idx_anamnesis_form_clinic_id ON AnamnesisForm(clinic_id);
CREATE INDEX idx_anamnesis_question_form_id ON AnamnesisQuestion(anamnesis_form_id);
CREATE INDEX idx_anamnesis_question_option_question_id ON AnamnesisQuestionOption(anamnesis_question_id);
CREATE INDEX idx_patient_session_patient_id ON PatientAnamnesisSession(patient_id);
CREATE INDEX idx_patient_session_form_id ON PatientAnamnesisSession(anamnesis_form_id);
CREATE INDEX idx_anamnesis_answer_session_id ON AnamnesisAnswer(patient_anamnesis_session_id);
CREATE INDEX idx_anamnesis_answer_question_id ON AnamnesisAnswer(anamnesis_question_id);
CREATE INDEX idx_anamnesis_answer_option_answer_id ON AnamnesisAnswerOption(anamnesis_answer_id);
CREATE INDEX idx_anamnesis_answer_option_option_id ON AnamnesisAnswerOption(anamnesis_question_option_id);
CREATE INDEX idx_consent_term_clinic_id ON ConsentTerm(clinic_id);
CREATE INDEX idx_patient_signature_patient_id ON PatientSignature(patient_id);
CREATE INDEX idx_patient_signature_consent_term_id ON PatientSignature(consent_term_id);
CREATE INDEX idx_payment_patient_id ON Payment(patient_id);
CREATE INDEX idx_payment_clinic_id ON Payment(clinic_id);
CREATE INDEX idx_payment_patient_anamnesis_session_id ON Payment(patient_anamnesis_session_id);
CREATE INDEX idx_payment_transaction_id ON Payment(transaction_id);
CREATE INDEX idx_doctor_clinic_id ON Doctor(clinic_id);
CREATE INDEX idx_doctor_clinic_user_id ON Doctor(clinic_user_id);
CREATE INDEX idx_doctor_is_active ON Doctor(is_active);
CREATE INDEX idx_appointment_patient_id ON Appointment(patient_id);
CREATE INDEX idx_appointment_clinic_id ON Appointment(clinic_id);
CREATE INDEX idx_appointment_doctor_id ON Appointment(doctor_id);
CREATE INDEX idx_appointment_schedule_to ON Appointment(schedule_to);
CREATE INDEX idx_appointment_scheduled_by ON Appointment(scheduled_by);