31 lines
1.1 KiB
SQL
31 lines
1.1 KiB
SQL
-- Add users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('admin', 'editor', 'viewer') DEFAULT 'viewer',
|
|
full_name VARCHAR(100),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Safely add status column
|
|
SET @dbname = DATABASE();
|
|
SET @tablename = "projects";
|
|
SET @columnname = "status";
|
|
SET @preparedStatement = (SELECT IF(
|
|
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @dbname
|
|
AND TABLE_NAME = @tablename
|
|
AND COLUMN_NAME = @columnname) > 0,
|
|
"SELECT 1",
|
|
"ALTER TABLE projects ADD COLUMN status ENUM('active', 'completed', 'on_hold', 'archived') DEFAULT 'active' AFTER industry"
|
|
));
|
|
PREPARE stmt FROM @preparedStatement;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Create default admin user (password: admin123)
|
|
INSERT INTO users (username, password, role, full_name)
|
|
VALUES ('admin', '$2y$10$Tsw26nakbueEO40q9tx8Pe4g1yQuWfQFhKXjyVYvbNakTgBi/7NkG', 'admin', 'System Administrator')
|
|
ON DUPLICATE KEY UPDATE password = VALUES(password);
|