<?php
// --- DB接続設定 ---
$dsn = 'mysql:dbname=databank;host=localhost;charset=utf8mb4';
$user = 'root';
$password = 'Vrock666$$$';

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// ---------- 対象月 ----------
// デフォルト：先月
$targetMonth = isset($_GET['month'])
    ? $_GET['month']
    : date('Y-m', strtotime('first day of last month'));

// 先月
$prevMonth = date('Y-m', strtotime($targetMonth . ' -1 month'));

$labelThisMonth = date('Y年n月', strtotime($targetMonth . '-01'));
$labelPrevMonth = date('Y年n月', strtotime($targetMonth . ' -1 month'));

// ---------- 表示対象企業（順番固定） ----------
$displayCompanies = [
    '株式会社フレスコ',
    '三菱地所ホーム',
    'OPEC',
    'GENERAL',
    '太平ハウス・ラボ',
    'ジャーブネット',
    'あさひハウジングセンター',
    '住友不動産ハウジング',
    'イエタス',
    'ワンズ・サポート・システム',
    'ITC',
    '旭化成建材',
    'WIT',
    '福工房',
    '株式会社高砂建設',
    '京阪電鉄不動産',
    'よかタウン',
    '篠原商店'
];

// ---------- 企業ID → 企業名（ログイン用） ----------
$companyMap = [
    'Fresco'=>'株式会社フレスコ',
    'MJHOME'=>'三菱地所ホーム',
    'OPEC'=>'OPEC',
    'GENERAL'=>'GENERAL',
    'houselabo'=>'太平ハウス・ラボ',
    'JAHBnet'=>'ジャーブネット',
    'AHC'=>'あさひハウジングセンター',
    'Sumitomo-rd'=>'住友不動産ハウジング',
    'YETUS'=>'イエタス',
    'ONESNET'=>'ワンズ・サポート・システム',
    'ITC'=>'ITC',
    'AKK'=>'旭化成建材',
    'WI-TOKYO'=>'WIT',
    'fukukobo'=>'福工房',
    'takasago'=>'株式会社高砂建設',
    'KEIHAN'=>'京阪電鉄不動産',
    'yoka-town'=>'よかタウン',
    'SHINOHARA'=>'篠原商店'
];

// ---------- GA ----------
$chartData = [
    'Resolution'=>['labels'=>[], 'data'=>[]],
    'Browser'=>['labels'=>[], 'data'=>[]],
    'OS'=>['labels'=>[], 'data'=>[]],
];
$limits = ['Resolution'=>8,'Browser'=>5,'OS'=>5];

