<?php
require_once(__DIR__ . "/../../lib/mydb.php");

try {
    $pdo = db_connect();
    
    $sql = "
    CREATE TABLE IF NOT EXISTS `sales_member` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `member_id` varchar(50) NOT NULL COMMENT '로그인 ID',
      `password` varchar(255) NOT NULL COMMENT '비밀번호',
      `name` varchar(100) NOT NULL COMMENT '성명',
      `phone` varchar(20) DEFAULT NULL COMMENT '전화번호',
      `email` varchar(100) DEFAULT NULL COMMENT '이메일',
      `parent_id` int(11) DEFAULT NULL COMMENT '상위 관리자 ID (sales_member.id)',
      `role` varchar(20) DEFAULT 'manager' COMMENT '역할 (operator, manager)',
      `remarks` text DEFAULT NULL COMMENT '비고',
      `is_active` tinyint(1) DEFAULT 1 COMMENT '활성화 여부',
      `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
      `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      UNIQUE KEY `idx_member_id` (`member_id`),
      KEY `idx_parent_id` (`parent_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='영업 담당자 및 관리자 정보 테이블';
    ";
    
    $pdo->exec($sql);

    // 2. sales_record 테이블 생성
    $sql_record = "
    CREATE TABLE IF NOT EXISTS `sales_record` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `member_id` int(11) NOT NULL COMMENT '영업 담당자 ID (sales_member.id)',
      `customer_name` varchar(100) NOT NULL COMMENT '고객사 성명/사명',
      `contract_date` date NOT NULL COMMENT '계약일 (가입비 완료일 기준)',
      `amount` decimal(15, 2) NOT NULL DEFAULT 0.00 COMMENT '가입비 금액',
      `status` varchar(20) DEFAULT 'completed' COMMENT '상태 (pending, completed, cancelled)',
      `remarks` text DEFAULT NULL COMMENT '비고',
      `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
      `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `idx_member_id` (`member_id`),
      KEY `idx_contract_date` (`contract_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='영업 실적(가입비) 기록 테이블';
    ";
    $pdo->exec($sql_record);
    
    // 기본 운영자 계정 생성
    $check = $pdo->prepare("SELECT id FROM sales_member WHERE member_id = 'admin'");
    $check->execute();
    if (!$check->fetch()) {
        $stmt = $pdo->prepare("INSERT INTO sales_member (member_id, password, name, role) VALUES ('admin', 'admin', '운영자', 'operator')");
        $stmt->execute();
    }

    // 기본 영업파트너 계정 생성
    $check = $pdo->prepare("SELECT id FROM sales_member WHERE member_id = 'sales'");
    $check->execute();
    if (!$check->fetch()) {
        $stmt = $pdo->prepare("INSERT INTO sales_member (member_id, password, name, role) VALUES ('sales', 'sales', '영업파트너', 'sales_admin')");
        $stmt->execute();
    }
    $sales_id = $pdo->lastInsertId() ?: 2;

    // 기본 매니저 계정 생성 (영업파트너 하위)
    $check = $pdo->prepare("SELECT id FROM sales_member WHERE member_id = 'manager'");
    $check->execute();
    if (!$check->fetch()) {
        $stmt = $pdo->prepare("INSERT INTO sales_member (member_id, password, name, role, parent_id) VALUES ('manager', 'manager', '매니저', 'manager', ?)");
        $stmt->execute([$sales_id]);
    }
    $manager_id = $pdo->lastInsertId() ?: 3;

    // 샘플 실적 데이터 (데이터가 없을 때만 입력)
    $check = $pdo->prepare("SELECT id FROM sales_record LIMIT 1");
    $check->execute();
    if (!$check->fetch()) {
        $stmt = $pdo->prepare("INSERT INTO sales_record (member_id, customer_name, contract_date, amount) VALUES (?, ?, ?, ?)");
        
        // 영업파트너(sales) 직접 실적
        $stmt->execute([$sales_id, '스타트업 A', '2024-12-01', 25000000]);
        $stmt->execute([$sales_id, '벤처기업 B', '2024-12-10', 30000000]);
        
        // 매니저(manager) 실적 (sales의 1차 하위)
        $stmt->execute([$manager_id, '매뉴팩처링 C', '2024-12-15', 20000000]);
        $stmt->execute([$manager_id, '서비스 D', '2024-12-20', 15000000]);
    }
    
    echo "Success: Database initialized with sales_member and sales_record tables.";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>