-- ===========================================================================
-- SmartKabir  -  COMPLETE DATABASE (all features, including admin notes and birthdays)
-- Plain standard SQL: works the same on MySQL and MariaDB (phpMyAdmin runs it either way).
--
-- HOW TO USE
--   1. Create a NEW, EMPTY database in cPanel (MySQL Databases) and add a user to it.
--   2. phpMyAdmin -> select that new database -> Import -> choose this file -> Go.
--   3. Put the new database name / user / password in connectdb.php.
--
-- Already imported an older version of this file into a database? Do NOT run any upgrade script:
-- in phpMyAdmin open that database > Structure > tick "Check all" > "With selected:" Drop > Yes,
-- then import this file. (If it already holds subscribers you want to keep, export it first.)
--
-- This file deliberately has NO "DROP TABLE". If you import it into a database that
-- already has these tables, MySQL stops with "table already exists" and changes nothing -
-- so it can never wipe a live database by accident.
--
-- It contains structure plus default settings only. Autoresponder emails and existing
-- subscribers are NOT included; add them again in the admin panel (or restore from a backup).
-- ===========================================================================

SET NAMES utf8mb4;

CREATE TABLE `settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `setting` varchar(120) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `autoresponder` (
  `aid` int NOT NULL AUTO_INCREMENT,
  `asubject` varchar(250) DEFAULT NULL,
  `amessage` text,
  `aday` int DEFAULT NULL,
  `sent` int NOT NULL DEFAULT 0,
  `topad` varchar(10) NOT NULL DEFAULT 'off',
  `bottomad` varchar(10) NOT NULL DEFAULT 'off',
  `gstatus` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `banners` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bannerurl` varchar(120) NOT NULL DEFAULT '',
  `targeturl` varchar(120) NOT NULL DEFAULT '',
  `status` tinyint NOT NULL DEFAULT 0,
  `impressions` int NOT NULL DEFAULT 0,
  `clicks` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `emailad` (
  `adid` int NOT NULL AUTO_INCREMENT,
  `admessage` text,
  `adstatus` varchar(10) NOT NULL DEFAULT 'off',
  PRIMARY KEY (`adid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `pages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL DEFAULT '',
  `htmlcode` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Custom emails sent from Admin > Email (one subscriber or everyone).
-- mail_campaigns = one row per email you wrote; mail_queue = one row per recipient, so a big send
-- can be paused, resumed or cancelled and you can always see who received what.
CREATE TABLE `mail_campaigns` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kind` varchar(10) NOT NULL DEFAULT 'bulk',
  `subject` varchar(250) NOT NULL DEFAULT '',
  `message` longtext NOT NULL,
  `footer` tinyint NOT NULL DEFAULT 1,
  `created` datetime NOT NULL,
  `status` varchar(12) NOT NULL DEFAULT 'sending',
  `total` int NOT NULL DEFAULT 0,
  `sent` int NOT NULL DEFAULT 0,
  `failed` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `mail_queue` (
  `id` int NOT NULL AUTO_INCREMENT,
  `campaign_id` int NOT NULL,
  `subscriber_id` int NOT NULL,
  `email` varchar(255) NOT NULL DEFAULT '',
  `firstname` varchar(255) NOT NULL DEFAULT '',
  `status` varchar(10) NOT NULL DEFAULT 'pending',
  `error` varchar(255) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_campaign_status` (`campaign_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Events and registrations, with bKash payment (manual or automatic).
-- events / event_tickets are created in Admin > Events; event_registrations holds each registration and
-- its payment; event_config holds the payment settings (Admin > Events > Payment settings).
CREATE TABLE `events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `description` text,
  `event_date` datetime DEFAULT NULL,
  `last_payment_date` date DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `venue` varchar(255) NOT NULL DEFAULT '',
  `seat_limit` int NOT NULL DEFAULT 0,
  `status` varchar(10) NOT NULL DEFAULT 'draft',
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `event_tickets` (
  `id` int NOT NULL AUTO_INCREMENT,
  `event_id` int NOT NULL,
  `name` varchar(100) NOT NULL DEFAULT '',
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `seat_limit` int NOT NULL DEFAULT 0,
  `active` tinyint NOT NULL DEFAULT 1,
  `sort_order` int NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `event_registrations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ref` varchar(12) NOT NULL,
  `event_id` int NOT NULL,
  `ticket_id` int NOT NULL,
  `ticket_name` varchar(100) NOT NULL DEFAULT '',
  `unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `quantity` int NOT NULL DEFAULT 1,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `firstname` varchar(255) NOT NULL DEFAULT '',
  `phone` varchar(11) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `notes` text,
  `payment_method` varchar(10) NOT NULL DEFAULT 'manual',
  `payment_status` varchar(12) NOT NULL DEFAULT 'pending',
  `bkash_payment_id` varchar(64) DEFAULT NULL,
  `bkash_trx_id` varchar(32) DEFAULT NULL,
  `manual_trx_id` varchar(32) DEFAULT NULL,
  `payer_number` varchar(20) DEFAULT NULL,
  `pay_note` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  `paid_at` datetime DEFAULT NULL,
  `ip` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ref` (`ref`),
  UNIQUE KEY `uq_manual_trx` (`manual_trx_id`),
  UNIQUE KEY `uq_bkash_trx` (`bkash_trx_id`),
  KEY `idx_event_status` (`event_id`, `payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The ticket lines of a registration: one row per ticket type chosen (for example Regular x 2 and VIP x 1).
CREATE TABLE `event_registration_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `registration_id` int NOT NULL,
  `ticket_id` int NOT NULL,
  `ticket_name` varchar(100) NOT NULL DEFAULT '',
  `unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `quantity` int NOT NULL DEFAULT 1,
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_registration` (`registration_id`),
  KEY `idx_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Birthday wishes: one row per person per year per channel (email / sms).
CREATE TABLE `birthday_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subscriber_id` int NOT NULL,
  `year_sent` int NOT NULL,
  `channel` varchar(10) NOT NULL,
  `sent_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_once_a_year` (`subscriber_id`, `year_sent`, `channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `event_config` (
  `name` varchar(50) NOT NULL,
  `value` text,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Subscribers: the original autoresponder columns (firstname = form field 1, email = form
-- field 3) plus the other form answers (pdp_...). Only the first four form fields are
-- required; that is enforced by the form and sent.php, so every pdp_ column may be empty.
-- pdp_membership_number is filled automatically with the subscriber's ID at sign-up.
CREATE TABLE `subscribers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `status` varchar(300) NOT NULL DEFAULT 'Respondent',
  `verified` tinyint NOT NULL DEFAULT 1,
  `ip` varchar(45) NOT NULL DEFAULT '',
  `code` varchar(30) NOT NULL DEFAULT '',
  `joindate` date NOT NULL DEFAULT '2000-01-01',
  `nmailout` varchar(11) NOT NULL DEFAULT '-1',
  `pdp_phone` varchar(11) NULL DEFAULT NULL,
  `pdp_dob` date NULL DEFAULT NULL,
  `pdp_nid_number` varchar(255) NULL DEFAULT NULL,
  `pdp_profession` varchar(255) NULL DEFAULT NULL,
  `pdp_identity` varchar(255) NULL DEFAULT NULL,
  `pdp_monthly_income` varchar(255) NULL DEFAULT NULL,
  `pdp_monthly_expense` varchar(255) NULL DEFAULT NULL,
  `pdp_income_goal` varchar(255) NULL DEFAULT NULL,
  `pdp_hours_for_passive_income` varchar(255) NULL DEFAULT NULL,
  `pdp_mindset_change_willingness` varchar(255) NULL DEFAULT NULL,
  `pdp_comfort_zone_willingness` varchar(255) NULL DEFAULT NULL,
  `pdp_daily_action_followup` varchar(255) NULL DEFAULT NULL,
  `pdp_motivation` text NULL,
  `pdp_annoyance` text NULL,
  `pdp_listen_or_speak` varchar(255) NULL DEFAULT NULL,
  `pdp_happiness` text NULL,
  `pdp_strengths` text NULL,
  `pdp_weaknesses` text NULL,
  `pdp_wish` text NULL,
  `pdp_passion` text NULL,
  `pdp_hobby` varchar(255) NULL DEFAULT NULL,
  `pdp_talent` varchar(255) NULL DEFAULT NULL,
  `pdp_skills` text NULL,
  `pdp_dream` text NULL,
  `pdp_niche` varchar(255) NULL DEFAULT NULL,
  `pdp_future_vision_3yr` text NULL,
  `pdp_nid_front` varchar(255) NULL DEFAULT NULL,
  `pdp_nid_back` varchar(255) NULL DEFAULT NULL,
  `pdp_membership_number` varchar(255) NULL DEFAULT NULL,
  `admin_note` text NULL,
  `pdp_father_name` varchar(255) NULL,
  `pdp_mother_name` varchar(255) NULL,
  `pdp_spouse_name` varchar(255) NULL,
  `pdp_child1_name` varchar(255) NULL,
  `pdp_child2_name` varchar(255) NULL,
  `pdp_child3_name` varchar(255) NULL,
  `pdp_child4_name` varchar(255) NULL,
  `pdp_emergency_phone` varchar(11) NULL,
  `pdp_present_village` varchar(255) NULL,
  `pdp_present_post_office` varchar(255) NULL,
  `pdp_permanent_village` varchar(255) NULL,
  `pdp_permanent_post_office` varchar(255) NULL,
  `pdp_ssc_school` varchar(255) NULL,
  `pdp_profession_type` varchar(255) NULL,
  `pdp_workplace` varchar(255) NULL,
  `pdp_tshirt_size` varchar(10) NULL,
  `pdp_blood_group` varchar(10) NULL,
  `pdp_photo` varchar(255) NULL,
  `pdp_comments` text NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`(100)),
  KEY `idx_membership` (`pdp_membership_number`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Default settings. EDIT THESE in phpMyAdmin (settings table) or Admin > General Settings:
--   adminemail    = the address welcome emails are sent FROM and admin notices go TO
--   maileremail   = the autoresponder's From address
--   adminname     = the name signed at the bottom of emails
--   adminpassword = change it from 'admin'
-- ---------------------------------------------------------------------------
INSERT INTO `settings` (`name`, `setting`) VALUES
('websiteurl', 'https://smartkabir.com'),
('sitename', 'SmartKabir'),
('sitetitle', 'SmartKabir'),
('adminid', 'admin'),
('adminpassword', 'admin'),
('adminemail', 'change-me@smartkabir.com'),
('adminname', 'SmartKabir'),
('adminaddress', ''),
('leadsleapid', ''),
('paypalemail', ''),
('paypalitem', ''),
('price', ''),
('lastresponder', '2000-01-01'),
('maileremail', 'noreply@smartkabir.com'),
('lastmassmail', '1'),
('freedownloadurl', ''),
('unsub', '0'),
('otodownloadurl', ''),
('throttle', '8'),
('exitpopup', 'off'),
('exiturl', ''),
('exitpopup2', ''),
('exiturl2', '');

INSERT INTO `pages` (`name`, `htmlcode`) VALUES
('homepagetop', ''),
('homepagebottom', ''),
('homepagecall', ''),
('otopagetop', ''),
('otopagebottom', ''),
('otopagecall', '');

-- Two blank ad slots, as in the original install (cron.php expects at least two).
INSERT INTO `emailad` (`admessage`, `adstatus`) VALUES
('', 'on'),
('', 'on');

INSERT INTO `banners` (`bannerurl`, `targeturl`, `status`, `impressions`, `clicks`) VALUES
('', '', 1, 0, 0);

-- Payment settings for events (change them in Admin > Events > Payment settings).
INSERT INTO `event_config` (`name`, `value`) VALUES
('manual_enabled', '1'),
('manual_number', ''),
('manual_type', 'Personal'),
('manual_note', ''),
('bkash_enabled', '0'),
('bkash_mode', 'sandbox'),
('bkash_app_key', ''),
('bkash_app_secret', ''),
('bkash_username', ''),
('bkash_password', '');

-- Member accounts: one login per subscriber. Deleting the subscriber in the admin also deletes the login.
CREATE TABLE `member_accounts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `subscriber_id` int NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `failed_attempts` int NOT NULL DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_subscriber` (`subscriber_id`),
  UNIQUE KEY `uq_email` (`email`),
  CONSTRAINT `fk_member_subscriber` FOREIGN KEY (`subscriber_id`) REFERENCES `subscribers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;