migrations/001_create_tables.sql

-- Database Schema for Profile and Contact Management UI
-- Feature: 012-profile-contact-ui
-- Version: 1.0.0

-- User profiles table
CREATE TABLE IF NOT EXISTS user_profiles (
    user_id VARCHAR(255) PRIMARY KEY NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    profile_picture_url TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NOT NULL,
    session_id VARCHAR(255) NOT NULL UNIQUE
);

-- Contact sessions table
CREATE TABLE IF NOT EXISTS contact_sessions (
    session_id VARCHAR(255) PRIMARY KEY NOT NULL,
    user_id VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255) NOT NULL,
    contact_description TEXT CHECK(LENGTH(contact_description) <= 500),
    is_reference BOOLEAN NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_interaction TIMESTAMP NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user_profiles(user_id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_contact_sessions_user ON contact_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_contact_sessions_sort ON contact_sessions(user_id, last_interaction DESC);
CREATE INDEX IF NOT EXISTS idx_contact_sessions_reference ON contact_sessions(is_reference);