CREATE TABLE users(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(60) UNIQUE NOT NULL,email VARCHAR(190) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,is_admin TINYINT(1) NOT NULL DEFAULT 0,vip_until DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE posts(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,title VARCHAR(180) NOT NULL,body TEXT NOT NULL,download_url TEXT NULL,vip_only TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE likes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,post_id INT UNSIGNED NOT NULL,user_id INT UNSIGNED NOT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uniq_like(post_id,user_id),FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE plans(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,days INT UNSIGNED NOT NULL,price DECIMAL(12,2) NOT NULL,currency VARCHAR(10) NOT NULL DEFAULT 'USD',description TEXT,active TINYINT(1) NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE payments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,plan_id INT UNSIGNED NOT NULL,method VARCHAR(60) NOT NULL,reference VARCHAR(190),status VARCHAR(30) NOT NULL DEFAULT 'pending',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,approved_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,FOREIGN KEY(plan_id) REFERENCES plans(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE settings(name VARCHAR(120) PRIMARY KEY,value TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO plans(name,days,price,currency,description) VALUES('1 Week VIP',7,5,'USD','7 days premium access'),('1 Month VIP',30,12,'USD','30 days premium access'),('3 Months VIP',90,25,'USD','90 days premium access');
INSERT INTO settings(name,value) VALUES('site_notice','Welcome to the community.'),('payment_methods','USDT TRC20|JazzCash|Easypaisa');