CREATE EXTENSION IF NOT EXISTS pgcrypto;

DO $$ BEGIN
  CREATE TYPE user_role AS ENUM ('patient', 'admin');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  cpf VARCHAR(14) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role user_role DEFAULT 'patient',
  created_at TIMESTAMP DEFAULT NOW(),
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  icon VARCHAR(10),
  color VARCHAR(7)
);

CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  category_id INT REFERENCES categories(id),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  file_key VARCHAR(500) NOT NULL,
  file_type VARCHAR(50),
  file_size_bytes BIGINT,
  exam_date DATE,
  uploaded_by UUID REFERENCES users(id),
  uploaded_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE activity_logs (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  action VARCHAR(100),
  document_id UUID REFERENCES documents(id),
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO categories (name, icon, color) VALUES
  ('Exames de Sangue', '🩺', '#E3F2FD'),
  ('Cardiologia', '🫀', '#FFE4E6'),
  ('Ortopedia / Imagem', '🦴', '#FEF3C7'),
  ('Neurologia', '🧠', '#EDE9FE'),
  ('Oftalmologia', '👁️', '#DCFCE7'),
  ('Odontologia', '🦷', '#CCFBF1'),
  ('Laudos e Receitas', '💊', '#FCE7F3'),
  ('Documentos Gerais', '📋', '#E5E7EB')
ON CONFLICT DO NOTHING;