#!/bin/bash
# =============================================================================
# 单库容量实测：回答「单库上限在哪里」
#
# 测试目标：
#   1. 造出接近真实规模的数据（房源 / 租客 / 账单）
#   2. 测单表扫描、索引查询、聚合统计的真实耗时
#   3. 测并发下的 TPS 上限
#
# ⚠️ 口径说明：本机 PostgreSQL 是默认配置（shared_buffers=128MB），
#    生产（阿里云）会调到物理内存的 25%，结论需按比例修正。
# =============================================================================
set -u
DB=rental_bench
ROOM_ROWS=${1:-1000000}      # 默认 100 万房源
PERSON_ROWS=${2:-3000000}    # 默认 300 万租客（千万级取 1/3 省时）
BILL_ROWS=${3:-5000000}      # 默认 500 万账单

echo "=============================================="
echo " 单库容量实测"
echo " 房源: $ROOM_ROWS 行 / 租客: $PERSON_ROWS 行 / 账单: $BILL_ROWS 行"
echo "=============================================="

dropdb --if-exists "$DB" 2>/dev/null
createdb "$DB" || exit 1
DIR="$(cd "$(dirname "$0")/../DDL" && pwd)"
if [ ! -f "$DIR/01-平台租户房源.sql" ]; then
  echo "❌ 找不到 DDL 目录: $DIR"
  exit 1
fi

echo ""
echo "[1/6] 建表结构..."
for f in 01-平台租户房源.sql 02-租客合同收租.sql; do
  if ! psql -d "$DB" -q -v ON_ERROR_STOP=1 -f "$DIR/$f" > /tmp/ddl_err.txt 2>&1; then
    echo "      ❌ 建表失败: $f"
    grep -E "ERROR|错误" /tmp/ddl_err.txt | head -3
    exit 1
  fi
done
echo "      ✅ 结构就绪"

echo ""
echo "[2/6] 造基础租户与楼盘..."
psql -d "$DB" -q <<'SQL'
INSERT INTO tenant (tenant_code, tenant_name) VALUES ('BENCH','压测租户');
INSERT INTO property (tenant_id, property_code, property_name, city, district)
SELECT id, 'BP001', '压测小区', '杭州', '西湖区' FROM tenant WHERE tenant_code='BENCH';
SQL
echo "      ✅ 完成"

echo ""
echo "[3/6] 造 $ROOM_ROWS 行房源..."
START=$(date +%s)
psql -d "$DB" -q <<SQL
INSERT INTO room (tenant_id, property_id, room_no, rent_mode, status,
                  building_area, layout, list_price, bed_count)
SELECT 1, 1,
       'R' || g,
       CASE WHEN g % 10 < 2 THEN 'short' ELSE 'long' END,
       (ARRAY['vacant','occupied','reserved','maintenance'])[1 + (g % 4)],
       45 + (g % 120),
       (ARRAY['一室一厅','两室一厅','三室两厅','四室两厅'])[1 + (g % 4)],
       1500 + (g % 60) * 100,
       CASE WHEN g % 7 = 0 THEN 2 ELSE 0 END
  FROM generate_series(1, $ROOM_ROWS) g;
SQL
echo "      ✅ 完成，耗时 $(( $(date +%s) - START )) 秒"

echo ""
echo "[4/6] 造 $PERSON_ROWS 行租客..."
START=$(date +%s)
psql -d "$DB" -q <<SQL
INSERT INTO tenant_person (origin_tenant_id, person_name, id_type, phone, id_no_hash)
SELECT 1, '租客' || g, 'cn_id', '139' || lpad(g::text, 8, '0'),
       md5('bench' || g)
  FROM generate_series(1, $PERSON_ROWS) g;
SQL
echo "      ✅ 完成，耗时 $(( $(date +%s) - START )) 秒"

echo ""
echo "[5/6] 造 $BILL_ROWS 行账单..."
START=$(date +%s)
psql -d "$DB" -q <<SQL
INSERT INTO bill (tenant_id, bill_no, contract_id, room_id, person_id, bill_type,
                  period_start, period_end, due_date, amount_due,
                  status, idempotency_key)
SELECT 1, 'B' || g, NULL, 1 + (g % $ROOM_ROWS), 1 + (g % $PERSON_ROWS), 'rent',
       d, d + 29, d + 4, 3000 + (g % 10) * 100,
       (ARRAY['pending','paid','paid','paid','overdue','partial'])[1 + (g % 6)],
       'k' || g
  FROM generate_series(1, $BILL_ROWS) g,
       LATERAL (SELECT ('2024-01-01'::date + ((g % 730)) * interval '1 day')::date AS d) x;
SQL
echo "      ✅ 完成，耗时 $(( $(date +%s) - START )) 秒"

echo ""
echo "[6/6] 执行统计信息收集（ANALYZE）..."
psql -d "$DB" -q -c "ANALYZE room; ANALYZE tenant_person; ANALYZE bill;"
echo "      ✅ 完成"

echo ""
echo "=============================================="
echo " 数据规模确认"
echo "=============================================="
psql -d "$DB" -tAc "
select 'room          : ' || count(*) || ' 行' from room
union all select 'tenant_person : ' || count(*) || ' 行' from tenant_person
union all select 'bill          : ' || count(*) || ' 行' from bill;"
echo ""
psql -d "$DB" -tAc "
select '库总大小      : ' || pg_size_pretty(pg_database_size('$DB'))
union all select 'room 表+索引  : ' || pg_size_pretty(pg_total_relation_size('room'))
union all select 'person 表+索引: ' || pg_size_pretty(pg_total_relation_size('tenant_person'))
union all select 'bill 表+索引  : ' || pg_size_pretty(pg_total_relation_size('bill'));"