try {
    // ※ DB接続は省略（既存の $pdo を使用）

    // ---------- ディスク使用量 ----------
    $monthCur   = $targetMonth;
    $monthPrev  = date('Y-m', strtotime($monthCur.' -1 month'));
    $monthPrev2 = date('Y-m', strtotime($monthCur.' -2 month'));

    $sqlDisk = "
    SELECT
        cur.company_name,
        (cur.total_size_gb - IFNULL(prev.total_size_gb,0)) AS cur_size,
        (cur.file_count - IFNULL(prev.file_count,0)) AS cur_files,
        (prev.total_size_gb - IFNULL(prev2.total_size_gb,0)) AS prev_size,
        (prev.file_count - IFNULL(prev2.file_count,0)) AS prev_files,
        cur.total_size_gb AS total_size,
        cur.file_count AS total_files
    FROM company_file_usage cur
    LEFT JOIN company_file_usage prev
      ON cur.company_id = prev.company_id AND prev.target_month = :m1
    LEFT JOIN company_file_usage prev2
      ON cur.company_id = prev2.company_id AND prev2.target_month = :m0
    WHERE cur.target_month = :m2
    ";
    $stmt = $pdo->prepare($sqlDisk);
    $stmt->execute([':m0'=>$monthPrev2,':m1'=>$monthPrev,':m2'=>$monthCur]);
    $diskRows = $stmt->fetchAll();

    // company_name をキーに
    $diskMap = [];
    foreach ($diskRows as $r) {
        $diskMap[$r['company_name']] = $r;
    }

    // ---------- ログイン回数 ----------
    $sqlLogin = "
    SELECT
        company_id,
        SUM(CASE WHEN target_month=:m1 THEN login_ok_cnt ELSE 0 END) AS cur_ok,
        SUM(CASE WHEN target_month=:m1 THEN login_ng_cnt ELSE 0 END) AS cur_ng,
        SUM(CASE WHEN target_month=:m2 THEN login_ok_cnt ELSE 0 END) AS prev_ok,
        SUM(CASE WHEN target_month=:m2 THEN login_ng_cnt ELSE 0 END) AS prev_ng
    FROM company_login_stats
    WHERE target_month IN (:m1,:m2)
    GROUP BY company_id
    ";
    $stmt = $pdo->prepare($sqlLogin);
    $stmt->execute([':m1'=>$targetMonth,':m2'=>$prevMonth]);
    $loginRows = $stmt->fetchAll();

    // 企業名キーに変換
    $loginMap = [];
    foreach ($loginRows as $r) {
        if (isset($companyMap[$r['company_id']])) {
            $loginMap[$companyMap[$r['company_id']]] = $r;
        }
    }

    // ---------- GA ----------
    $sqlGA = "
    SELECT category,label,active_users
    FROM ga4_monthly_report
    WHERE report_month=:month
    ORDER BY category,active_users DESC
    ";
    $stmt = $pdo->prepare($sqlGA);
    $stmt->execute([':month'=>$targetMonth]);
    $gaRows = $stmt->fetchAll();

    foreach ($gaRows as $r) {
        $chartData[$r['category']]['labels'][] = $r['label'];
        $chartData[$r['category']]['data'][]   = $r['active_users'];
    }
    foreach ($limits as $k=>$v) {
        $chartData[$k]['labels'] = array_slice($chartData[$k]['labels'],0,$v);
        $chartData[$k]['data']   = array_slice($chartData[$k]['data'],0,$v);
    }

} catch (PDOException $e) {
    exit('DB Error: '.$e->getMessage());
}
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title><?= htmlspecialchars($labelThisMonth) ?> 月報</title>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2"></script>
<style>
body{background:#e5e7eb;margin:0;padding:40px 0;font-family:"Helvetica Neue",Arial;}
.page{width:1000px;margin:0 auto;background:#fff;padding:40px 50px;}
h1{font-size:26px;color:#1a73e8;border-bottom:2px solid #e5e7eb;}
h2{margin-top:50px;border-left:5px solid #1a73e8;padding-left:12px;}
table{width:100%;border-collapse:collapse;font-size:14px;}
th,td{border:1px solid #d1d5db;padding:10px;}
th{background:#f3f4f6;text-align:center;}
td{text-align:right;}
td:first-child{text-align:left;}
tr:nth-child(even) td{background:#fafafa;}
.report-container{display:flex;gap:30px;}
.chart-box{flex:1;min-width:0;overflow:hidden;background:#fafafa;padding:20px;border-radius:10px;}
@media print{
 body{background:#fff;padding:0;}
 .page{padding:20mm;}
 table{page-break-inside:avoid;}
}
</style>
</head>

<body>
<div class="page">
<h1><?= htmlspecialchars($labelThisMonth) ?> 月報</h1>

<h2>① ディスク使用量</h2>
<table>
<tr>
<th rowspan="2">企業名</th>
<th colspan="2"><?= $labelThisMonth ?></th>
<th colspan="2"><?= $labelPrevMonth ?></th>
<th colspan="2">合計</th>
</tr>
<tr>
<th>利用量(GB)</th><th>ファイル数</th>
<th>利用量(GB)</th><th>ファイル数</th>
<th>利用量(GB)</th><th>ファイル数</th>
</tr>
<?php foreach ($displayCompanies as $c):
 if (!isset($diskMap[$c])) continue;
 $r = $diskMap[$c]; ?>
<tr>
<td><?= htmlspecialchars($c) ?></td>
<td><?= number_format($r['cur_size'],2) ?></td>
<td><?= number_format($r['cur_files']) ?></td>
<td><?= number_format($r['prev_size'],2) ?></td>
<td><?= number_format($r['prev_files']) ?></td>
<td><?= number_format($r['total_size'],2) ?></td>
<td><?= number_format($r['total_files']) ?></td>
</tr>
<?php endforeach; ?>
</table>

<h2>② ログイン回数</h2>
<table>
<tr>
<th rowspan="2">企業名</th>
<th colspan="2"><?= $labelThisMonth ?></th>
<th colspan="2"><?= $labelPrevMonth ?></th>
</tr>
<tr><th>成功</th><th>失敗</th><th>成功</th><th>失敗</th></tr>
<?php foreach ($displayCompanies as $c):
 if (!isset($loginMap[$c])) continue;
 $r = $loginMap[$c]; ?>
<tr>
<td><?= htmlspecialchars($c) ?></td>
<td><?= number_format($r['cur_ok']) ?></td>
<td><?= number_format($r['cur_ng']) ?></td>
<td><?= number_format($r['prev_ok']) ?></td>
<td><?= number_format($r['prev_ng']) ?></td>
</tr>
<?php endforeach; ?>
</table>

<h2>③ Google Analytics</h2>
<div class="report-container">
<div class="chart-box"><canvas id="chartResolution"></canvas></div>
<div class="chart-box"><canvas id="chartBrowser"></canvas></div>
<div class="chart-box"><canvas id="chartOS"></canvas></div>
</div>
</div>

<script>
Chart.register(ChartDataLabels);
const rawData = <?= json_encode($chartData) ?>;
function draw(id, labels, data){
 new Chart(document.getElementById(id),{
  type:'bar',
  data:{labels:labels,datasets:[{data:data,backgroundColor:'#4285f4',barThickness:10}]},
options:{
  indexAxis:'y',
  responsive:true,
  maintainAspectRatio:false,
  plugins:{
    legend:{display:false},
    datalabels:{
      anchor:'end',
      align:'right',   // ← ここ重要
      offset:8,        // ← ここ重要
      color:'#333',
      font:{
        weight:'bold',
        size:11
      }
    }
  },
  layout:{
    padding:{ right:40 }   // ★ 右側に余白を作る
  }
}
 });
}
draw('chartResolution',rawData.Resolution.labels,rawData.Resolution.data);
draw('chartBrowser',rawData.Browser.labels,rawData.Browser.data);
draw('chartOS',rawData.OS.labels,rawData.OS.data);
</script>
</body>
</html>