-- BlogPostAI Initial Data
-- Version: 1.0
-- Created: 2025-10-02


-- ============================================
-- Initial Roles
-- ============================================

INSERT INTO `roles` (`code`, `name`, `description`, `level`) VALUES
('admin', 'Administrator', 'Full system access with all permissions', 100),
('ops', 'Operations', 'Can manage publishing and monitor system', 80),
('editor', 'Editor', 'Can manage content and review posts', 60),
('viewer', 'Viewer', 'Read-only access to view content and reports', 20);

-- ============================================
-- Initial Programs
-- ============================================

INSERT INTO `programs` (`code`, `name`, `description`) VALUES
('program.fb_publisher', 'Facebook Publisher', 'Publish content to Facebook pages'),
('program.ig_publisher', 'Instagram Publisher', 'Publish content to Instagram business accounts'),
('program.xhs_publisher', 'XHS Publisher', 'Publish content to XiaoHongShu (小紅書)'),
('program.scheduler', 'Scheduler', 'Manage publishing schedule and triggers'),
('program.settings', 'Settings', 'Configure platform and system settings'),
('program.docs', 'Documentation', 'Access system documentation'),
('program.webchat_front', 'Frontend WebChat', 'Use AI chat on frontend'),
('program.webchat_admin', 'Admin WebChat', 'Use AI chat in admin panel'),
('program.audit', 'Audit Logs', 'View and export audit logs'),
('program.user_admin', 'User Management', 'Manage users, roles, and permissions');

-- ============================================
-- Initial Permissions
-- ============================================

-- Facebook Publisher Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.fb_publisher'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.fb_publisher'
UNION ALL
SELECT id, 'config' FROM `programs` WHERE code = 'program.fb_publisher';

-- Instagram Publisher Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.ig_publisher'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.ig_publisher'
UNION ALL
SELECT id, 'config' FROM `programs` WHERE code = 'program.ig_publisher';

-- XHS Publisher Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.xhs_publisher'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.xhs_publisher'
UNION ALL
SELECT id, 'config' FROM `programs` WHERE code = 'program.xhs_publisher';

-- Scheduler Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.scheduler'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.scheduler'
UNION ALL
SELECT id, 'config' FROM `programs` WHERE code = 'program.scheduler';

-- Settings Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.settings'
UNION ALL
SELECT id, 'config' FROM `programs` WHERE code = 'program.settings';

-- Documentation Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.docs';

-- Frontend WebChat Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.webchat_front'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.webchat_front';

-- Admin WebChat Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.webchat_admin'
UNION ALL
SELECT id, 'execute' FROM `programs` WHERE code = 'program.webchat_admin';

-- Audit Logs Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.audit';

-- User Management Permissions
INSERT INTO `permissions` (`program_id`, `action`)
SELECT id, 'read' FROM `programs` WHERE code = 'program.user_admin'
UNION ALL
SELECT id, 'create' FROM `programs` WHERE code = 'program.user_admin'
UNION ALL
SELECT id, 'update' FROM `programs` WHERE code = 'program.user_admin'
UNION ALL
SELECT id, 'delete' FROM `programs` WHERE code = 'program.user_admin';

-- ============================================
-- Admin Role - Full Permissions
-- ============================================

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
  (SELECT id FROM roles WHERE code = 'admin'),
  id,
  1
FROM `permissions`;

-- ============================================
-- Ops Role - Operations Permissions
-- ============================================

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
  (SELECT id FROM roles WHERE code = 'ops'),
  p.id,
  1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE pr.code IN (
  'program.fb_publisher',
  'program.ig_publisher',
  'program.xhs_publisher',
  'program.scheduler',
  'program.docs',
  'program.webchat_admin',
  'program.audit'
) AND p.action IN ('read', 'execute');

-- ============================================
-- Editor Role - Content Management Permissions
-- ============================================

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
  (SELECT id FROM roles WHERE code = 'editor'),
  p.id,
  1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE pr.code IN (
  'program.fb_publisher',
  'program.ig_publisher',
  'program.xhs_publisher',
  'program.docs',
  'program.webchat_front'
) AND p.action IN ('read', 'execute');

