-- Migration 002: Extend users table for BLOCO 001 - Cadastro de Usuário
-- Date: 2024-08-03
-- Description: Add fields for complete user registration

ALTER TABLE users ADD COLUMN full_name VARCHAR(150) AFTER username;
ALTER TABLE users ADD COLUMN cpf VARCHAR(14) UNIQUE AFTER full_name;
ALTER TABLE users ADD COLUMN rg VARCHAR(20) AFTER cpf;
ALTER TABLE users ADD COLUMN birth_date DATE AFTER rg;
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER birth_date;
ALTER TABLE users ADD COLUMN whatsapp VARCHAR(20) AFTER phone;
ALTER TABLE users ADD COLUMN address VARCHAR(255) AFTER whatsapp;
ALTER TABLE users ADD COLUMN municipality VARCHAR(100) AFTER address;
ALTER TABLE users ADD COLUMN state VARCHAR(2) AFTER municipality;
ALTER TABLE users ADD COLUMN job_title VARCHAR(100) AFTER state;
ALTER TABLE users ADD COLUMN organization VARCHAR(100) AFTER job_title;

-- Add status field if not exists (0=inactive, 1=active, 2=pending, 3=rejected)
-- Note: Already has status column from previous migrations, update comment only
ALTER TABLE users MODIFY COLUMN status INT NOT NULL DEFAULT 2 COMMENT '0=inactive, 1=active, 2=pending, 3=rejected';

-- Add created_by and approved_by for audit trail
ALTER TABLE users ADD COLUMN created_by INT AFTER updated_at;
ALTER TABLE users ADD COLUMN approved_by INT AFTER created_by;
ALTER TABLE users ADD COLUMN approved_at DATETIME AFTER approved_by;

-- Add rejection reason
ALTER TABLE users ADD COLUMN rejection_reason TEXT AFTER approved_at;

-- Create index for faster lookups
CREATE INDEX idx_users_cpf ON users(cpf);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_municipality ON users(municipality);
