-- Database Schema for Community Hub Admin Panel & App Backend
-- Import this file into phpMyAdmin on your cPanel

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- --------------------------------------------------------
-- Table structure for table `admin_users`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin user: username: admin | password: password123
INSERT INTO `admin_users` (`id`, `username`, `password`) VALUES
(1, 'admin', '$2y$10$wEeVgqV09u9nC92YVq41..vVbU95jK5l4z73Uj7m94z1wW6aZ1iGq')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- --------------------------------------------------------
-- Table structure for table `settings`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_name` varchar(100) NOT NULL DEFAULT 'Community Hub',
  `onesignal_app_id` varchar(100) DEFAULT '',
  `onesignal_rest_key` varchar(150) DEFAULT '',
  `privacy_policy` text DEFAULT NULL,
  `support_email` varchar(100) DEFAULT 'support@example.com',
  `app_version` varchar(20) DEFAULT '1.0.0',
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`id`, `app_name`, `onesignal_app_id`, `onesignal_rest_key`, `privacy_policy`, `support_email`, `app_version`) VALUES
(1, 'Official Community Hub', '', '', 'We respect your privacy. This application does not collect personal identity information. Notifications are powered by OneSignal to deliver community updates and announcements. Users can opt out of notifications anytime via device settings.', 'contact@yourdomain.com', '1.0.0')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- --------------------------------------------------------
-- Table structure for table `banners`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `banners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `image_url` varchar(500) NOT NULL,
  `click_url` varchar(500) DEFAULT '',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `banners` (`id`, `title`, `image_url`, `click_url`, `sort_order`, `status`) VALUES
(1, 'Welcome to Community Hub', 'https://images.unsplash.com/photo-1618005182384-a83a8bd57fbe?w=1000&q=80', 'https://t.me/', 1, 1),
(2, 'Exclusive Updates & Discussion', 'https://images.unsplash.com/photo-1557683316-973673baf926?w=1000&q=80', 'https://chat.whatsapp.com/', 2, 1)
ON DUPLICATE KEY UPDATE `id`=`id`;

-- --------------------------------------------------------
-- Table structure for table `social_links`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `social_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT '',
  `type` enum('telegram','whatsapp','facebook','youtube','discord','website') NOT NULL DEFAULT 'telegram',
  `link_url` varchar(500) NOT NULL,
  `badge_text` varchar(50) DEFAULT 'Official',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `social_links` (`id`, `title`, `description`, `type`, `link_url`, `badge_text`, `sort_order`, `status`) VALUES
(1, 'Telegram Main Channel', 'Get instant updates and daily notices', 'telegram', 'https://t.me/', 'VIP Channel', 1, 1),
(2, 'Telegram Discussion Group', 'Chat with community members 24/7', 'telegram', 'https://t.me/', '50k+ Members', 2, 1),
(3, 'WhatsApp Community Group', 'Connect directly on WhatsApp', 'whatsapp', 'https://chat.whatsapp.com/', 'Active', 3, 1),
(4, 'Official Facebook Group', 'Join our official Facebook community', 'facebook', 'https://facebook.com/groups/', 'Verified', 4, 1),
(5, 'YouTube Official Channel', 'Watch guides, tutorials and video updates', 'youtube', 'https://youtube.com/', 'Tutorials', 5, 1)
ON DUPLICATE KEY UPDATE `id`=`id`;

-- --------------------------------------------------------
-- Table structure for table `notices`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `notices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `tag` varchar(50) DEFAULT 'Announcement',
  `action_url` varchar(500) DEFAULT '',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `notices` (`id`, `title`, `content`, `tag`, `action_url`, `sort_order`, `status`) VALUES
(1, 'Welcome to our Official Community App!', 'Welcome everyone! Tap any group card above to join our Telegram, WhatsApp, and Facebook channels. Enable notifications so you never miss an important announcement.', 'Important', '', 1, 1),
(2, 'Rule reminder for all groups', 'Please be respectful in all group chats. No spam or unapproved advertisements. Contact admin for any help.', 'Notice', '', 2, 1)
ON DUPLICATE KEY UPDATE `id`=`id`;

COMMIT;