-- 医師管理
CREATE TABLE specialties (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT
);

CREATE TABLE doctors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name VARCHAR(200) NOT NULL,
  license_number VARCHAR(50) NOT NULL UNIQUE,
  specialty_id INT NOT NULL REFERENCES specialties(id),
  hospital_id UUID NOT NULL REFERENCES hospitals(id),
  consultation_fee DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE doctor_schedules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  doctor_id UUID NOT NULL REFERENCES doctors(id),
  day_of_week INT NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  slot_duration_minutes INT NOT NULL DEFAULT 30,
  UNIQUE (doctor_id, day_of_week, start_time)
);

-- 予約管理
CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id UUID NOT NULL REFERENCES patients(id),
  doctor_id UUID NOT NULL REFERENCES doctors(id),
  appointment_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'scheduled'
    CHECK (status IN ('scheduled','confirmed','in_progress','completed','cancelled','no_show')),
  cancellation_reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_appointments_doctor_date ON appointments(doctor_id, appointment_date);
CREATE INDEX idx_appointments_patient_date ON appointments(patient_id, appointment_date DESC);
CREATE INDEX idx_appointments_date_status ON appointments(appointment_date, status);

-- 空き枠検索用ビュー
CREATE VIEW available_slots AS
SELECT
  ds.doctor_id,
  d.full_name AS doctor_name,
  s.name AS specialty,
  gs.slot_start,
  gs.slot_start + (ds.slot_duration_minutes || ' minutes')::INTERVAL AS slot_end
FROM doctor_schedules ds
JOIN doctors d ON ds.doctor_id = d.id
JOIN specialties s ON d.specialty_id = s.id
CROSS JOIN LATERAL generate_series(
  ds.start_time,
  ds.end_time - (ds.slot_duration_minutes || ' minutes')::INTERVAL,
  (ds.slot_duration_minutes || ' minutes')::INTERVAL
) AS gs(slot_start)
WHERE NOT EXISTS (
  SELECT 1 FROM appointments a
  WHERE a.doctor_id = ds.doctor_id
    AND a.start_time = gs.slot_start
    AND a.status NOT IN ('cancelled', 'no_show')
);

設計判断:

• specialtiesを別テーブルにして3NF準拠（推移的依存を排除）
• doctor_schedulesで曜日ベースの定期スケジュールを管理
• インデックスはアクセスパターンに直結する3つを作成

// DynamoDB シングルテーブル設計
interface MedicalRecordItem {
  PK: string;      // パーティションキー
  SK: string;      // ソートキー
  GSI1PK?: string; // GSI1 パーティションキー（医師検索用）
  GSI1SK?: string;
  [key: string]: unknown;
}

const records: MedicalRecordItem[] = [
  // 診療記録メイン
  {
    PK: 'PATIENT#P001',
    SK: 'RECORD#2024-06-15#REC001',
    GSI1PK: 'DOCTOR#D001',
    GSI1SK: 'RECORD#2024-06-15#REC001',
    recordId: 'REC001',
    appointmentId: 'APT001',
    doctorId: 'D001',
    doctorName: '山田医師',
    diagnosis: '急性上気道炎',
    symptoms: ['発熱', '咳', '倦怠感'],
    notes: '3日前から38度の発熱。咽頭発赤あり。',
    createdAt: '2024-06-15T10:30:00Z',
  },

  // 処方箋（診療記録の子要素）
  {
    PK: 'RECORD#REC001',
    SK: 'PRESCRIPTION#PRE001',
    prescriptionId: 'PRE001',
    medications: [
      { name: 'ロキソニン', dosage: '60mg', frequency: '1日3回', days: 5 },
      { name: 'ムコダイン', dosage: '500mg', frequency: '1日3回', days: 5 },
    ],
    pharmacyId: 'PHARM001',
    issuedAt: '2024-06-15T11:00:00Z',
  },

  // 検査結果（診療記録の子要素）
  {
    PK: 'RECORD#REC001',
    SK: 'TEST#TEST001',
    testId: 'TEST001',
    testType: '血液検査',
    results: {
      WBC: { value: 9200, unit: '/μL', reference: '3500-9500', status: 'normal' },
      CRP: { value: 2.5, unit: 'mg/dL', reference: '< 0.3', status: 'high' },
    },
    resultDate: '2024-06-15T14:00:00Z',
  },
];

// クエリパターン
// 1. 患者の診療履歴: Query PK=PATIENT#P001, SK begins_with RECORD#, ScanIndexForward=false
// 2. 医師の診察一覧: Query GSI1PK=DOCTOR#D001, GSI1SK begins_with RECORD#
// 3. 診療記録詳細: Query PK=RECORD#REC001（処方箋・検査結果も一括取得）

設計判断:

• 患者IDをPKにして診療日でソート（最頻出パターンを最適化）
• GSI1で医師による検索をサポート
• 処方箋・検査結果は診療記録の子要素として別アイテムに（16MBリミット回避）

