eagleisbatman/tips-mcp-server

# Clone the repository
git clone https://github.com/eagleisbatman/tips-mcp-server.git
cd tips-mcp-server

# Install dependencies
npm install

# Set up environment variables
cp .env.example .env
# Edit .env with your database credentials

# Run database migrations (see Database Schema section)

# Run in development mode
npm run dev

-- Tip categories
CREATE TABLE tip_categories (
  id VARCHAR(50) PRIMARY KEY,
  name_vi VARCHAR(100) NOT NULL,
  name_en VARCHAR(100) NOT NULL,
  icon VARCHAR(10) NOT NULL,
  color VARCHAR(7) NOT NULL,
  background_color VARCHAR(7) NOT NULL,
  priority INT NOT NULL DEFAULT 10,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Tips
CREATE TABLE tips (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category_id VARCHAR(50) REFERENCES tip_categories(id),
  title_vi VARCHAR(100) NOT NULL,
  title_en VARCHAR(100) NOT NULL,
  content_vi TEXT NOT NULL,
  content_en TEXT NOT NULL,
  actionable BOOLEAN DEFAULT false,
  action_vi VARCHAR(50),
  action_en VARCHAR(50),
  action_type VARCHAR(50),
  action_data JSONB DEFAULT '{}',
  regions TEXT[] DEFAULT '{}',
  crops TEXT[] DEFAULT '{}',
  conditions JSONB DEFAULT '{}',
  urgency VARCHAR(20) DEFAULT 'medium',
  valid_from TIMESTAMP,
  valid_to TIMESTAMP,
  source VARCHAR(50) DEFAULT 'manual',
  active BOOLEAN DEFAULT true,
  view_count INT DEFAULT 0,
  dismiss_count INT DEFAULT 0,
  action_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Tip interactions
CREATE TABLE tip_interactions (
  id SERIAL PRIMARY KEY,
  tip_id UUID REFERENCES tips(id) ON DELETE CASCADE,
  device_id VARCHAR(255) NOT NULL,
  interaction_type VARCHAR(20) NOT NULL,
  region VARCHAR(50),
  language VARCHAR(10) DEFAULT 'vi',
  created_at TIMESTAMP DEFAULT NOW()
);