<?php
/**
 * setup.php — 베타 배포용 설치 마법사
 * Step1: DB설정 → Step2: 환경구축 → Step3: 계정생성 → Step4: 완료
 */
session_start();
$_base = '/' . trim(str_replace(str_replace('\\','/',$_SERVER['DOCUMENT_ROOT']??''), '', str_replace('\\','/',__DIR__)), '/') . '/';
if (file_exists(__DIR__.'/config/database.php') && !isset($_GET['step']) && !isset($_POST['step'])) {
    try { require_once __DIR__.'/config/database.php'; $t=(new Database())->getConnection(); $c=$t->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
        if(count($c)>10){
            // VIEW 재생성 모드
            if(isset($_GET['refresh_views'])){
                $pdo=$t; $sn=0;$er=0;$lg=[];
                function rvS($p,$s,$l,&$sn,&$er,&$lg){$sn++;try{$p->exec($s);$lg[]=['ok',$l];}catch(PDOException $e){$er++;$lg[]=['err',$l.' — '.$e->getMessage()];}}
                $vs=['v_sales_dup_warn','v_kpi_achievement','v_profit_by_person','v_profit_by_team','v_profit_total','v_incentive_calc','v_pipeline','v_performance_total','v_dashboard_summary','v_dashboard_ranking','v_dashboard_approval','v_dashboard_warnings','v_dashboard_monthly','v_underperform_flag','v_manager_warn'];
                foreach($vs as $vn){try{$pdo->exec("DROP VIEW IF EXISTS {$vn}");}catch(Exception $e){}}
                rvS($pdo,"CREATE OR REPLACE VIEW v_sales_dup_warn AS SELECT s.*,st.name AS staff_name,st.email,st.grade,st.team,IF(st.email='미기재','⚠','') AS email_warn,IF(cnt.dup_count>1,'⚠','') AS dup_warn FROM sales s JOIN staff st ON s.staff_id=st.staff_id LEFT JOIN(SELECT staff_id,company_name,episode,COUNT(*) AS dup_count FROM sales GROUP BY staff_id,company_name,episode) cnt ON s.staff_id=cnt.staff_id AND s.company_name COLLATE utf8mb4_unicode_ci=cnt.company_name COLLATE utf8mb4_unicode_ci AND s.episode COLLATE utf8mb4_unicode_ci=cnt.episode COLLATE utf8mb4_unicode_ci","v_sales_dup_warn",$sn,$er,$lg);
                rvS($pdo,"CREATE OR REPLACE VIEW v_profit_by_person AS SELECT s.staff_id,st.name,st.grade,st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit,CASE WHEN COUNT(*)>0 THEN ROUND(SUM(s.revenue)/COUNT(*)) ELSE 0 END AS avg_deal_price FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE s.stage IN('계약','촬영','방송완료') GROUP BY s.staff_id,st.name,st.grade,st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m'))","v_profit_by_person",$sn,$er,$lg);
                rvS($pdo,"CREATE OR REPLACE VIEW v_profit_by_team AS SELECT st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(DISTINCT s.staff_id) AS members,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE s.stage IN('계약','촬영','방송완료') GROUP BY st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m'))","v_profit_by_team",$sn,$er,$lg);
                rvS($pdo,"CREATE OR REPLACE VIEW v_profit_total AS SELECT COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit,CASE WHEN COUNT(*)>0 THEN ROUND(SUM(s.revenue)/COUNT(*)) ELSE 0 END AS avg_deal_price FROM sales s WHERE s.stage IN('계약','촬영','방송완료') GROUP BY COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m'))","v_profit_total",$sn,$er,$lg);
                rvS($pdo,"CREATE OR REPLACE VIEW v_incentive_calc AS SELECT p.staff_id,p.name,p.grade,p.team,p.sale_month,p.deal_count,p.total_revenue,p.total_net_profit,CASE WHEN p.deal_count>=COALESCE((SELECT CAST(REPLACE(rule_value,',','') AS UNSIGNED) FROM policy_rule WHERE rule_key='incentive_min_deals'),3) AND p.total_net_profit>0 AND COALESCE(ir.is_active,1)=1 THEN 'Y' ELSE 'N' END AS eligible,CASE WHEN COALESCE(ir.is_active,1)=1 AND p.total_net_profit>0 THEN ROUND(p.total_net_profit*ir.min_rate) ELSE 0 END AS incentive_min,CASE WHEN COALESCE(ir.is_active,1)=1 AND p.total_net_profit>0 THEN ROUND(p.total_net_profit*ir.max_rate) ELSE 0 END AS incentive_max,ir.min_rate,ir.max_rate,ir.basis AS rate_basis,COALESCE(ir.is_active,1) AS grade_incentive_active,COALESCE(NULLIF(kt.target_deals,0),(SELECT CAST(REPLACE(rule_value,',','') AS UNSIGNED) FROM policy_rule WHERE rule_key='deal_target_monthly'),0) AS target_deals,COALESCE(NULLIF(kt.target_amount,0),(SELECT CAST(REPLACE(rule_value,',','') AS UNSIGNED) FROM policy_rule WHERE rule_key='revenue_target_monthly'),0) AS target_amount FROM v_profit_by_person p LEFT JOIN incentive_rate ir ON p.grade COLLATE utf8mb4_unicode_ci=ir.grade COLLATE utf8mb4_unicode_ci LEFT JOIN kpi_target kt ON p.staff_id=kt.staff_id AND p.sale_month=kt.target_month","v_incentive_calc",$sn,$er,$lg);
                rvS($pdo,"CREATE OR REPLACE VIEW v_pipeline AS SELECT CASE WHEN s.stage IS NULL OR s.stage='' THEN '미진행' ELSE s.stage END AS stage,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(*) AS deal_count,COALESCE(SUM(s.revenue),0) AS total_revenue,COALESCE(SUM(s.cost),0) AS total_cost,COALESCE(SUM(s.net_profit),0) AS total_net_profit,ROUND(COUNT(*)*100/(SELECT COUNT(*) FROM sales),1) AS stage_pct FROM sales s GROUP BY stage,sale_month","v_pipeline",$sn,$er,$lg);
                echo '<!DOCTYPE html><html lang="ko"><head><meta charset="UTF-8"><style>body{font-family:sans-serif;background:#0d1117;color:#e0e0e0;display:flex;justify-content:center;align-items:center;min-height:100vh;margin:0;}.box{background:#161b22;padding:2rem;border-radius:12px;max-width:520px;border:1px solid #30363d;width:100%;}.ok{color:#3fb950;}.err{color:#f85149;}a{color:#58a6ff;text-decoration:none;font-weight:600;}</style></head><body><div class="box">';
                echo '<h2 style="margin-bottom:1rem;">🔄 VIEW 재생성 완료</h2>';
                echo '<p style="color:#8b949e;margin-bottom:1rem;">'.($sn-$er).'/'.$sn.' 성공'.($er?' · <span class="err">'.$er.' 오류</span>':'').'</p>';
                foreach($lg as $l) echo '<div style="font-size:0.85rem;padding:0.3rem 0;" class="'.($l[0]==='err'?'err':'ok').'">'.($l[0]==='ok'?'✅':'❌').' '.htmlspecialchars($l[1]).'</div>';
                echo '<p style="margin-top:1.5rem;text-align:center;"><a href="'.$_base.'">→ 로그인</a></p></div></body></html>';
                exit;
            }
            echo '<!DOCTYPE html><html lang="ko"><head><meta charset="UTF-8"><style>body{font-family:sans-serif;background:#0d1117;color:#e0e0e0;display:flex;justify-content:center;align-items:center;min-height:100vh;margin:0;}.box{background:#161b22;padding:3rem;border-radius:12px;text-align:center;max-width:420px;border:1px solid #30363d;}a{color:#58a6ff;text-decoration:none;font-weight:600;}</style></head><body><div class="box"><div style="font-size:3rem;margin-bottom:1rem;">✅</div><h2>이미 설치되었습니다</h2><p style="color:#8b949e;margin:1rem 0;">시스템이 정상 구동 중입니다.</p><a href="'.$_base.'">→ 로그인</a><p style="margin-top:2rem;font-size:0.8rem;color:#f85149;">⚠ setup.php를 삭제하세요.</p></div></body></html>';exit;}
    } catch(Exception $e) {}
}
$step=$_POST['step']??$_GET['step']??'1';
$stepNum=(int)preg_replace('/[^0-9]/','', $step);
?><!DOCTYPE html><html lang="ko"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title>설치 마법사</title>
<style>
*{box-sizing:border-box;margin:0;padding:0}body{font-family:'Noto Sans KR','Segoe UI',sans-serif;background:#0d1117;color:#c9d1d9;min-height:100vh;display:flex;justify-content:center;align-items:center}
.w{width:100%;max-width:580px;padding:2rem}.c{background:#161b22;border:1px solid #30363d;border-radius:12px;padding:2.5rem}
h2{font-size:1.5rem;font-weight:700;color:#e6edf3;margin-bottom:.3rem}.sub{font-size:.85rem;color:#8b949e;margin-bottom:2rem}
.dots{display:flex;gap:.5rem;margin-bottom:2rem}.dot{flex:1;height:4px;border-radius:2px;background:#21262d}.dot.a{background:#58a6ff}.dot.d{background:#3fb950}
.sl{display:flex;justify-content:space-between;margin-bottom:.5rem;font-size:.7rem;color:#8b949e}
.fg{margin-bottom:1.25rem}label.fl{display:block;font-size:.8rem;font-weight:600;color:#c9d1d9;margin-bottom:.4rem}
.fh{font-size:.7rem;color:#8b949e;margin-top:.2rem}
input.fi{width:100%;padding:.6rem .8rem;background:#0d1117;border:1px solid #30363d;border-radius:6px;color:#e6edf3;font-size:.9rem;outline:none}
input.fi:focus{border-color:#58a6ff}.fr{display:grid;grid-template-columns:1fr 1fr;gap:1rem}
.btn{padding:.65rem 1.5rem;border:none;border-radius:6px;font-size:.9rem;font-weight:600;cursor:pointer;display:inline-block;text-decoration:none;text-align:center}
.bp{background:#238636;color:#fff}.bp:hover{background:#2ea043}.bs{background:#21262d;color:#c9d1d9;border:1px solid #30363d}
.al{padding:.75rem 1rem;border-radius:6px;font-size:.85rem;margin-bottom:1.25rem}
.ae{background:rgba(248,81,73,.1);border:1px solid rgba(248,81,73,.3);color:#f85149}
.as{background:rgba(63,185,80,.1);border:1px solid rgba(63,185,80,.3);color:#3fb950}
.ai{background:rgba(88,166,255,.1);border:1px solid rgba(88,166,255,.3);color:#58a6ff}
.la{background:#0d1117;border:1px solid #21262d;border-radius:6px;padding:1rem;max-height:350px;overflow-y:auto;font-size:.75rem;font-family:Consolas,monospace;line-height:1.6}
.lok{color:#3fb950}.ler{color:#f85149}.lsk{color:#8b949e}
.ab{display:flex;justify-content:flex-end;gap:.75rem;margin-top:1.5rem}
.rt{width:100%;border-collapse:collapse;margin:1rem 0;font-size:.85rem}.rt th,.rt td{padding:.5rem .75rem;text-align:left;border-bottom:1px solid #21262d}.rt th{color:#8b949e;font-size:.75rem}
code{background:#21262d;padding:.15rem .4rem;border-radius:3px;font-size:.8rem}
.sec{padding:.75rem;border-radius:6px;margin-bottom:1.5rem}
</style></head><body><div class="w">
<div style="text-align:center;margin-bottom:1.5rem"><div style="font-size:2.5rem">🔧</div><div style="font-size:.75rem;color:#8b949e;margin-top:.3rem">AP MEDIA ONE · 설치 마법사</div></div>
<div class="sl"><span>DB 설정</span><span>환경 구축</span><span>계정 생성</span><span>완료</span></div>
<div class="dots"><div class="dot <?=$stepNum>=1?($stepNum>1?'d':'a'):''?>"></div><div class="dot <?=$stepNum>=2?($stepNum>2?'d':'a'):''?>"></div><div class="dot <?=$stepNum>=3?($stepNum>3?'d':'a'):''?>"></div><div class="dot <?=$stepNum>=4?'d':''?>"></div></div>
<div class="c">
<?php if($step==='1'): ?>
<h2>데이터베이스 설정</h2><div class="sub">MySQL/MariaDB 접속 정보를 입력하세요. DB가 없으면 자동 생성됩니다.</div>
<?php if(!empty($_GET['err'])):?><div class="al ae"><?=htmlspecialchars($_GET['err'])?></div><?php endif;?>
<form method="POST"><input type="hidden" name="step" value="1_process">
<div class="fg"><label class="fl">DB 호스트</label><input type="text" name="db_host" class="fi" value="localhost" required><div class="fh">일반적으로 localhost 또는 127.0.0.1</div></div>
<div class="fg"><label class="fl">DB 포트</label><input type="text" name="db_port" class="fi" value="3306" required></div>
<div class="fg"><label class="fl">데이터베이스 이름</label><input type="text" name="db_name" class="fi" value="strategic_ops" required><div class="fh">존재하지 않으면 새로 생성됩니다</div></div>
<div class="fr"><div class="fg"><label class="fl">DB 사용자 아이디</label><input type="text" name="db_user" class="fi" value="root" required></div>
<div class="fg"><label class="fl">DB 비밀번호</label><input type="password" name="db_pass" class="fi" placeholder="비밀번호"></div></div>
<div class="ab"><button type="submit" class="btn bp">다음 단계 →</button></div></form>

<?php elseif($step==='1_process'):
$h=trim($_POST['db_host']??'localhost');$po=trim($_POST['db_port']??'3306');$dn=trim($_POST['db_name']??'strategic_ops');$du=trim($_POST['db_user']??'root');$dp=$_POST['db_pass']??'';
try{$pdo=new PDO("mysql:host={$h};port={$po};charset=utf8mb4",$du,$dp);$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
$pdo->exec("CREATE DATABASE IF NOT EXISTS `{$dn}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");$pdo->exec("USE `{$dn}`");
$cd=__DIR__.'/config';if(!is_dir($cd))mkdir($cd,0755,true);
$cf="<?php\nclass Database {\n    private \$host='{$h}'; private \$port='{$po}'; private \$db_name='{$dn}'; private \$username='{$du}'; private \$password='".addslashes($dp)."';\n    public \$conn;\n    public function getConnection() {\n        try { \$this->conn=new PDO(\"mysql:host={\$this->host};port={\$this->port};dbname={\$this->db_name};charset=utf8mb4\",\$this->username,\$this->password);\n            \$this->conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);\n            \$this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);\n            \$this->conn->exec(\"SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci\");\n        } catch(PDOException \$e){die(\"DB 연결 실패: \".\$e->getMessage());}\n        return \$this->conn;\n    }\n}\n";
file_put_contents($cd.'/database.php',$cf);$_SESSION['setup_db']=compact('h','po','dn','du','dp');header("Location: setup.php?step=2");exit;
}catch(PDOException $e){header("Location: setup.php?step=1&err=".urlencode('DB 연결 실패: '.$e->getMessage()));exit;}

elseif($step==='2'):$di=$_SESSION['setup_db']??null;if(!$di){header("Location: setup.php?step=1");exit;}?>
<h2>환경 자동 구축</h2><div class="sub">테이블, 트리거, 뷰를 자동으로 생성합니다.</div>
<div class="al ai">📦 데이터베이스: <strong><?=htmlspecialchars($di['dn'])?></strong> @ <?=htmlspecialchars($di['h'])?>:<?=htmlspecialchars($di['po'])?></div>
<form method="POST"><input type="hidden" name="step" value="2_process">
<div class="ab"><a href="setup.php?step=1" class="btn bs">← 이전</a><button type="submit" class="btn bp">🔨 구축 시작</button></div></form>

<?php elseif($step==='2_process'):
$di=$_SESSION['setup_db']??null;if(!$di){header("Location: setup.php?step=1");exit;}
extract($di);$pdo=new PDO("mysql:host={$h};port={$po};dbname={$dn};charset=utf8mb4",$du,$dp);$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
$pdo->exec("ALTER DATABASE `{$dn}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
$sn=0;$er=0;$lg=[];
function rS($p,$s,$l,&$sn,&$er,&$lg){$sn++;try{$p->exec($s);$lg[]=['ok',"Step {$sn}: {$l}"];}catch(PDOException $e){if(strpos($e->getMessage(),'already exists')!==false||strpos($e->getMessage(),'Duplicate')!==false){$lg[]=['skip',"Step {$sn}: {$l} (이미 존재)"];}else{$er++;$lg[]=['err',"Step {$sn}: {$l} — ".$e->getMessage()];}}}

// ── 테이블 생성 ──
rS($pdo,"CREATE TABLE IF NOT EXISTS staff (staff_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(100) DEFAULT '미기재',phone VARCHAR(20) DEFAULT '',grade VARCHAR(30) NOT NULL DEFAULT '사원',team VARCHAR(50) NOT NULL,role ENUM('직원','팀장','관리자','대표','시스템관리자') NOT NULL DEFAULT '직원',hire_date DATE,is_active TINYINT(1) DEFAULT 1,memo VARCHAR(200) DEFAULT '',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_team(team)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","staff",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS user_account (user_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,login_id VARCHAR(50) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,user_role ENUM('staff','team_leader','manager','ceo','sysadmin') NOT NULL DEFAULT 'staff',is_active TINYINT(1) DEFAULT 0,approval_status ENUM('대기','승인','반려') NOT NULL DEFAULT '대기',approved_by INT DEFAULT NULL,approved_at DATETIME DEFAULT NULL,reject_reason VARCHAR(300) DEFAULT '',must_change_pw TINYINT(1) DEFAULT 0,pw_changed_at DATETIME DEFAULT NULL,temp_pw_issued_at DATETIME DEFAULT NULL,temp_pw_issued_by INT DEFAULT NULL,last_login DATETIME DEFAULT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE,INDEX idx_approval(approval_status),INDEX idx_role(user_role)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","user_account",$sn,$er,$lg);
// 이름 중복 허용 — 기존 DB에서 UNIQUE KEY 제거
try{$pdo->exec("ALTER TABLE staff DROP INDEX uk_name");}catch(Exception $e){}
rS($pdo,"CREATE TABLE IF NOT EXISTS team_master (team_id INT AUTO_INCREMENT PRIMARY KEY,team_name VARCHAR(50) NOT NULL UNIQUE,description VARCHAR(200) DEFAULT '',leader_staff_id INT DEFAULT NULL,is_active TINYINT(1) DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","team_master",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS grade_master (grade_id INT AUTO_INCREMENT PRIMARY KEY,grade_name VARCHAR(30) NOT NULL UNIQUE,sort_order INT DEFAULT 0,is_active TINYINT(1) DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","grade_master",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS policy_rule (rule_id INT AUTO_INCREMENT PRIMARY KEY,rule_key VARCHAR(100) NOT NULL UNIQUE,rule_value VARCHAR(200) NOT NULL,description VARCHAR(300) DEFAULT '',updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","policy_rule",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS incentive_rate (rate_id INT AUTO_INCREMENT PRIMARY KEY,grade VARCHAR(30) NOT NULL,min_rate DECIMAL(5,4) NOT NULL,max_rate DECIMAL(5,4) NOT NULL,basis VARCHAR(100) NOT NULL,memo VARCHAR(200) DEFAULT '',is_active TINYINT(1) DEFAULT 1,UNIQUE KEY uk_grade(grade)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","incentive_rate",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS sales (sale_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,company_name VARCHAR(100) NOT NULL,program_name VARCHAR(100) NOT NULL,episode VARCHAR(20) DEFAULT '',contract_date DATE,filming_date DATE,broadcast_date DATE,revenue BIGINT NOT NULL DEFAULT 0,cost BIGINT NOT NULL DEFAULT 0,net_profit BIGINT NOT NULL DEFAULT 0,stage ENUM('리드','제안','계약','촬영','방송완료') NOT NULL DEFAULT '리드',confirm_type ENUM('확정','예상') NOT NULL DEFAULT '예상',memo VARCHAR(500) DEFAULT '',sale_month CHAR(7) DEFAULT NULL,handler_name VARCHAR(50) DEFAULT '' COMMENT '처리자',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_dup_check(staff_id,company_name,episode),INDEX idx_stage(stage),INDEX idx_month(contract_date),FOREIGN KEY(staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","sales",$sn,$er,$lg);
// sales 트리거
try{$pdo->exec("DROP TRIGGER IF EXISTS trg_sales_before_insert");$pdo->exec("DROP TRIGGER IF EXISTS trg_sales_before_update");$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);
$pdo->exec("CREATE TRIGGER trg_sales_before_insert BEFORE INSERT ON sales FOR EACH ROW BEGIN SET NEW.net_profit=NEW.revenue-NEW.cost;IF NEW.contract_date IS NOT NULL THEN SET NEW.sale_month=DATE_FORMAT(NEW.contract_date,'%Y-%m');END IF;END");
$pdo->exec("CREATE TRIGGER trg_sales_before_update BEFORE UPDATE ON sales FOR EACH ROW BEGIN SET NEW.net_profit=NEW.revenue-NEW.cost;IF NEW.contract_date IS NOT NULL THEN SET NEW.sale_month=DATE_FORMAT(NEW.contract_date,'%Y-%m');END IF;END");
$sn++;$lg[]=['ok',"Step {$sn}: sales 트리거"];}catch(PDOException $e){$sn++;$lg[]=['skip',"Step {$sn}: sales 트리거 (스킵)"];}
rS($pdo,"CREATE TABLE IF NOT EXISTS kpi_target (kpi_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,target_month CHAR(7) NOT NULL,target_deals INT NOT NULL DEFAULT 0,target_amount BIGINT NOT NULL DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uk_staff_month(staff_id,target_month),FOREIGN KEY(staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","kpi_target",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS kpi_team_target (id INT AUTO_INCREMENT PRIMARY KEY,team VARCHAR(50) NOT NULL,target_month CHAR(7) NOT NULL,target_deals INT NOT NULL DEFAULT 0,target_amount BIGINT NOT NULL DEFAULT 0,UNIQUE KEY uk_team_month(team,target_month)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","kpi_team_target",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS performance_score (score_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,eval_month CHAR(7) NOT NULL,quant_revenue_rate DECIMAL(5,1) DEFAULT 0,quant_deal_rate DECIMAL(5,1) DEFAULT 0,qual_attitude TINYINT DEFAULT 0,qual_teamwork TINYINT DEFAULT 0,qual_contrib TINYINT DEFAULT 0,memo VARCHAR(300) DEFAULT '',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uk_staff_month(staff_id,eval_month),FOREIGN KEY(staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","performance_score",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS approval_flow (approval_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,approval_month CHAR(7) NOT NULL,calc_min BIGINT DEFAULT 0,calc_max BIGINT DEFAULT 0,reviewer_id INT DEFAULT NULL,review_status ENUM('대기','보류','확인') NOT NULL DEFAULT '대기',review_date DATETIME DEFAULT NULL,review_memo VARCHAR(500) DEFAULT '',approve_status ENUM('대기','보류','승인') NOT NULL DEFAULT '대기',approve_date DATETIME DEFAULT NULL,approve_memo VARCHAR(500) DEFAULT '',final_amount BIGINT DEFAULT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uk_staff_month(staff_id,approval_month),FOREIGN KEY(staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","approval_flow",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS menu_permission (perm_id INT AUTO_INCREMENT PRIMARY KEY,user_role ENUM('staff','team_leader','manager','ceo','sysadmin') NOT NULL,menu_key VARCHAR(50) NOT NULL,menu_label VARCHAR(50) NOT NULL,menu_icon VARCHAR(10) DEFAULT '📄',menu_group VARCHAR(30) NOT NULL,menu_url VARCHAR(100) NOT NULL,sort_order INT DEFAULT 0,is_visible TINYINT(1) DEFAULT 1,UNIQUE KEY uk_role_menu(user_role,menu_key),INDEX idx_role_visible(user_role,is_visible,sort_order)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","menu_permission",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS team_leader_log (log_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,previous_role VARCHAR(20) NOT NULL,new_role VARCHAR(20) NOT NULL,assigned_by INT NOT NULL,assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,reason VARCHAR(300) DEFAULT '',FOREIGN KEY(staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE,INDEX idx_staff(staff_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","team_leader_log",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS audit_log (log_id BIGINT AUTO_INCREMENT PRIMARY KEY,table_name VARCHAR(50) NOT NULL,record_id INT NOT NULL,action VARCHAR(30) NOT NULL,changed_by INT DEFAULT NULL,old_values JSON DEFAULT NULL,new_values JSON DEFAULT NULL,ip_address VARCHAR(45) DEFAULT '',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_table_record(table_name,record_id),INDEX idx_created(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","audit_log",$sn,$er,$lg);
try { $pdo->exec("ALTER TABLE audit_log MODIFY COLUMN action VARCHAR(30) NOT NULL"); } catch(Exception $e) {}
// ── 보조 테이블 ──
rS($pdo,"CREATE TABLE IF NOT EXISTS user_theme (theme_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL UNIQUE,accent_color VARCHAR(20) DEFAULT '#E53935',icon_color VARCHAR(20) DEFAULT '#E53935',glass_bg VARCHAR(50) DEFAULT 'rgba(12,12,16,0.75)',card_bg_color VARCHAR(20) DEFAULT '#0C0C10',text_color VARCHAR(20) DEFAULT '#EAEAEA',sidebar_bg VARCHAR(50) DEFAULT 'rgba(10,10,14,0.82)',form_line_color VARCHAR(20) DEFAULT '#333338',form_placeholder VARCHAR(20) DEFAULT '#555560',video_opacity DECIMAL(3,2) DEFAULT 0.65,video_url VARCHAR(500) DEFAULT '',updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","user_theme",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS user_images (img_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,filename VARCHAR(255),sort_order INT DEFAULT 0,uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_user(user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","user_images",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS notice (notice_id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200) NOT NULL,content TEXT,is_pinned TINYINT(1) DEFAULT 0,author_id INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","notice",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS team_notice (notice_id INT AUTO_INCREMENT PRIMARY KEY,team VARCHAR(50) NOT NULL,title VARCHAR(200) NOT NULL,content TEXT,is_pinned TINYINT(1) DEFAULT 0,author_id INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","team_notice",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS notifications (noti_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,message VARCHAR(300),link VARCHAR(200) DEFAULT '',is_read TINYINT(1) DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_user_read(user_id,is_read)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","notifications",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS evaluation (eval_id INT AUTO_INCREMENT PRIMARY KEY,target_staff_id INT NOT NULL,eval_period VARCHAR(10) NOT NULL,evaluator_id INT NOT NULL,evaluator_role VARCHAR(30) NOT NULL,eval_step TINYINT DEFAULT 1 COMMENT '1=팀장,2=관리자,3=대표',score_quant DECIMAL(5,1) DEFAULT 0,score_attitude DECIMAL(5,1) DEFAULT 0,score_collab DECIMAL(5,1) DEFAULT 0,score_qual DECIMAL(5,1) DEFAULT 0,total_score DECIMAL(5,1) DEFAULT 0,eval_status ENUM('작성중','팀장완료','관리자완료','확정') DEFAULT '작성중',comment TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uk_eval(target_staff_id,eval_period,eval_step)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","evaluation",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS eval_notification (noti_id INT AUTO_INCREMENT PRIMARY KEY,target_evaluator_id INT NOT NULL,target_staff_id INT NOT NULL,eval_period VARCHAR(10) NOT NULL,eval_step TINYINT NOT NULL,message VARCHAR(200) DEFAULT '',is_read TINYINT DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uk_noti(target_evaluator_id,target_staff_id,eval_period,eval_step)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","eval_notification",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS promotion (promo_id INT AUTO_INCREMENT PRIMARY KEY,promo_month VARCHAR(7),title VARCHAR(200),description TEXT,is_active TINYINT(1) DEFAULT 1,created_by INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","promotion",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS team_leader_candidate (cand_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,nominated_by INT,reason VARCHAR(300) DEFAULT '',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uk_staff(staff_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","team_leader_candidate",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS incentive_review (review_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,sale_month VARCHAR(7) NOT NULL,review_status ENUM('대기','검토중','승인요청','대표승인','반려','확정') DEFAULT '대기',review_memo VARCHAR(300) DEFAULT '',reviewed_by INT DEFAULT NULL,ceo_approved_by INT DEFAULT NULL,ceo_memo VARCHAR(300) DEFAULT '',updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uk_staff_month(staff_id,sale_month)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","incentive_review",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS incentive_review_log (log_id INT AUTO_INCREMENT PRIMARY KEY,staff_id INT NOT NULL,sale_month VARCHAR(7) NOT NULL,old_status VARCHAR(20),new_status VARCHAR(20),memo VARCHAR(300) DEFAULT '',changed_by INT NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","incentive_review_log",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS edit_request (req_id INT AUTO_INCREMENT PRIMARY KEY,sale_id INT NOT NULL,requested_by INT NOT NULL,edit_data TEXT,reason VARCHAR(200) DEFAULT '',status ENUM('대기','승인','거절') DEFAULT '대기',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","edit_request",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS delete_request (req_id INT AUTO_INCREMENT PRIMARY KEY,sale_id INT NOT NULL,requested_by INT NOT NULL,reason VARCHAR(200) DEFAULT '',status ENUM('대기','승인','거절') DEFAULT '대기',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","delete_request",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS board_permission (bp_id INT AUTO_INCREMENT PRIMARY KEY,board_key VARCHAR(30) NOT NULL,role_key VARCHAR(20) NOT NULL,can_view TINYINT(1) DEFAULT 1,can_create TINYINT(1) DEFAULT 0,can_edit TINYINT(1) DEFAULT 0,can_delete TINYINT(1) DEFAULT 0,can_copy TINYINT(1) DEFAULT 0,can_comment TINYINT(1) DEFAULT 0,can_comment_edit TINYINT(1) DEFAULT 0,can_comment_delete TINYINT(1) DEFAULT 0,can_reply TINYINT(1) DEFAULT 0,can_reply_edit TINYINT(1) DEFAULT 0,can_reply_delete TINYINT(1) DEFAULT 0,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uk_board_role(board_key,role_key)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","board_permission",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS menu_role_permission (mrp_id INT AUTO_INCREMENT PRIMARY KEY,menu_key VARCHAR(30) NOT NULL,role_key VARCHAR(20) NOT NULL,view_scope ENUM('none','own','team','all') DEFAULT 'own',can_create TINYINT(1) DEFAULT 0,can_edit TINYINT(1) DEFAULT 0,can_delete TINYINT(1) DEFAULT 0,need_ceo_approval TINYINT(1) DEFAULT 0,need_sysadmin_approval TINYINT(1) DEFAULT 0,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,UNIQUE KEY uk_menu_role(menu_key,role_key)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","menu_role_permission",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS team_menu_group_assign (assign_id INT AUTO_INCREMENT PRIMARY KEY,menu_group VARCHAR(50) NOT NULL,assigned_team VARCHAR(50) NOT NULL,UNIQUE KEY uk_group_team(menu_group,assigned_team)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","team_menu_group_assign",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS approval_review_log (log_id INT AUTO_INCREMENT PRIMARY KEY,approval_id INT NOT NULL,staff_id INT NOT NULL,sale_month VARCHAR(7) DEFAULT '',staff_name VARCHAR(50) DEFAULT '',staff_grade VARCHAR(30) DEFAULT '',action_type VARCHAR(20) NOT NULL,old_status VARCHAR(20) DEFAULT '',new_status VARCHAR(20) NOT NULL,memo VARCHAR(300) DEFAULT '',changed_by INT NOT NULL,changed_by_name VARCHAR(50) DEFAULT '',created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","approval_review_log",$sn,$er,$lg);
// 마케팅
rS($pdo,"CREATE TABLE IF NOT EXISTS mkt_campaign (campaign_id INT AUTO_INCREMENT PRIMARY KEY,campaign_name VARCHAR(100) NOT NULL,campaign_type ENUM('디지털','오프라인','SNS','브랜드','프로모션','기타') DEFAULT '디지털',channel VARCHAR(50),start_date DATE,end_date DATE,budget DECIMAL(15,0) DEFAULT 0,status ENUM('기획','진행','완료','중단') DEFAULT '기획',staff_id INT,description TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","mkt_campaign",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS mkt_performance (perf_id INT AUTO_INCREMENT PRIMARY KEY,campaign_id INT NOT NULL,perf_month VARCHAR(7) NOT NULL,impressions BIGINT DEFAULT 0,clicks INT DEFAULT 0,conversions INT DEFAULT 0,revenue DECIMAL(15,0) DEFAULT 0,cost DECIMAL(15,0) DEFAULT 0,leads INT DEFAULT 0,staff_id INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","mkt_performance",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS mkt_roi (roi_id INT AUTO_INCREMENT PRIMARY KEY,campaign_id INT,calc_month VARCHAR(7),total_cost DECIMAL(15,0) DEFAULT 0,total_revenue DECIMAL(15,0) DEFAULT 0,roi_percent DECIMAL(8,2) DEFAULT 0,ctr DECIMAL(8,4) DEFAULT 0,cvr DECIMAL(8,4) DEFAULT 0,cpa DECIMAL(15,0) DEFAULT 0,roas DECIMAL(8,2) DEFAULT 0,calc_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uk_camp_month(campaign_id,calc_month)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","mkt_roi",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS mkt_brand_strategy (strategy_id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200) NOT NULL,category ENUM('브랜딩','리포지셔닝','캠페인전략','콘텐츠전략','채널전략') DEFAULT '브랜딩',quarter VARCHAR(7),target_audience VARCHAR(200),core_message TEXT,kpi_target TEXT,status ENUM('초안','검토','확정','실행','완료') DEFAULT '초안',staff_id INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","mkt_brand_strategy",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS mkt_content_pipeline (content_id INT AUTO_INCREMENT PRIMARY KEY,campaign_id INT,content_type ENUM('블로그','SNS','영상','배너','이메일','보도자료','기타') DEFAULT 'SNS',title VARCHAR(200) NOT NULL,channel VARCHAR(50),scheduled_date DATE,publish_date DATE,status ENUM('기획','제작','검토','발행','완료') DEFAULT '기획',staff_id INT,notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","mkt_content_pipeline",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS mkt_alert_log (alert_id INT AUTO_INCREMENT PRIMARY KEY,alert_type ENUM('오류','경고','정보','성공') DEFAULT '정보',module VARCHAR(50),message TEXT,resolved TINYINT(1) DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME,resolved_by INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","mkt_alert_log",$sn,$er,$lg);

// ── 기업 DB (영업 보고서 누적) ──
rS($pdo,"CREATE TABLE IF NOT EXISTS sales_report (report_id INT AUTO_INCREMENT PRIMARY KEY,sale_id INT DEFAULT NULL,staff_id INT NOT NULL,company_name VARCHAR(100) NOT NULL,ceo_name VARCHAR(50) DEFAULT '',contact_name VARCHAR(50) DEFAULT '' COMMENT '담당자명',contact_phone VARCHAR(30) DEFAULT '' COMMENT '담당자번호',industry VARCHAR(100) DEFAULT '' COMMENT '업종',field VARCHAR(100) DEFAULT '' COMMENT '분야',phone VARCHAR(30) DEFAULT '' COMMENT '대표전화',mobile VARCHAR(30) DEFAULT '' COMMENT '휴대폰',email VARCHAR(100) DEFAULT '',website VARCHAR(200) DEFAULT '' COMMENT '홈페이지',contact_route VARCHAR(200) DEFAULT '' COMMENT '컨택경로',call_notes TEXT DEFAULT NULL COMMENT '통화내용',check_date DATE DEFAULT NULL COMMENT '진행확인일',handler_name VARCHAR(50) DEFAULT '' COMMENT '처리자',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_sale(sale_id),INDEX idx_staff(staff_id),INDEX idx_company(company_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","sales_report",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS company_db (company_id INT AUTO_INCREMENT PRIMARY KEY,company_name VARCHAR(100) NOT NULL,ceo_name VARCHAR(50) DEFAULT '',contact_name VARCHAR(50) DEFAULT '' COMMENT '담당자명',contact_phone VARCHAR(30) DEFAULT '' COMMENT '담당자번호',industry VARCHAR(100) DEFAULT '',field VARCHAR(100) DEFAULT '',phone VARCHAR(30) DEFAULT '',mobile VARCHAR(30) DEFAULT '',email VARCHAR(100) DEFAULT '',website VARCHAR(200) DEFAULT '',contact_route VARCHAR(200) DEFAULT '',registered_by INT DEFAULT NULL COMMENT 'staff_id',source_type ENUM('영업보고서','엑셀등록','수동등록') DEFAULT '수동등록',source_report_id INT DEFAULT NULL COMMENT 'sales_report.report_id',is_active TINYINT(1) DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_company(company_name),INDEX idx_active(is_active)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","company_db",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS company_delete_requests (req_id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,requested_by INT NOT NULL COMMENT 'staff_id',reason VARCHAR(500) NOT NULL,status ENUM('대기','승인','거절') DEFAULT '대기',reviewed_by INT DEFAULT NULL,review_memo VARCHAR(300) DEFAULT '',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME DEFAULT NULL,INDEX idx_status(status),INDEX idx_company(company_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","company_delete_requests",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS report_edit_request (req_id INT AUTO_INCREMENT PRIMARY KEY,report_id INT NOT NULL,requested_by INT NOT NULL,edit_data TEXT,reason VARCHAR(500) DEFAULT '',status ENUM('대기','승인','거절') DEFAULT '대기',reviewed_by INT DEFAULT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME DEFAULT NULL,INDEX idx_status(status),INDEX idx_report(report_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","report_edit_request",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS report_delete_request (req_id INT AUTO_INCREMENT PRIMARY KEY,report_id INT NOT NULL,requested_by INT NOT NULL,reason VARCHAR(500) DEFAULT '',status ENUM('대기','승인','거절') DEFAULT '대기',reviewed_by INT DEFAULT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,resolved_at DATETIME DEFAULT NULL,INDEX idx_status(status),INDEX idx_report(report_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","report_delete_request",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS sale_comments (comment_id INT AUTO_INCREMENT PRIMARY KEY,sale_id INT NOT NULL,staff_id INT NOT NULL,content TEXT NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX idx_sale(sale_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","sale_comments",$sn,$er,$lg);
rS($pdo,"CREATE TABLE IF NOT EXISTS report_call_files (file_id INT AUTO_INCREMENT PRIMARY KEY,report_id INT NOT NULL,staff_id INT NOT NULL,file_path VARCHAR(300) NOT NULL,original_name VARCHAR(200) DEFAULT '',file_size INT DEFAULT 0,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,INDEX idx_report(report_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci","report_call_files",$sn,$er,$lg);

// ── 모든 테이블 collation 통일 (XAMPP 기본 general_ci → unicode_ci 충돌 방지) ──
try {
    $tbls = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tbls as $tbl) {
        try { $pdo->exec("ALTER TABLE `{$tbl}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"); } catch(Exception $e) {}
    }
} catch(Exception $e) {}

// ── VIEW 생성 ──
$vs=['v_sales_dup_warn','v_kpi_achievement','v_profit_by_person','v_profit_by_team','v_profit_total','v_incentive_calc','v_pipeline','v_performance_total','v_dashboard_summary','v_dashboard_ranking','v_dashboard_approval','v_dashboard_warnings','v_dashboard_monthly','v_underperform_flag','v_manager_warn'];
foreach($vs as $v){try{$pdo->exec("DROP VIEW IF EXISTS `{$v}`");}catch(Exception $e){}}
rS($pdo,"CREATE OR REPLACE VIEW v_sales_dup_warn AS SELECT s.*,st.name AS staff_name,st.email,st.grade,st.team,IF(st.email='미기재','⚠ 이메일 등록 필요','') AS email_warn,IF(cnt.dup_count>1,'⚠ 중복','') AS dup_warn FROM sales s JOIN staff st ON s.staff_id=st.staff_id LEFT JOIN(SELECT staff_id,company_name,episode,COUNT(*) AS dup_count FROM sales GROUP BY staff_id,company_name,episode) cnt ON s.staff_id=cnt.staff_id AND s.company_name COLLATE utf8mb4_unicode_ci=cnt.company_name COLLATE utf8mb4_unicode_ci AND s.episode COLLATE utf8mb4_unicode_ci=cnt.episode COLLATE utf8mb4_unicode_ci","VIEW v_sales_dup_warn",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_profit_by_person AS SELECT s.staff_id,st.name,st.grade,st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit,CASE WHEN COUNT(*)>0 THEN ROUND(SUM(s.revenue)/COUNT(*)) ELSE 0 END AS avg_deal_price FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE s.stage IN('계약','촬영','방송완료') GROUP BY s.staff_id,st.name,st.grade,st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m'))","VIEW v_profit_by_person",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_profit_by_team AS SELECT st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(DISTINCT s.staff_id) AS members,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE s.stage IN('계약','촬영','방송완료') GROUP BY st.team,COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m'))","VIEW v_profit_by_team",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_profit_total AS SELECT COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m')) AS sale_month,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit,CASE WHEN COUNT(*)>0 THEN ROUND(SUM(s.revenue)/COUNT(*)) ELSE 0 END AS avg_deal_price FROM sales s WHERE s.stage IN('계약','촬영','방송완료') GROUP BY COALESCE(s.sale_month,DATE_FORMAT(s.contract_date,'%Y-%m'),DATE_FORMAT(s.created_at,'%Y-%m'))","VIEW v_profit_total",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_kpi_achievement AS SELECT k.staff_id,st.name,st.grade,st.team,k.target_month,k.target_deals,k.target_amount,COALESCE(a.actual_deals,0) AS actual_deals,COALESCE(a.actual_amount,0) AS actual_amount FROM kpi_target k JOIN staff st ON k.staff_id=st.staff_id LEFT JOIN(SELECT staff_id,sale_month,COUNT(*) AS actual_deals,SUM(revenue) AS actual_amount FROM sales WHERE stage IN('계약','촬영','방송완료') GROUP BY staff_id,sale_month) a ON k.staff_id=a.staff_id AND k.target_month=a.sale_month","VIEW v_kpi_achievement",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_incentive_calc AS SELECT p.staff_id,p.name,p.grade,p.team,p.sale_month,p.deal_count,p.total_revenue,p.total_net_profit,CASE WHEN p.deal_count>=COALESCE((SELECT CAST(rule_value AS UNSIGNED) FROM policy_rule WHERE rule_key='incentive_min_deals'),3) AND p.total_net_profit>0 AND COALESCE(ir.is_active,1)=1 THEN 'Y' ELSE 'N' END AS eligible,CASE WHEN COALESCE(ir.is_active,1)=1 AND p.total_net_profit>0 THEN ROUND(p.total_net_profit*ir.min_rate) ELSE 0 END AS incentive_min,CASE WHEN COALESCE(ir.is_active,1)=1 AND p.total_net_profit>0 THEN ROUND(p.total_net_profit*ir.max_rate) ELSE 0 END AS incentive_max,ir.min_rate,ir.max_rate,ir.basis AS rate_basis,COALESCE(ir.is_active,1) AS grade_incentive_active,COALESCE(NULLIF(kt.target_deals,0),(SELECT CAST(rule_value AS UNSIGNED) FROM policy_rule WHERE rule_key='deal_target_monthly'),0) AS target_deals,COALESCE(NULLIF(kt.target_amount,0),(SELECT CAST(rule_value AS UNSIGNED) FROM policy_rule WHERE rule_key='revenue_target_monthly'),0) AS target_amount FROM v_profit_by_person p LEFT JOIN incentive_rate ir ON p.grade COLLATE utf8mb4_unicode_ci=ir.grade COLLATE utf8mb4_unicode_ci LEFT JOIN kpi_target kt ON p.staff_id=kt.staff_id AND p.sale_month=kt.target_month","VIEW v_incentive_calc",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_pipeline AS SELECT s.stage,s.sale_month,COUNT(*) AS deal_count,SUM(s.revenue) AS total_revenue,SUM(s.cost) AS total_cost,SUM(s.net_profit) AS total_net_profit FROM sales s GROUP BY s.stage,s.sale_month","VIEW v_pipeline",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_performance_total AS SELECT ps.score_id,ps.staff_id,st.name,st.grade,st.team,ps.eval_month,ROUND(((ps.quant_revenue_rate+ps.quant_deal_rate)/2)*0.6,1) AS quant_score,ROUND((ps.qual_attitude+ps.qual_teamwork+ps.qual_contrib)/30*40,1) AS qual_score,ROUND(((ps.quant_revenue_rate+ps.quant_deal_rate)/2)*0.6+(ps.qual_attitude+ps.qual_teamwork+ps.qual_contrib)/30*40,1) AS total_score FROM performance_score ps JOIN staff st ON ps.staff_id=st.staff_id","VIEW v_performance_total",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_dashboard_summary AS SELECT SUM(revenue) AS total_revenue,SUM(net_profit) AS total_net_profit,COUNT(*) AS total_deals FROM sales WHERE stage IN('계약','촬영','방송완료')","VIEW v_dashboard_summary",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_dashboard_ranking AS SELECT st.name,st.grade,st.team,SUM(s.revenue) AS total_revenue,SUM(s.net_profit) AS total_net_profit,COUNT(*) AS total_deals FROM sales s JOIN staff st ON s.staff_id=st.staff_id WHERE s.stage IN('계약','촬영','방송완료') GROUP BY st.staff_id,st.name,st.grade,st.team","VIEW v_dashboard_ranking",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_dashboard_approval AS SELECT SUM(CASE WHEN approve_status='대기' THEN 1 ELSE 0 END) AS pending_count,SUM(CASE WHEN approve_status='승인' THEN 1 ELSE 0 END) AS approved_count FROM approval_flow","VIEW v_dashboard_approval",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_dashboard_warnings AS SELECT (SELECT COUNT(*) FROM staff WHERE email='미기재' AND is_active=1) AS email_missing_count,(SELECT COUNT(*) FROM(SELECT staff_id,company_name,episode FROM sales GROUP BY staff_id,company_name,episode HAVING COUNT(*)>1) t) AS duplicate_count","VIEW v_dashboard_warnings",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_dashboard_monthly AS SELECT s.sale_month,COUNT(CASE WHEN s.stage IN('계약','촬영','방송완료') THEN 1 END) AS deal_count,SUM(CASE WHEN s.stage IN('계약','촬영','방송완료') THEN s.revenue ELSE 0 END) AS total_revenue,SUM(CASE WHEN s.stage IN('계약','촬영','방송완료') THEN s.net_profit ELSE 0 END) AS total_net_profit,SUM(CASE WHEN s.stage='리드' THEN 1 ELSE 0 END) AS stage_lead,SUM(CASE WHEN s.stage='제안' THEN 1 ELSE 0 END) AS stage_proposal,SUM(CASE WHEN s.stage='계약' THEN 1 ELSE 0 END) AS stage_contract,SUM(CASE WHEN s.stage='촬영' THEN 1 ELSE 0 END) AS stage_filming,SUM(CASE WHEN s.stage='방송완료' THEN 1 ELSE 0 END) AS stage_complete FROM sales s GROUP BY s.sale_month ORDER BY s.sale_month","VIEW v_dashboard_monthly",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_underperform_flag AS SELECT st.staff_id,st.name,st.grade,st.team,COUNT(DISTINCT p.sale_month) AS low_months FROM staff st LEFT JOIN v_profit_by_person p ON st.staff_id=p.staff_id AND p.deal_count<2 WHERE st.is_active=1 GROUP BY st.staff_id,st.name,st.grade,st.team HAVING low_months>=2","VIEW v_underperform_flag",$sn,$er,$lg);
rS($pdo,"CREATE OR REPLACE VIEW v_manager_warn AS SELECT st.staff_id,st.name,st.grade,p.sale_month,COALESCE(p.deal_count,0) AS deal_count,CASE WHEN COALESCE(p.deal_count,0)=0 THEN '실적없음' WHEN p.deal_count<2 THEN '미달' ELSE '정상' END AS warn_flag FROM staff st LEFT JOIN v_profit_by_person p ON st.staff_id=p.staff_id WHERE st.role IN('관리자','대표')","VIEW v_manager_warn",$sn,$er,$lg);
?>
<h2>환경 구축 결과</h2><div class="sub">테이블, 트리거, 뷰 생성이 완료되었습니다.</div>
<div class="la"><?php foreach($lg as $l):?><div class="l<?=$l[0]?>"><?=$l[0]==='ok'?'✅':($l[0]==='err'?'❌':'⏭')?> <?=htmlspecialchars($l[1])?></div><?php endforeach;?></div>
<div style="margin-top:1rem">
<?php if($er>0):?><div class="al ae">⚠ <?=$er?>개 오류 발생</div><?php else:?><div class="al as">✅ <?=$sn?>개 항목 구축 완료!</div><?php endif;?>
</div>
<div class="ab"><a href="setup.php?step=3" class="btn bp" style="text-decoration:none">다음: 계정 생성 →</a></div>

<?php elseif($step==='3'):?>
<h2>관리자 계정 생성</h2><div class="sub">시스템관리자와 대표 계정을 생성합니다.</div>
<?php if(!empty($_GET['err'])):?><div class="al ae"><?=htmlspecialchars($_GET['err'])?></div><?php endif;?>
<form method="POST"><input type="hidden" name="step" value="3_process">
<div class="sec" style="background:rgba(88,166,255,.08);border:1px solid rgba(88,166,255,.2)">
<div style="font-size:.8rem;font-weight:700;color:#58a6ff;margin-bottom:.5rem">🔑 시스템관리자 (sysadmin)</div>
<div class="fr"><div class="fg" style="margin-bottom:.5rem"><label class="fl">이름</label><input type="text" name="admin_name" class="fi" value="시스템관리자" required></div>
<div class="fg" style="margin-bottom:.5rem"><label class="fl">이메일</label><input type="email" name="admin_email" class="fi" placeholder="admin@company.com" required></div></div>
<div class="fr"><div class="fg" style="margin-bottom:0"><label class="fl">로그인 아이디</label><input type="text" name="admin_login" class="fi" value="sysadmin" required></div>
<div class="fg" style="margin-bottom:0"><label class="fl">비밀번호</label><input type="password" name="admin_pw" class="fi" placeholder="8자 이상" required minlength="8"></div></div></div>

<div class="sec" style="background:rgba(255,179,0,.08);border:1px solid rgba(255,179,0,.2)">
<div style="font-size:.8rem;font-weight:700;color:#FFB300;margin-bottom:.5rem">👑 대표 (CEO)</div>
<div class="fr"><div class="fg" style="margin-bottom:.5rem"><label class="fl">이름</label><input type="text" name="ceo_name" class="fi" placeholder="대표 이름" required></div>
<div class="fg" style="margin-bottom:.5rem"><label class="fl">이메일</label><input type="email" name="ceo_email" class="fi" placeholder="ceo@company.com" required></div></div>
<div class="fr"><div class="fg" style="margin-bottom:0"><label class="fl">로그인 아이디</label><input type="text" name="ceo_login" class="fi" placeholder="ceo" required></div>
<div class="fg" style="margin-bottom:0"><label class="fl">비밀번호</label><input type="password" name="ceo_pw" class="fi" placeholder="8자 이상" required minlength="8"></div></div></div>

<div class="ab"><a href="setup.php?step=2" class="btn bs" style="text-decoration:none">← 이전</a><button type="submit" class="btn bp">계정 생성 & 완료 →</button></div></form>

<?php elseif($step==='3_process'):
require_once __DIR__.'/config/database.php';$pdo=(new Database())->getConnection();
$an=trim($_POST['admin_name']??'');$ae=trim($_POST['admin_email']??'');$al=trim($_POST['admin_login']??'');$ap=$_POST['admin_pw']??'';
$cn=trim($_POST['ceo_name']??'');$ce=trim($_POST['ceo_email']??'');$cl=trim($_POST['ceo_login']??'');$cp=$_POST['ceo_pw']??'';
if(!$an||!$al||strlen($ap)<8||!$cn||!$cl||strlen($cp)<8){header("Location: setup.php?step=3&err=".urlencode("모든 필드를 올바르게 입력하세요. 비밀번호 8자 이상."));exit;}
if($al===$cl){header("Location: setup.php?step=3&err=".urlencode("관리자와 대표의 로그인 아이디는 달라야 합니다."));exit;}
try{$pdo->beginTransaction();
$pdo->exec("INSERT IGNORE INTO team_master (team_name,description) VALUES ('시스템','시스템 관리'),('경영진','경영진')");
$pdo->prepare("INSERT INTO staff (name,email,grade,team,role,hire_date,is_active,memo) VALUES (?,?,'수석','시스템','시스템관리자',CURDATE(),1,'초기 관리자')")->execute([$an,$ae]);$asi=$pdo->lastInsertId();
$pdo->prepare("INSERT INTO user_account (staff_id,login_id,password_hash,user_role,is_active,approval_status,must_change_pw,approved_at) VALUES (?,?,?,'sysadmin',1,'승인',0,NOW())")->execute([$asi,$al,password_hash($ap,PASSWORD_DEFAULT)]);
$pdo->prepare("INSERT INTO staff (name,email,grade,team,role,hire_date,is_active,memo) VALUES (?,?,'이사','경영진','대표',CURDATE(),1,'대표')")->execute([$cn,$ce]);$csi=$pdo->lastInsertId();
$pdo->prepare("INSERT INTO user_account (staff_id,login_id,password_hash,user_role,is_active,approval_status,must_change_pw,approved_at) VALUES (?,?,?,'ceo',1,'승인',0,NOW())")->execute([$csi,$cl,password_hash($cp,PASSWORD_DEFAULT)]);
$pdo->commit();$_SESSION['setup_accts']=['admin'=>['name'=>$an,'login'=>$al],'ceo'=>['name'=>$cn,'login'=>$cl]];
header("Location: setup.php?step=4");exit;
}catch(PDOException $e){$pdo->rollBack();header("Location: setup.php?step=3&err=".urlencode("계정 생성 실패: ".$e->getMessage()));exit;}

elseif($step==='4'):$ac=$_SESSION['setup_accts']??['admin'=>['name'=>'관리자','login'=>'sysadmin'],'ceo'=>['name'=>'대표','login'=>'ceo']];?>
<div style="text-align:center"><div style="font-size:3.5rem;margin-bottom:.5rem">🎉</div>
<h2 style="text-align:center">설치 완료!</h2><div class="sub" style="text-align:center">시스템이 성공적으로 설치되었습니다.</div></div>
<table class="rt"><thead><tr><th>구분</th><th>이름</th><th>로그인 아이디</th></tr></thead><tbody>
<tr><td><span style="color:#58a6ff;font-weight:600">🔑 시스템관리자</span></td><td><?=htmlspecialchars($ac['admin']['name'])?></td><td><strong><?=htmlspecialchars($ac['admin']['login'])?></strong></td></tr>
<tr><td><span style="color:#FFB300;font-weight:600">👑 대표</span></td><td><?=htmlspecialchars($ac['ceo']['name'])?></td><td><strong><?=htmlspecialchars($ac['ceo']['login'])?></strong></td></tr>
</tbody></table>
<div class="al ae" style="margin-top:1.5rem">⚠ <strong>보안 경고:</strong> 설치 완료 후 <code>setup.php</code> 파일을 반드시 삭제하세요.</div>
<div class="ab" style="justify-content:center"><a href="<?=$_base?>" class="btn bp" style="width:100%;max-width:300px;text-decoration:none">로그인 페이지로 이동 →</a></div>
<?php session_destroy();endif;?>
</div></div></body></html>
