芝麻web文件管理V1.00

编辑当前文件:/home/pulsehostuk9/public_html/teafund.pulsehost.co.uk/init.sql
-- init.sql (full schema with funds & roles & activity log) CREATE DATABASE IF NOT EXISTS tea_fund CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE tea_fund; CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','manager','viewer') NOT NULL DEFAULT 'admin', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS funds ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS members ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NULL, join_date DATE NULL, notes TEXT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS payments ( member_id INT NOT NULL, fund_id INT NOT NULL, year INT NOT NULL, month TINYINT NOT NULL CHECK (month BETWEEN 1 AND 12), status ENUM('paid','unpaid','off') NOT NULL DEFAULT 'unpaid', updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (member_id, fund_id, year, month), FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE, FOREIGN KEY (fund_id) REFERENCES funds(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS activity_log ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, action VARCHAR(64) NOT NULL, details JSON NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ); -- seed minimal data INSERT IGNORE INTO users (email, password_hash, role) VALUES ('admin@example.com', '$2y$10$fVJgRZf1aAqvQK98m7c1eO7m5U2z2bY4u9cOqL2yQpZp0o3L1Q3nC', 'admin'); -- admin123 INSERT IGNORE INTO funds (id, name, active) VALUES (1, 'Tea', 1), (2, 'Coffee', 1);