# Python仮想環境の作成
python -m venv venv
source venv/bin/activate

# 必要なライブラリのインストール
pip install fastapi uvicorn
pip install sqlalchemy psycopg2-binary
pip install pandas openpyxl openai
pip install python-multipart

# PostgreSQLの起動（Docker）
docker run --name excel-db-postgres \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 -d postgres:15

import pandas as pd
from openpyxl import load_workbook

def read_excel_file(file_path):
    # エクセルファイルを読み込み
    workbook = load_workbook(file_path)
    
    result = {}
    for sheet_name in workbook.sheetnames:
        # 各シートをDataFrameに変換
        df = pd.read_excel(
            file_path,
            sheet_name=sheet_name
        )
        
        # カラム名を取得
        columns = df.columns.tolist()
        
        # データを取得
        sample_data = df.head(5).values.tolist()
        
        result[sheet_name] = {
            'columns': columns,
            'data': sample_data,
            'total_rows': len(df)
        }
    
    return result

from openai import OpenAI
import json

client = OpenAI(api_key="your-api-key")

def analyze_excel_with_ai(
    columns,
    sample_data
):
    prompt = f"""
以下のエクセルデータを分析して、
適切なデータベーステーブル定義を
生成してください。

カラム名: {columns}
サンプルデータ: {sample_data[:3]}

以下の形式でJSONで返してください:
{{
  "table_name": "テーブル名",
  "columns": [
    {{
      "name": "カラム名",
      "original_name": "元のカラム名",
      "type": "データ型",
      "nullable": true,
      "description": "説明"
    }}
  ]
}}
"""
    
    response = client.chat.completions.create(
        model="gpt-4",
        messages=[
            {
                "role": "system",
                "content": "あなたはDB設計の専門家"
            },
            {
                "role": "user",
                "content": prompt
            }
        ]
    )
    
    result = json.loads(
        response.choices[0].message.content
    )
    return result

import psycopg2

def create_table_from_analysis(
    db_conn, 
    table_def, 
    data
):
    cursor = db_conn.cursor()
    
    # テーブル作成SQL生成
    table_name = table_def['table_name']
    columns_sql = []
    
    # IDカラムを追加
    columns_sql.append(
        "id SERIAL PRIMARY KEY"
    )
    
    # 各カラムの定義
    for col in table_def['columns']:
        col_name = col['name']
        col_type = col['type']
        nullable = "NULL" if col['nullable'] \
                   else "NOT NULL"
        
        # データ型のマッピング
        type_map = {
            'text': 'VARCHAR(255)',
            'integer': 'INTEGER',
            'date': 'DATE',
            'boolean': 'BOOLEAN',
            'email': 'VARCHAR(255)',
            'phone': 'VARCHAR(20)'
        }
        
        sql_type = type_map.get(
            col_type, 
            'TEXT'
        )
        columns_sql.append(
            f"{col_name} {sql_type} {nullable}"
        )
    
    # CREATE TABLE実行
    create_sql = f"""
        CREATE TABLE {table_name} (
            {', '.join(columns_sql)}
        )
    """
    cursor.execute(create_sql)
    
    # データ投入
    for row in data:
        placeholders = ', '.join(
            ['%s'] * len(row)
        )
        insert_sql = f"""
            INSERT INTO {table_name} 
            VALUES ({placeholders})
        """
        cursor.execute(insert_sql, row)
    
    db_conn.commit()
    cursor.close()

import React, { 
    useState, 
    useEffect 
} from 'react';

interface DataRow {
  [key: string]: any;
}

