-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE, -- Nullable for blockchain-only users
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on email for faster lookups
CREATE INDEX idx_users_email ON users(email) WHERE email IS NOT NULL;

-- Create enum for identity types
CREATE TYPE identity_type AS ENUM ('email_password', 'blockchain_address', 'did');

-- Create identities table for multi-factor authentication
CREATE TABLE IF NOT EXISTS identities (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type identity_type NOT NULL,
    identifier VARCHAR(500) NOT NULL, -- Email, blockchain address, or DID
    credential TEXT, -- Password hash, public key, or other credential data
    is_primary_login BOOLEAN DEFAULT false,
    metadata JSONB, -- Store additional identity-specific data
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(type, identifier)
);

-- Create indexes for identities
CREATE INDEX idx_identities_user_id ON identities(user_id);
CREATE INDEX idx_identities_type_identifier ON identities(type, identifier);
CREATE INDEX idx_identities_primary_login ON identities(user_id, is_primary_login) WHERE is_primary_login = true;

-- Create enum for user roles
CREATE TYPE user_role AS ENUM ('ADMIN', 'STAFF', 'CLIENT');

-- Create user_roles table for role-based access control
CREATE TABLE IF NOT EXISTS user_roles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role user_role NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, role)
);

-- Create index on user_roles
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updated_at
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_identities_updated_at BEFORE UPDATE ON identities
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();