-- Create client_users table
CREATE TABLE IF NOT EXISTS client_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    client_id VARCHAR(255) NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
    
    -- Personal Details
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    date_of_birth DATE,
    gender VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100),
    postal_code VARCHAR(20),
    
    -- Authentication
    username VARCHAR(100) UNIQUE,
    password VARCHAR(255),
    
    -- Role and Permissions
    role VARCHAR(50), -- admin, manager, cashier, staff, etc.
    permissions JSONB, -- Store permissions as JSON
    
    -- Status
    status VARCHAR(20) DEFAULT 'active', -- active, inactive, suspended
    is_active BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    email_verified_at TIMESTAMP,
    phone_verified_at TIMESTAMP,
    
    -- Profile
    avatar_url TEXT,
    bio TEXT,
    preferred_language VARCHAR(10) DEFAULT 'en',
    timezone VARCHAR(50),
    
    -- Logs and Tracking
    last_login TIMESTAMP,
    last_login_ip VARCHAR(45),
    last_activity TIMESTAMP,
    login_count INTEGER DEFAULT 0,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP,
    
    -- Audit Fields
    created_by UUID REFERENCES client_users(id),
    updated_by UUID REFERENCES client_users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_client_users_client_id ON client_users(client_id);
CREATE INDEX IF NOT EXISTS idx_client_users_email ON client_users(email) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_client_users_username ON client_users(username) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_client_users_status ON client_users(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_client_users_is_active ON client_users(is_active) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_client_users_created_at ON client_users(created_at);
CREATE INDEX IF NOT EXISTS idx_client_users_deleted_at ON client_users(deleted_at) WHERE deleted_at IS NULL;

-- Create updated_at trigger
CREATE OR REPLACE FUNCTION update_client_users_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_client_users_updated_at ON client_users;
CREATE TRIGGER update_client_users_updated_at
    BEFORE UPDATE ON client_users
    FOR EACH ROW
    EXECUTE FUNCTION update_client_users_updated_at();