function DataTable() {
  const [data, setData] = 
    useState<DataRow[]>([]);
  const [columns, setColumns] = 
    useState<string[]>([]);
  const [searchTerm, setSearchTerm] = 
    useState('');

  useEffect(() => {
    // APIからデータ取得
    fetch('/api/data')
      .then(res => res.json())
      .then(result => {
        setColumns(result.columns);
        setData(result.data);
      });
  }, []);

  const filteredData = data.filter(row =>
    Object.values(row).some(val =>
      String(val)
        .toLowerCase()
        .includes(searchTerm.toLowerCase())
    )
  );

  return (
    <div>
      <input
        type="text"
        placeholder="検索..."
        value={searchTerm}
        onChange={(e) => 
          setSearchTerm(e.target.value)
        }
      />
      
      <table>
        <thead>
          <tr>
            {columns.map(col => 
              <th key={col}>{col}</th>
            )}
          </tr>
        </thead>
        <tbody>
          {filteredData.map((row, idx) => (
            <tr key={idx}>
              {columns.map(col => 
                <td key={col}>
                  {row[col]}
                </td>
              )}
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

from fastapi import FastAPI
from pydantic import BaseModel
from typing import Dict, Any

app = FastAPI()

class RecordCreate(BaseModel):
    data: Dict[str, Any]

class RecordUpdate(BaseModel):
    id: int
    data: Dict[str, Any]

@app.get("/api/data")
async def get_data(
    table: str, 
    search: str = ""
):
    # データベースから取得
    cursor = db_conn.cursor()
    
    if search:
        query = f"""
            SELECT * FROM {table} 
            WHERE ... LIKE %s
        """
        cursor.execute(
            query, 
            (f"%{search}%",)
        )
    else:
        cursor.execute(
            f"SELECT * FROM {table}"
        )
    
    columns = [
        desc[0] 
        for desc in cursor.description
    ]
    rows = cursor.fetchall()
    
    return {
        "columns": columns, 
        "data": rows
    }

@app.post("/api/data")
async def create_record(
    table: str, 
    record: RecordCreate
):
    cursor = db_conn.cursor()
    
    columns = ', '.join(
        record.data.keys()
    )
    placeholders = ', '.join(
        ['%s'] * len(record.data)
    )
    values = tuple(
        record.data.values()
    )
    
    query = f"""
        INSERT INTO {table} 
        ({columns}) 
        VALUES ({placeholders})
    """
    cursor.execute(query, values)
    db_conn.commit()
    
    return {"message": "作成成功"}

@app.put("/api/data")
async def update_record(
    table: str, 
    record: RecordUpdate
):
    cursor = db_conn.cursor()
    
    set_clause = ', '.join([
        f"{k} = %s" 
        for k in record.data.keys()
    ])
    values = tuple(
        record.data.values()
    ) + (record.id,)
    
    query = f"""
        UPDATE {table} 
        SET {set_clause} 
        WHERE id = %s
    """
    cursor.execute(query, values)
    db_conn.commit()
    
    return {"message": "更新成功"}

@app.delete("/api/data/{record_id}")
async def delete_record(
    table: str, 
    record_id: int
):
    cursor = db_conn.cursor()
    cursor.execute(
        f"DELETE FROM {table} WHERE id = %s", 
        (record_id,)
    )
    db_conn.commit()
    
    return {"message": "削除成功"}

from fastapi import (
    Depends, 
    HTTPException, 
    status
)
from fastapi.security import (
    OAuth2PasswordBearer
)
from jose import JWTError, jwt
from datetime import datetime, timedelta

SECRET_KEY = "your-secret-key-here"
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 30

oauth2_scheme = OAuth2PasswordBearer(
    tokenUrl="token"
)

def create_access_token(data: dict):
    to_encode = data.copy()
    expire = datetime.utcnow() + timedelta(
        minutes=ACCESS_TOKEN_EXPIRE_MINUTES
    )
    to_encode.update({"exp": expire})
    
    encoded_jwt = jwt.encode(
        to_encode, 
        SECRET_KEY, 
        algorithm=ALGORITHM
    )
    return encoded_jwt

async def get_current_user(
    token: str = Depends(oauth2_scheme)
):
    credentials_exception = HTTPException(
        status_code=status.HTTP_401_UNAUTHORIZED,
        detail="認証に失敗しました"
    )
    
    try:
        payload = jwt.decode(
            token, 
            SECRET_KEY, 
            algorithms=[ALGORITHM]
        )
        username: str = payload.get("sub")
        if username is None:
            raise credentials_exception
    except JWTError:
        raise credentials_exception
    
    return username

# 保護されたエンドポイント
@app.get("/api/protected")
async def protected_route(
    current_user: str = 
        Depends(get_current_user)
):
    return {
        "message": f"こんにちは、{current_user}さん"
    }