r117/core/Database.php · 约 17429 字符
1<?php2/**3 * SQLite 数据库单例 + 自动迁移 + 演示数据播种4 */5class Database {6 private static $pdo = null;78 public static function pdo(): PDO {9 if (self::$pdo === null) {10 if (!is_dir(DATA_DIR)) { mkdir(DATA_DIR, 0755, true); }11 $pdo = new PDO('sqlite:' . DB_FILE);12 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);13 $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);14 $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);15 $pdo->exec('PRAGMA journal_mode=WAL;');16 $pdo->exec('PRAGMA busy_timeout=5000;');17 $pdo->exec('PRAGMA foreign_keys=ON;');18 self::$pdo = $pdo;19 self::migrate($pdo);20 }21 return self::$pdo;22 }2324 private static function migrate(PDO $pdo): void {25 $pdo->exec("CREATE TABLE IF NOT EXISTS banners (26 id INTEGER PRIMARY KEY AUTOINCREMENT,27 title TEXT NOT NULL DEFAULT '',28 image TEXT NOT NULL DEFAULT '',29 link TEXT NOT NULL DEFAULT '',30 sort INTEGER NOT NULL DEFAULT 0,31 status INTEGER NOT NULL DEFAULT 132 )");33 $pdo->exec("CREATE TABLE IF NOT EXISTS categories (34 id INTEGER PRIMARY KEY AUTOINCREMENT,35 name TEXT NOT NULL,36 type TEXT NOT NULL DEFAULT 'kind',37 sort INTEGER NOT NULL DEFAULT 0,38 status INTEGER NOT NULL DEFAULT 139 )");40 $pdo->exec("CREATE TABLE IF NOT EXISTS products (41 id INTEGER PRIMARY KEY AUTOINCREMENT,42 name TEXT NOT NULL,43 kind_id INTEGER NOT NULL DEFAULT 0,44 scenes TEXT NOT NULL DEFAULT '',45 price REAL NOT NULL DEFAULT 0,46 original_price REAL NOT NULL DEFAULT 0,47 sales INTEGER NOT NULL DEFAULT 0,48 rating REAL NOT NULL DEFAULT 5,49 stock INTEGER NOT NULL DEFAULT 99,50 status INTEGER NOT NULL DEFAULT 1,51 is_new INTEGER NOT NULL DEFAULT 0,52 is_hot INTEGER NOT NULL DEFAULT 0,53 is_recommend INTEGER NOT NULL DEFAULT 0,54 description TEXT NOT NULL DEFAULT '',55 ingredients TEXT NOT NULL DEFAULT '',56 allergen TEXT NOT NULL DEFAULT '',57 shelf_life TEXT NOT NULL DEFAULT '',58 advice TEXT NOT NULL DEFAULT '',59 images TEXT NOT NULL DEFAULT '[]',60 tags TEXT NOT NULL DEFAULT ''61 )");62 $pdo->exec("CREATE TABLE IF NOT EXISTS skus (63 id INTEGER PRIMARY KEY AUTOINCREMENT,64 product_id INTEGER NOT NULL,65 size TEXT NOT NULL DEFAULT '8寸',66 flavor TEXT NOT NULL DEFAULT '原味',67 price REAL NOT NULL DEFAULT 0,68 stock INTEGER NOT NULL DEFAULT 99,69 sales INTEGER NOT NULL DEFAULT 070 )");71 $pdo->exec("CREATE TABLE IF NOT EXISTS members (72 id INTEGER PRIMARY KEY AUTOINCREMENT,73 username TEXT NOT NULL DEFAULT '',74 phone TEXT NOT NULL DEFAULT '',75 level TEXT NOT NULL DEFAULT 'normal',76 points INTEGER NOT NULL DEFAULT 0,77 balance REAL NOT NULL DEFAULT 0,78 growth INTEGER NOT NULL DEFAULT 0,79 birthday TEXT NOT NULL DEFAULT '',80 status INTEGER NOT NULL DEFAULT 1,81 created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))82 )");83 $pdo->exec("CREATE TABLE IF NOT EXISTS coupons (84 id INTEGER PRIMARY KEY AUTOINCREMENT,85 name TEXT NOT NULL DEFAULT '',86 type TEXT NOT NULL DEFAULT 'full_reduce',87 threshold REAL NOT NULL DEFAULT 0,88 value REAL NOT NULL DEFAULT 0,89 discount REAL NOT NULL DEFAULT 1,90 valid_days INTEGER NOT NULL DEFAULT 30,91 total INTEGER NOT NULL DEFAULT 0,92 received INTEGER NOT NULL DEFAULT 0,93 used INTEGER NOT NULL DEFAULT 0,94 status INTEGER NOT NULL DEFAULT 195 )");96 $pdo->exec("CREATE TABLE IF NOT EXISTS coupon_user (97 id INTEGER PRIMARY KEY AUTOINCREMENT,98 coupon_id INTEGER NOT NULL,99 member_id INTEGER NOT NULL DEFAULT 0,100 status TEXT NOT NULL DEFAULT 'unused',101 received_at TEXT NOT NULL DEFAULT (datetime('now','localtime')),102 used_at TEXT NOT NULL DEFAULT ''103 )");104 $pdo->exec("CREATE TABLE IF NOT EXISTS carts (105 id INTEGER PRIMARY KEY AUTOINCREMENT,106 member_id INTEGER NOT NULL DEFAULT 0,107 product_id INTEGER NOT NULL,108 sku_id INTEGER NOT NULL DEFAULT 0,109 qty INTEGER NOT NULL DEFAULT 1110 )");111 $pdo->exec("CREATE TABLE IF NOT EXISTS orders (112 id INTEGER PRIMARY KEY AUTOINCREMENT,113 order_no TEXT NOT NULL DEFAULT '',114 member_id INTEGER NOT NULL DEFAULT 0,115 contact_name TEXT NOT NULL DEFAULT '',116 phone TEXT NOT NULL DEFAULT '',117 address TEXT NOT NULL DEFAULT '',118 delivery_type TEXT NOT NULL DEFAULT 'delivery',119 delivery_time TEXT NOT NULL DEFAULT '',120 card_msg TEXT NOT NULL DEFAULT '',121 remark TEXT NOT NULL DEFAULT '',122 coupon_id INTEGER NOT NULL DEFAULT 0,123 coupon_amount REAL NOT NULL DEFAULT 0,124 points_used INTEGER NOT NULL DEFAULT 0,125 total_amount REAL NOT NULL DEFAULT 0,126 pay_amount REAL NOT NULL DEFAULT 0,127 status TEXT NOT NULL DEFAULT 'pending',128 created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))129 )");130 $pdo->exec("CREATE TABLE IF NOT EXISTS order_items (131 id INTEGER PRIMARY KEY AUTOINCREMENT,132 order_id INTEGER NOT NULL,133 product_id INTEGER NOT NULL,134 sku_id INTEGER NOT NULL DEFAULT 0,135 name TEXT NOT NULL DEFAULT '',136 size TEXT NOT NULL DEFAULT '',137 flavor TEXT NOT NULL DEFAULT '',138 price REAL NOT NULL DEFAULT 0,139 qty INTEGER NOT NULL DEFAULT 1,140 subtotal REAL NOT NULL DEFAULT 0141 )");142 $pdo->exec("CREATE TABLE IF NOT EXISTS reviews (143 id INTEGER PRIMARY KEY AUTOINCREMENT,144 order_id INTEGER NOT NULL DEFAULT 0,145 product_id INTEGER NOT NULL DEFAULT 0,146 member_id INTEGER NOT NULL DEFAULT 0,147 rating INTEGER NOT NULL DEFAULT 5,148 content TEXT NOT NULL DEFAULT '',149 images TEXT NOT NULL DEFAULT '[]',150 reply TEXT NOT NULL DEFAULT '',151 status INTEGER NOT NULL DEFAULT 1,152 created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))153 )");154 $pdo->exec("CREATE TABLE IF NOT EXISTS custom_cakes (155 id INTEGER PRIMARY KEY AUTOINCREMENT,156 member_id INTEGER NOT NULL DEFAULT 0,157 image TEXT NOT NULL DEFAULT '',158 text TEXT NOT NULL DEFAULT '',159 shape TEXT NOT NULL DEFAULT '',160 size TEXT NOT NULL DEFAULT '8寸',161 note TEXT NOT NULL DEFAULT '',162 status TEXT NOT NULL DEFAULT 'pending',163 created_at TEXT NOT NULL DEFAULT (datetime('now','localtime'))164 )");165 $pdo->exec("CREATE TABLE IF NOT EXISTS settings (166 k TEXT PRIMARY KEY,167 v TEXT NOT NULL DEFAULT ''168 )");169170 self::seed($pdo);171 }172173 private static function seed(PDO $pdo): void {174 $cnt = $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn();175 if ($cnt > 0) return;176177 // ---- banners ----178 $banners = [179 ['新品上市 · 当季限定', '🎂', '#category', 1],180 ['生日蛋糕专场 · 免费升磅', '🎉', '#category', 2],181 ['会员日福利 · 积分兑好礼', '❤️', '#member', 3],182 ['精致下午茶套餐', '☕', '#category', 4],183 ];184 $ins = $pdo->prepare("INSERT INTO banners (title,image,link,sort) VALUES (?,?,?,?)");185 foreach ($banners as $b) { $ins->execute($b); }186187 // ---- categories ----188 $scenes = ['生日蛋糕','婚礼庆典','聚会派对','商务茶歇','儿童节','情侣下午茶','长辈祝寿','节日礼盒'];189 $kinds = ['奶油蛋糕','慕斯蛋糕','芝士蛋糕','巧克力蛋糕','水果蛋糕','无糖蛋糕','翻糖蛋糕'];190 $sizes = ['6寸','8寸','10寸','12寸','14寸','多层'];191 $stmt = $pdo->prepare("INSERT INTO categories (name,type,sort) VALUES (?,?,?)");192 $s = 1; foreach ($scenes as $n) { $stmt->execute([$n,'scene',$s++]); }193 $s = 1; foreach ($kinds as $n) { $stmt->execute([$n,'kind',$s++]); }194 $s = 1; foreach ($sizes as $n) { $stmt->execute([$n,'size',$s++]); }195196 // kind id 映射（按插入顺序）197 $kindMap = [];198 $rows = $pdo->query("SELECT id,name FROM categories WHERE type='kind' ORDER BY id")->fetchAll();199 foreach ($rows as $r) { $kindMap[$r['name']] = $r['id']; }200201 // ---- products ----202 $products = [203 ['经典草莓奶油蛋糕','奶油蛋糕','生日蛋糕|情侣下午茶',168,218,true,true,false,204 '选用新鲜草莓与动物奶油，口感轻盈不腻，是生日与约会的经典之选。',205 '小麦粉、鸡蛋、牛奶、动物奶油、草莓','含麸质、蛋、奶','冷藏 2 天','0-4℃ 冷藏保存，食用前回温 10 分钟口感更佳',206 '🍓|🎂|🍰','招牌'],207 ['芒果千层慕斯','慕斯蛋糕','情侣下午茶|聚会派对',158,198,false,false,true,208 '层层薄饼夹芒果慕斯，入口即化，酸甜清新。',209 '小麦粉、鸡蛋、芒果、动物奶油','含麸质、蛋、奶、芒果','冷藏 2 天','冷藏保存，避免反复冻融',210 '🥭|🍰|✨','人气'],211 ['海盐芝士蛋糕','芝士蛋糕','商务茶歇|情侣下午茶',178,228,false,false,false,212 '微咸海盐平衡芝士厚重，风味层次丰富。',213 '奶酪、鸡蛋、饼干底、海盐','含蛋、奶、麸质','冷藏 3 天','冷藏保存',214 '🧀|🍰|💛','咸香'],215 ['黑森林巧克力蛋糕','巧克力蛋糕','生日蛋糕|聚会派对',188,238,false,true,false,216 '浓郁黑巧克力与樱桃酒香，经典德式风味。',217 '巧克力、樱桃、奶油、小麦粉','含蛋、奶、麸质','冷藏 2 天','冷藏保存',218 '🍫|🎂|🖤','微醺'],219 ['鲜果缤纷水果蛋糕','水果蛋糕','生日蛋糕|儿童节',198,258,true,false,true,220 '当季时令鲜果铺满，颜值与营养兼具，孩子最爱。',221 '草莓、蓝莓、芒果、动物奶油、小麦粉','含麸质、蛋、奶','冷藏 2 天','即做即食风味最佳',222 '🍓|🫐|🥭|🎂','鲜果'],223 ['轻享无糖燕麦蛋糕','无糖蛋糕','长辈祝寿|商务茶歇',168,208,true,false,false,224 '0 添加蔗糖，燕麦谷香，长辈控糖也能安心吃。',225 '燕麦、鸡蛋、代糖、植物油','含蛋、麸质','冷藏 3 天','糖尿病人群亦可适量食用',226 '🌾|🍰|💚','无糖'],227 ['迪士尼公主翻糖蛋糕','翻糖蛋糕','儿童节|生日蛋糕',288,358,true,true,false,228 '手工翻糖造型，公主城堡立体呈现，生日派对焦点。',229 '翻糖、蛋糕胚、食用色素','含麸质、蛋、奶','常温 1 天/冷藏 3 天','避免阳光直射',230 '👑|🏰|🎂','定制'],231 ['法式覆盆子慕斯','慕斯蛋糕','情侣下午茶|商务茶歇',198,248,false,false,true,232 '覆盆子酸甜与慕斯柔滑交织，红粉诱惑。',233 '覆盆子、动物奶油、小麦粉','含麸质、蛋、奶','冷藏 2 天','冷藏保存',234 '🍇|🍰|🌸','法式'],235 ['提拉米苏芝士','芝士蛋糕','聚会派对|情侣下午茶',176,216,false,true,false,236 '马斯卡彭与咖啡酒香，意式经典。',237 '马斯卡彭、手指饼、咖啡、蛋','含蛋、奶、麸质','冷藏 3 天','冷藏保存',238 '☕|🍰|🤎','意式'],239 ['红丝绒巧克力','巧克力蛋糕','婚礼庆典|情侣下午茶',208,268,false,false,true,240 '红丝绒胚体配白巧奶油，浪漫喜庆。',241 '可可、奶油芝士、小麦粉','含蛋、奶、麸质','冷藏 2 天','冷藏保存',242 '🌹|🍰|❤️','浪漫'],243 ['抹茶红豆慕斯','慕斯蛋糕','生日蛋糕|情侣下午茶',168,208,true,false,false,244 '宇治抹茶微苦回甘，红豆绵密。',245 '抹茶、红豆、动物奶油、小麦粉','含麸质、蛋、奶','冷藏 2 天','冷藏保存',246 '🍵|🍰|💚','和风'],247 ['寿桃无糖祝寿蛋糕','无糖蛋糕','长辈祝寿',238,298,false,true,false,248 '寿桃造型无糖配方，祝寿宴席体面之选。',249 '燕麦、代糖、坚果、植物油','含蛋、麸质、坚果','冷藏 3 天','祝寿专属',250 '🍑|🎂|🎊','祝寿'],251 ];252 $insP = $pdo->prepare("INSERT INTO products253 (name,kind_id,scenes,price,original_price,is_new,is_hot,is_recommend,description,ingredients,allergen,shelf_life,advice,images,tags)254 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");255 $sizeMul = ['6寸'=>0.7,'8寸'=>1.0,'10寸'=>1.35,'12寸'=>1.7,'14寸'=>2.1,'多层'=>3.0];256 $flavors = [['原味',0],['招牌',10]];257 $pid = 0;258 foreach ($products as $p) {259 $kindId = $kindMap[$p[1]] ?? 0;260 $insP->execute([$p[0],$kindId,$p[2],$p[3],$p[4],261 $p[5]?1:0,$p[6]?1:0,$p[7]?1:0,$p[8],$p[9],$p[10],$p[11],$p[12],$p[13],$p[14]]);262 $pid = $pdo->lastInsertId();263 // 生成 SKU：4 种尺寸 × 2 口味264 $insS = $pdo->prepare("INSERT INTO skus (product_id,size,flavor,price,stock) VALUES (?,?,?,?,?)");265 foreach (['6寸','8寸','10寸','12寸'] as $sz) {266 foreach ($flavors as $fl) {267 $price = round($p[3]*$sizeMul[$sz] + $fl[1]);268 $insS->execute([$pid,$sz,$fl[0],$price,99]);269 }270 }271 }272273 // ---- coupons ----274 $coupons = [275 ['新人专享券','newcomer',0,20,1,30,999],276 ['满199减30','full_reduce',199,30,1,30,999],277 ['满299减50','full_reduce',299,50,1,30,999],278 ['会员尊享8折','discount',0,0,0.8,30,500],279 ['无门槛5元券','no_threshold',0,5,1,15,999],280 ['生日专享券','birthday',0,30,1,7,300],281 ];282 $insCou = $pdo->prepare("INSERT INTO coupons (name,type,threshold,value,discount,valid_days,total) VALUES (?,?,?,?,?,?,?)");283 foreach ($coupons as $c) { $insCou->execute($c); }284285 // ---- members ----286 $members = [287 ['小甜','13800000001','normal',120,0,120,'',1],288 ['阿慕','阿慕', 'gold', 880,200,1500,'',1],289 ['寿星婆婆','13900000003','diamond',2600,500,3200,'1949-10-01',1],290 ];291 $insM = $pdo->prepare("INSERT INTO members (username,phone,level,points,balance,growth,birthday,status) VALUES (?,?,?,?,?,?,?,?)");292 foreach ($members as $m) { $insM->execute($m); }293294 // ---- reviews ----295 $reviews = [296 [1,1,1,5,'蛋糕很新鲜，草莓超甜，配送也准时，下次还来！','[]'],297 [3,2,2,5,'海盐芝士味道惊艳，不太腻，办公室下午茶首选。','[]'],298 [5,3,1,4,'水果很丰富，孩子很喜欢，就是有点小贵哈哈。','[]'],299 ];300 $insR = $pdo->prepare("INSERT INTO reviews (product_id,member_id,order_id,rating,content,images) VALUES (?,?,?,?,?,?)");301 foreach ($reviews as $r) { $insR->execute($r); }302303 // ---- custom_cakes ----304 $pdo->prepare("INSERT INTO custom_cakes (member_id,image,text,shape,size,note,status) VALUES (?,?,?,?,?,?,?)")305 ->execute([2,'','宝贝生日快乐','城堡','10寸','请加卡通蜡烛','pending']);306307 // ---- settings ----308 $sets = [309 ['shop_name', SITE_NAME],310 ['phone', '400-888-6688'],311 ['address', '西安市雁塔区科技路 18 号甜趣烘焙工坊'],312 ['delivery_fee', '15'],313 ['free_delivery_amount', '199'],314 ['delivery_range_km', '10'],315 ['delivery_advance_hours', '6'],316 ['open_time', '09:00-21:00'],317 ['notice', '蛋糕为新鲜手工制作，请提前 6 小时预约；定制化蛋糕建议提前 1 天。'],318 ];319 $insS = $pdo->prepare("INSERT INTO settings (k,v) VALUES (?,?)");320 foreach ($sets as $s) { $insS->execute($s); }321 }322}323