// ドメインイベント定義
type AppointmentEvent =
  | { type: 'AppointmentRequested'; payload: {
      patientId: string; doctorId: string;
      requestedDate: string; requestedTime: string;
      reason: string;
    }}
  | { type: 'AppointmentConfirmed'; payload: {
      confirmedAt: string; confirmationCode: string;
    }}
  | { type: 'AppointmentRescheduled'; payload: {
      oldDate: string; oldTime: string;
      newDate: string; newTime: string;
      reason: string;
    }}
  | { type: 'AppointmentCancelled'; payload: {
      cancelledBy: 'patient' | 'doctor' | 'system';
      reason: string; cancelledAt: string;
    }}
  | { type: 'PatientCheckedIn'; payload: {
      checkedInAt: string;
    }}
  | { type: 'ConsultationStarted'; payload: {
      startedAt: string; roomNumber: string;
    }}
  | { type: 'ConsultationCompleted'; payload: {
      completedAt: string; medicalRecordId: string;
    }};

// 状態遷移図
// Requested → Confirmed → CheckedIn → InProgress → Completed
//     │            │
//     └── Cancelled ┘

// Aggregate
class AppointmentAggregate {
  private state: {
    status: string;
    patientId: string;
    doctorId: string;
    date: string;
    time: string;
    rescheduleCount: number;
  };

  cancel(cancelledBy: string, reason: string): AppointmentEvent {
    if (['completed', 'cancelled'].includes(this.state.status)) {
      throw new Error(`${this.state.status}の予約はキャンセルできません`);
    }
    if (this.state.status === 'in_progress') {
      throw new Error('診察中の予約はキャンセルできません');
    }
    return {
      type: 'AppointmentCancelled',
      payload: { cancelledBy, reason, cancelledAt: new Date().toISOString() },
    };
  }

  reschedule(newDate: string, newTime: string, reason: string): AppointmentEvent {
    if (this.state.rescheduleCount >= 3) {
      throw new Error('予約変更は3回までです');
    }
    return {
      type: 'AppointmentRescheduled',
      payload: {
        oldDate: this.state.date, oldTime: this.state.time,
        newDate, newTime, reason,
      },
    };
  }
}

// Projection: 読み取りモデル
// 1. 予約ボード（医師別の当日スケジュール）
interface DoctorScheduleView {
  doctorId: string;
  date: string;
  slots: Array<{
    time: string;
    appointmentId: string;
    patientName: string;
    status: string;
    reason: string;
  }>;
}

// 2. 患者の予約履歴
interface PatientAppointmentHistory {
  patientId: string;
  upcoming: Array<{ appointmentId: string; date: string; doctorName: string }>;
  past: Array<{ appointmentId: string; date: string; doctorName: string; status: string }>;
  cancellationCount: number;
}

apiVersion: datamesh/v1
kind: DataProduct
metadata:
  name: appointment-analytics
  domain: appointment
  owner: appointment-team
  description: "予約に関する分析用データプロダクト"

spec:
  outputPorts:
    - name: appointment-events
      type: event-stream
      topic: appointment.analytics.events.v1
      format: avro
      description: "リアルタイム予約イベントストリーム"

    - name: daily-appointment-stats
      type: dataset
      location: s3://data-products/appointment/daily-stats/
      format: parquet
      partitionBy: [date, hospital_id]
      refreshSchedule: "0 2 * * *"
      description: "日次予約統計データセット"

    - name: appointment-summary-api
      type: rest-api
      endpoint: /api/v1/data-products/appointments/summary
      description: "予約サマリー API（集計済み）"

  schema:
    fields:
      - name: appointment_id
        type: string
        pii: false
      - name: patient_id
        type: string
        pii: true
        classification: restricted
        description: "匿名化必須: hash化して提供"
      - name: doctor_id
        type: string
        pii: false
      - name: specialty
        type: string
      - name: appointment_date
        type: date
      - name: status
        type: enum
        values: [requested, confirmed, checked_in, in_progress, completed, cancelled, no_show]
      - name: wait_time_minutes
        type: integer
        description: "受付からの待ち時間（分）"
      - name: consultation_duration_minutes
        type: integer
      - name: cancellation_reason
        type: string
        nullable: true

  slo:
    freshness: "< 2 hours"
    completeness: 99.5
    accuracy: 99.9
    availability: "99.5%"

  governance:
    dataClassification: confidential
    retentionPolicy: "5 years"
    anonymization:
      - field: patient_id
        method: sha256_hash
      - field: date_of_birth
        method: age_bucket  # 10歳単位に丸める
    accessControl:
      - role: hospital-admin
        permissions: [read]
        filter: "hospital_id = requester.hospital_id"
      - role: data-analyst
        permissions: [read]
        requiredTraining: "医療データ取扱研修"
      - role: appointment-team
        permissions: [read, write, admin]

設計判断:

• 患者IDは医療データのためrestricted分類、SHA256ハッシュで匿名化
• 病院IDでのパーティション分割で、マルチテナントのアクセス制御を効率化
• 医療データの特性上、保持期間は5年（カルテ保存義務を考慮）