-- =============================================================================
-- BeKind Backend — Consolidated Migrations (004-010)
-- Combines: oauth-only flag, notification dedupe cleanup, avatar migration,
--           performance indexes, and password history
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- OAuth-Only Flag (from 004)
-- Add is_oauth_only flag to user_auth table for OAuth-only accounts
-- -----------------------------------------------------------------------------
ALTER TABLE user_auth ADD COLUMN IF NOT EXISTS is_oauth_only BOOLEAN DEFAULT FALSE;

COMMENT ON COLUMN user_auth.is_oauth_only IS 'Flag to identify OAuth-only accounts that cannot login via password (e.g., Zalo Mini App users)';

CREATE INDEX IF NOT EXISTS idx_user_auth_is_oauth_only ON user_auth(is_oauth_only) WHERE is_oauth_only = TRUE;

-- -----------------------------------------------------------------------------
-- Notification Dedupe Key Cleanup (from 005)
-- Depends on: 003-notification-schema.sql
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION cleanup_old_dedupe_keys()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM notification_messages
    WHERE dedupe_key IS NOT NULL
      AND created_at < CURRENT_TIMESTAMP - INTERVAL '7 days'
      AND id NOT IN (
          SELECT DISTINCT message_id 
          FROM notification_receipts 
          WHERE deleted_at IS NULL
      );
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION cleanup_old_dedupe_keys IS 
'Cleans up notification messages with dedupe_key older than 7 days that are not referenced by any active receipts. 
Should be run periodically via scheduled job (e.g., daily).';

-- -----------------------------------------------------------------------------
-- Avatar ID Migration (from 006)
-- Converts TEXT avatar URL to UUID FK referencing files(id)
-- -----------------------------------------------------------------------------
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS avatar_id UUID REFERENCES files(id) ON DELETE SET NULL;

ALTER TABLE users
    DROP COLUMN IF EXISTS avatar_url;

-- -----------------------------------------------------------------------------
-- Performance Indexes (from 008)
-- Adds missing indexes for frequently queried columns
-- -----------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_user_auth_twofa_enabled ON user_auth(twofa_enabled);
CREATE INDEX IF NOT EXISTS idx_user_auth_locked_until ON user_auth(locked_until);
CREATE INDEX IF NOT EXISTS idx_user_auth_login_attempts ON user_auth(login_attempts);
CREATE INDEX IF NOT EXISTS idx_files_status ON files(status);
CREATE INDEX IF NOT EXISTS idx_files_visibility ON files(visibility);
CREATE INDEX IF NOT EXISTS idx_files_category ON files(category);
CREATE INDEX IF NOT EXISTS idx_files_owner_user_id ON files(owner_user_id);
CREATE INDEX IF NOT EXISTS idx_notification_receipts_channel ON notification_receipts(channel);
CREATE INDEX IF NOT EXISTS idx_notification_messages_type ON notification_messages(type);
CREATE INDEX IF NOT EXISTS idx_users_avatar_id ON users(avatar_id);

-- -----------------------------------------------------------------------------
-- Password History (from 010)
-- Depends on: 001-auth-schema.sql (users)
-- Track password changes to prevent reuse and enforce password policies
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_password_history (
    id               UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    password_hash    VARCHAR(255) NOT NULL,
    changed_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    changed_by       UUID REFERENCES users(id),
    reason           TEXT
);

CREATE INDEX IF NOT EXISTS idx_user_password_history_user_id    ON user_password_history(user_id);
CREATE INDEX IF NOT EXISTS idx_user_password_history_changed_at ON user_password_history(changed_at DESC);

COMMIT;
