-- Migration: Add password history and expiration fields
-- Version: V24
-- Description: Creates password_history table and adds password expiration fields to users table

-- Create password_history table
CREATE TABLE password_history
(
    id            UUID                     DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id       UUID         NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_password_history_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

-- Create indexes for better query performance
CREATE INDEX idx_password_history_user_id ON password_history (user_id);
CREATE INDEX idx_password_history_created_at ON password_history (created_at);

-- Add password expiration fields to users table
ALTER TABLE users
ADD COLUMN IF NOT EXISTS password_changed_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS password_expires_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS password_expiration_days INTEGER DEFAULT 90,
ADD COLUMN IF NOT EXISTS password_history_count INTEGER DEFAULT 5;

-- Add comment to table
COMMENT ON TABLE password_history IS 'Stores password history to prevent password reuse. Tracks previous password hashes for each user.';
COMMENT ON COLUMN password_history.password_hash IS 'Hashed password value (BCrypt hash)';
COMMENT ON COLUMN password_history.user_id IS 'Reference to the user who owns this password history entry';
COMMENT ON COLUMN password_history.created_at IS 'When this password was set';

-- Add comments to users table columns
COMMENT ON COLUMN users.password_changed_at IS 'When the password was last changed';
COMMENT ON COLUMN users.password_expires_at IS 'When the password expires (calculated from password_changed_at + password_expiration_days)';
COMMENT ON COLUMN users.password_expiration_days IS 'Number of days until password expires (default: 90 days)';
COMMENT ON COLUMN users.password_history_count IS 'Number of previous passwords to keep in history (default: 5)';

