# MySQL Database Setup Guide

## Prerequisites

- MySQL Server 5.7 or later installed
- Node.js 16 or later

## Configuration

### 1. Database Configuration

Edit `server/.env` file with your MySQL connection details:

```env
PORT=3001
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASSWORD=your_password_here
DB_NAME=promo_tracker

# SMTP Configuration (for email sending)
# Required for email verification and password reset
SMTP_HOST=smtp.example.com
SMTP_PORT=587
SMTP_SECURE=false
SMTP_USER=your-email@example.com
SMTP_PASS=your-email-password
SMTP_FROM=noreply@example.com
FRONTEND_URL=http://localhost:5173
APP_SECRET=your-secret-key-here

# CORS Configuration
# 許可するオリジンをカンマ区切りで指定（例: "http://localhost:5173,http://localhost:5174"）
# 未設定の場合はFRONTEND_URLが使用されます
# すべてのオリジンを許可する場合は "*" を指定（本番環境では非推奨）
CORS_ORIGIN=http://localhost:5173,http://localhost:5174
```

**SMTP設定について:**
- `SMTP_SECURE`: `true` はポート465（SSL/TLS）、`false` はポート587（STARTTLS）
- 主要なSMTPプロバイダーの設定例:
  - Gmail: `smtp.gmail.com`, ポート587
  - Outlook: `smtp-mail.outlook.com`, ポート587
  - Yahoo: `smtp.mail.yahoo.com`, ポート587
  - SendGrid: `smtp.sendgrid.net`, ポート587
  - AWS SES: `email-smtp.{region}.amazonaws.com`, ポート587

**注意:** SMTP設定がない場合、メール送信機能は無効になりますが、アプリケーションは動作します（コンソールにメール内容が出力されます）。

### 2. Create Database and Run Migrations

The migration system will automatically create the database if it doesn't exist.

```bash
cd server
npm run migrate
```

This command will:
- Create the `promo_tracker` database (if it doesn't exist)
- Execute all SQL migration files in order
- Create tables with the hierarchical structure
- Insert sample data including:
  - Default campaign template with 4 phases
  - Sample projects with parent-child relationships
  - Sample tasks with sub-tasks
  - Sample analytics data
  - Sample retrospective data

### 3. Verify Migration

After running migrations, you should see:
```
Starting database migration...
Database: promo_tracker
Host: localhost
Database 'promo_tracker' ready
MySQL connection pool created
Found 2 migration files
Running migration: 001_create_initial_schema.sql
✓ Completed migration: 001_create_initial_schema.sql
Running migration: 002_insert_sample_data.sql
✓ Completed migration: 002_insert_sample_data.sql

✓ Migration completed successfully!
```

## Database Schema

### Key Tables

**Projects (Hierarchical)**
- `projects` - Main projects and sub-projects
- `parent_project_id` - Links to parent project (nullable)
- Supports unlimited nesting depth

**Tasks (Hierarchical)**
- `project_tasks` - Tasks and sub-tasks
- `parent_task_id` - Links to parent task (nullable)
- `project_phase_id` - Optional phase assignment
- Can be standalone or part of a phase

**Dependencies**
- `task_dependencies` - Task dependencies for Gantt chart
- Supports different dependency types:
  - finish_to_start (default)
  - start_to_start
  - finish_to_finish
  - start_to_finish

### New Features in MySQL Version

1. **Hierarchical Projects**: Projects can have sub-projects
2. **Hierarchical Tasks**: Tasks can have sub-tasks
3. **Task Dependencies**: Define relationships between tasks
4. **Date Tracking**: Start/end dates for projects and tasks
5. **Progress Tracking**: Progress percentage for projects and tasks
6. **Priority Levels**: Task priority (low, medium, high, urgent)
7. **Enhanced Status**: More status options including 'blocked'

## Running the Application

### Start the MySQL-based Server

```bash
cd server
node server-mysql.js
```

Or use the original SQLite server (for comparison):
```bash
cd server
npm run dev
```

### Start the Client

```bash
cd client
npm run dev
```

## Sample Data Overview

The migration includes comprehensive sample data:

### Projects
1. **Main Project**: "2025年春季新商品Aプロモーション"
   - Status: Active
   - Budget: ¥50,000,000
   - Has 2 sub-projects:
     - SNS広告施策 (¥15,000,000)
     - リスティング広告施策 (¥8,000,000)

2. **Completed Project**: "2024年秋季新商品Bプロモーション"
   - Status: Completed
   - With retrospective data

### Tasks
- 50+ sample tasks across all phases
- Tasks with parent-child relationships
- Tasks with dates for Gantt chart visualization
- Mix of internal and outsourced tasks
- Various statuses and priorities

### Analytics
- Multiple metrics tracked (CV数, CPA, CV率, etc.)
- Data from different sources (GA4, Google Ads, Instagram)
- Time-series data for trend analysis

## Gantt Chart Features

The Gantt chart visualization includes:

1. **Project Timeline**: Main project and sub-projects
2. **Task Timeline**: All tasks with dates
3. **Dependencies**: Visual representation of task relationships
4. **Progress Indicators**: Color-coded by status
5. **Interactive**: Click tasks for details
6. **View Modes**: Day, Week, Month, Year views

## Troubleshooting

### Connection Errors

If you get connection errors:
1. Verify MySQL is running
2. Check credentials in `.env`
3. Ensure MySQL user has CREATE DATABASE permission

### Migration Fails

If migration fails partway:
1. Drop the database: `DROP DATABASE IF EXISTS promo_tracker;`
2. Re-run migration: `npm run migrate`

### Character Encoding Issues

If you see garbled Japanese characters:
- Ensure MySQL is configured for utf8mb4
- Check `my.cnf` or `my.ini`:
  ```
  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci
  ```

## API Changes

New endpoints for MySQL version:

### Gantt Chart Data
```
GET /api/gantt/:projectId
```
Returns projects, tasks, and dependencies for Gantt visualization

### Hierarchical Queries
```
GET /api/projects?parent_id=null
GET /api/projects?parent_id={projectId}
GET /api/tasks/:id/subtasks
```

### Task Creation with Hierarchy
```
POST /api/tasks
{
  "parent_task_id": "uuid",
  "project_id": "uuid",
  "task_name": "Subtask name",
  ...
}
```

## Migration from SQLite

To migrate from the SQLite version:

1. Export existing data from SQLite
2. Transform to MySQL INSERT statements
3. Run migrations
4. Import transformed data
5. Update server to use `server-mysql.js`

Note: The MySQL version includes enhanced features not available in SQLite version.