-- ============================================
-- Viewer Role - Read-Only Permissions
-- ============================================

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `allow`)
SELECT
  (SELECT id FROM roles WHERE code = 'viewer'),
  p.id,
  1
FROM `permissions` p
INNER JOIN `programs` pr ON p.program_id = pr.id
WHERE p.action = 'read';

-- ============================================
-- Initial Admin User
-- Password: ulik9910 (hashed with bcrypt cost=12)
-- ============================================

INSERT INTO `users` (
  `username`,
  `password_hash`,
  `email`,
  `full_name`,
  `locale`,
  `timezone`,
  `status`
) VALUES (
  'jeff',
  '$2y$12$LQ3xQZJZJqYZNJGQXZ8F8ODYWxh9f7KxFvP5N3XqZJ4BHMZLqh7Ym',
  'admin@blogpostai.local',
  'Jeff (Administrator)',
  'zh-TW',
  'Asia/Taipei',
  'active'
);

-- Assign admin role to initial user
INSERT INTO `user_roles` (`user_id`, `role_id`)
SELECT
  (SELECT id FROM users WHERE username = 'jeff'),
  (SELECT id FROM roles WHERE code = 'admin');

-- ============================================
-- Initial Ollama Server
-- ============================================

INSERT INTO `ollama_servers` (`server_name`, `ip`, `port`, `is_default`)
VALUES ('Main Ollama Server', '10.66.88.208', 11434, 1);

-- ============================================
-- Initial System Settings (non-sensitive)
-- ============================================

INSERT INTO `settings` (`section`, `key`, `value_encrypted`, `is_sensitive`) VALUES
('common', 'TZ', 'Asia/Taipei', 0),
('common', 'SCHEDULE_CRON', '0 8 * * *', 0),
('common', 'FB_ENABLED', 'true', 0),
('common', 'IG_ENABLED', 'true', 0),
('common', 'XHS_ENABLED', 'true', 0),
('common', 'UTM_SOURCE', 'social', 0),
('common', 'UTM_MEDIUM', 'auto_post', 0),
('common', 'UTM_CAMPAIGN', 'daily-post', 0),
('instagram', 'IG_DEFAULT_MEDIA_TYPE', 'image', 0),
('xhs', 'XHS_PUBLISHER_MODE', 'rpa', 0),
('xhs', 'XHS_PROVIDER', 'ldplayer', 0),
('xhs', 'XHS_RANDOM_DELAY_MIN', '5', 0),
('xhs', 'XHS_RANDOM_DELAY_MAX', '30', 0),
('xhs', 'XHS_DAILY_POST_LIMIT', '5', 0),
('ai', 'AI_DEFAULT_LOCALE', 'zh-TW', 0),
('ai', 'AI_DEFAULT_TOPIC', 'general', 0),
('ai', 'AI_DEFAULT_LENGTH', 'short', 0),
('ai', 'AI_DEFAULT_NEED_MEDIA', 'true', 0),
('n8n', 'N8N_SERVER_IP', '10.66.88.62', 0),
('n8n', 'N8N_SERVER_PORT', '5678', 0),
('n8n', 'N8N_CONTAINER_NAME', 'n8n', 0),
('ollama', 'OLLAMA_SERVER_IP', '10.66.88.208', 0),
('ollama', 'OLLAMA_SERVER_PORT', '11434', 0),
('webchat', 'OPENAI_MODEL', 'gpt-4o-mini', 0),
('webchat', 'PROVIDER', 'openai', 0),
('security', 'SESSION_TIMEOUT', '3600', 0),
('security', 'ENABLE_2FA', 'false', 0),
('security', 'PASSWORD_MIN_LENGTH', '12', 0),
('security', 'LOGIN_MAX_ATTEMPTS', '5', 0),
('security', 'LOGIN_LOCKOUT_TIME', '900', 0),
('security', 'LOG_RETENTION_DAYS', '90', 0);
