-- cpenginev2 schema dump
-- Generated: 2026-03-29 21:00:53

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table: cpe_agent_messages
-- ----------------------------
CREATE TABLE `cpe_agent_messages` (
  `msgid` int(11) NOT NULL AUTO_INCREMENT,
  `sessionid` int(11) NOT NULL,
  `agentid` int(11) NOT NULL DEFAULT 0,
  `role` enum('user','assistant') NOT NULL,
  `content` longtext DEFAULT NULL,
  `latency_ms` int(11) DEFAULT NULL,
  `input_tokens` int(11) DEFAULT NULL,
  `output_tokens` int(11) DEFAULT NULL,
  `cost_usd` decimal(10,6) DEFAULT NULL,
  `createdate` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`msgid`),
  KEY `idx_sessionid` (`sessionid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Table: cpe_agent_sessions
-- ----------------------------
CREATE TABLE `cpe_agent_sessions` (
  `sessionid` int(11) NOT NULL AUTO_INCREMENT,
  `agentid` int(11) NOT NULL DEFAULT 0,
  `orgid` int(11) NOT NULL DEFAULT 0,
  `crid` int(11) DEFAULT NULL COMMENT 'cpe_creditpulls.crid — NULL = demo/mock mode',
  `provider` varchar(50) DEFAULT NULL COMMENT 'anthropic, openai, etc.',
  `model` varchar(100) DEFAULT NULL,
  `message_count` int(11) NOT NULL DEFAULT 0,
  `createdate` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedate` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`sessionid`),
  KEY `idx_agentid` (`agentid`),
  KEY `idx_crid` (`crid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- ----------------------------
-- Table: cpe_aiassistant
-- ----------------------------
CREATE TABLE `cpe_aiassistant` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `agentid` int(11) DEFAULT 0,
  `orgid` int(11) DEFAULT 0,
  `crid` int(11) DEFAULT 0,
  `promptid` int(11) DEFAULT 0,
  `response` mediumtext DEFAULT NULL,
  `jsonrequest` mediumtext DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- ----------------------------
-- Table: cpe_creditpulls
-- ----------------------------
CREATE TABLE `cpe_creditpulls` (
  `crid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(1) DEFAULT 0,
  `agentid` int(1) DEFAULT 0,
  `priceid` varchar(255) DEFAULT '0',
  `invid` int(5) DEFAULT 0,
  `profid` varchar(255) DEFAULT '0',
  `custname` varchar(255) DEFAULT '0',
  `custdob` varchar(255) DEFAULT '0',
  `last4ss` varchar(255) DEFAULT '0',
  `success` int(5) DEFAULT 0,
  `apicall` int(1) NOT NULL DEFAULT 0,
  `cpejson` text DEFAULT NULL,
  `createdate` datetime NOT NULL DEFAULT current_timestamp(),
  `status` int(5) DEFAULT 0,
  `active` int(1) DEFAULT 1,
  PRIMARY KEY (`crid`),
  KEY `idx_org_history_id_orgid` (`crid`,`orgid`),
  KEY `idx_org_history_id_orgid_custID` (`crid`,`orgid`)
) ENGINE=MyISAM AUTO_INCREMENT=1140 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: cpe_creditpulls_temp
-- ----------------------------
CREATE TABLE `cpe_creditpulls_temp` (
  `cpeid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) DEFAULT 0,
  `evni` varchar(255) DEFAULT '0',
  `rawjson` mediumtext DEFAULT NULL,
  `directory` varchar(255) DEFAULT '0',
  `jsonfilename` varchar(255) DEFAULT '0',
  `createdate` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`cpeid`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- ----------------------------
-- Table: cpe_org_pricing
-- ----------------------------
CREATE TABLE `cpe_org_pricing` (
  `priceid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) NOT NULL,
  `package_name` varchar(100) DEFAULT 'Credit Report Package',
  `retail_price` decimal(6,2) NOT NULL COMMENT 'Total price client pays',
  `our_price` decimal(6,2) NOT NULL COMMENT 'What all services cost us combined',
  `includes_fico` tinyint(1) DEFAULT 0,
  `includes_vantage` tinyint(1) DEFAULT 0,
  `includes_idscan` tinyint(1) DEFAULT 0,
  `includes_directory` tinyint(1) DEFAULT 0,
  `active` tinyint(1) DEFAULT 1,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`priceid`),
  UNIQUE KEY `orgid` (`orgid`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: cpe_orgs_documents
-- ----------------------------
CREATE TABLE `cpe_orgs_documents` (
  `docid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) DEFAULT 0,
  `userid` int(11) DEFAULT 0,
  `document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `type` varchar(255) DEFAULT '0',
  `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `status` int(11) DEFAULT 0,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`docid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Table: cpe_products
-- ----------------------------
CREATE TABLE `cpe_products` (
  `prodid` int(11) NOT NULL AUTO_INCREMENT,
  `provider` varchar(20) DEFAULT '0',
  `product_code` varchar(20) NOT NULL DEFAULT '0' COMMENT 'CR, FICO, VANTAGE, IDSCAN, DIRECTORY',
  `product_name` varchar(100) NOT NULL DEFAULT '0',
  `our_cost` decimal(6,2) NOT NULL DEFAULT 0.00 COMMENT 'What we pay Equifax/TransUnion',
  `is_base` tinyint(1) DEFAULT 0 COMMENT '1 for Credit Report base, 0 for add-ons',
  `active` tinyint(1) DEFAULT 1,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`prodid`),
  UNIQUE KEY `product_code` (`product_code`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: cpe_tempreports
-- ----------------------------
CREATE TABLE `cpe_tempreports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agentid` int(11) DEFAULT NULL,
  `orgid` int(11) DEFAULT NULL,
  `template` varchar(255) DEFAULT '0',
  `ceatedate` datetime DEFAULT current_timestamp(),
  `active` int(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_activitylog
-- ----------------------------
CREATE TABLE `sp_activitylog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `trigval` varchar(255) DEFAULT NULL,
  `controller` varchar(64) DEFAULT NULL,
  `action` varchar(64) DEFAULT NULL,
  `method` varchar(8) DEFAULT NULL,
  `useragent` varchar(512) DEFAULT NULL,
  `device_type` varchar(16) DEFAULT NULL,
  `browser` varchar(64) DEFAULT NULL,
  `os` varchar(64) DEFAULT NULL,
  `msgval` text DEFAULT NULL,
  `device` varchar(255) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `ipaddress` varchar(255) DEFAULT '000.000.000',
  `public` int(11) DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_activitylog_userid_trigval_date` (`userid`,`trigval`,`createdate` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_api_requests
-- ----------------------------
CREATE TABLE `sp_api_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `apikeyid` varchar(11) DEFAULT '0',
  `userid` int(11) DEFAULT 0,
  `requesting_ip` varchar(255) DEFAULT '0',
  `request` varchar(255) DEFAULT '0',
  `service` varchar(255) DEFAULT '0',
  `apitype` varchar(255) DEFAULT '0' COMMENT 'tracker, or api',
  `sitepage` varchar(255) DEFAULT '0',
  `useragent` varchar(255) DEFAULT '0',
  `sessionid` varchar(255) DEFAULT '0',
  `sessiontime` varchar(255) DEFAULT '0',
  `visitduration` varchar(255) DEFAULT '0',
  `devicetype` varchar(255) DEFAULT '0',
  `domain` varchar(255) DEFAULT '0',
  `paramsvar` varchar(255) DEFAULT '0',
  `referral` varchar(255) DEFAULT '0',
  `language` varchar(255) DEFAULT '0',
  `ismobile` varchar(255) DEFAULT '0',
  `hascookies` varchar(255) DEFAULT '0',
  `scrolldepth` varchar(255) DEFAULT '0',
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_userid_createdate` (`userid`,`createdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_api_usage
-- ----------------------------
CREATE TABLE `sp_api_usage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `requests` int(11) NOT NULL DEFAULT 0,
  `report_date` date NOT NULL,
  `report_type` enum('daily','monthly','yearly') NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_date_type` (`userid`,`report_date`,`report_type`),
  KEY `idx_report_date` (`report_date`),
  KEY `idx_report_type` (`report_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table: sp_broker_commissions
-- ----------------------------
CREATE TABLE `sp_broker_commissions` (
  `commission_id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT 0,
  `orgid` int(11) DEFAULT 0,
  `crid` int(11) DEFAULT 0,
  `pull_date` datetime DEFAULT '0000-00-00 00:00:00',
  `retail_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `commission_type` varchar(255) DEFAULT '0',
  `commission_rate` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `flat_fee_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `commission_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `payout_eligible_date` datetime DEFAULT '0000-00-00 00:00:00',
  `payout_id` int(11) DEFAULT 0,
  `paid_date` datetime DEFAULT '0000-00-00 00:00:00',
  `status` int(11) DEFAULT 0,
  `notes` varchar(255) DEFAULT '0',
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`commission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_broker_orgs
-- ----------------------------
CREATE TABLE `sp_broker_orgs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT 0,
  `orgid` int(11) DEFAULT 0,
  `commission_override_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `commission_override_rate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `flat_fee_override` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `start_date` datetime DEFAULT '0000-00-00 00:00:00',
  `end_date` datetime DEFAULT '0000-00-00 00:00:00',
  `assigned_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `notes` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT '0',
  `createdate` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Table: sp_broker_payouts
-- ----------------------------
CREATE TABLE `sp_broker_payouts` (
  `payout_id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT NULL,
  `payout_period_start` datetime DEFAULT '0000-00-00 00:00:00',
  `payout_period_end` datetime DEFAULT '0000-00-00 00:00:00',
  `total_commissions` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `payment_method` varchar(255) DEFAULT NULL,
  `payment_reference` varchar(255) DEFAULT NULL,
  `payment_date` datetime DEFAULT '0000-00-00 00:00:00',
  `status` int(11) DEFAULT NULL,
  `processed_by` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  `updatedate` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  PRIMARY KEY (`payout_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_countries
-- ----------------------------
CREATE TABLE `sp_countries` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(2) NOT NULL COMMENT 'Two-letter country code (ISO 3166-1 alpha-2)',
  `name` varchar(64) NOT NULL COMMENT 'English country name',
  `full_name` varchar(128) NOT NULL COMMENT 'Full English country name',
  `iso3` char(3) NOT NULL COMMENT 'Three-letter country code (ISO 3166-1 alpha-3)',
  `number` smallint(3) unsigned zerofill NOT NULL COMMENT 'Three-digit country number (ISO 3166-1 numeric)',
  `continent_code` char(2) NOT NULL,
  `display_order` int(11) NOT NULL DEFAULT 900,
  PRIMARY KEY (`country_id`),
  UNIQUE KEY `idx_code` (`code`) USING BTREE,
  KEY `idx_continent_code` (`continent_code`) USING BTREE,
  CONSTRAINT `sp_countries_ibfk_1` FOREIGN KEY (`continent_code`) REFERENCES `snpi_continents` (`code`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci ROW_FORMAT=COMPACT;

-- ----------------------------
-- Table: sp_cron_tasks
-- ----------------------------
CREATE TABLE `sp_cron_tasks` (
  `cronid` int(11) NOT NULL AUTO_INCREMENT,
  `keyval` varchar(255) DEFAULT '0',
  `varval` varchar(255) DEFAULT '0',
  `task` varchar(255) DEFAULT '0',
  `response` text DEFAULT NULL,
  `completed` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `created` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`cronid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_email_templates
-- ----------------------------
CREATE TABLE `sp_email_templates` (
  `emailtemplateid` int(11) NOT NULL AUTO_INCREMENT,
  `type` mediumtext NOT NULL,
  `slug` varchar(100) NOT NULL,
  `language` varchar(40) DEFAULT NULL,
  `name` mediumtext NOT NULL,
  `subject` mediumtext NOT NULL,
  `message` mediumtext NOT NULL,
  `fromname` mediumtext NOT NULL,
  `fromemail` varchar(100) DEFAULT NULL,
  `plaintext` int(11) NOT NULL DEFAULT 0,
  `active` tinyint(4) NOT NULL DEFAULT 0,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`emailtemplateid`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_master
-- ----------------------------
CREATE TABLE `sp_master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '''0''',
  `jvalue` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_menu_items
-- ----------------------------
CREATE TABLE `sp_menu_items` (
  `item_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `menu_id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `label` varchar(100) NOT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `type` enum('link','header','divider') DEFAULT 'link',
  `url` varchar(255) DEFAULT NULL,
  `perm_id` int(10) unsigned DEFAULT NULL,
  `role_id` int(10) unsigned DEFAULT NULL,
  `match_prefix` tinyint(4) DEFAULT 0,
  `sort_order` int(11) DEFAULT 0,
  `active` tinyint(4) DEFAULT 1,
  PRIMARY KEY (`item_id`),
  KEY `menu_id` (`menu_id`),
  KEY `parent_id` (`parent_id`),
  KEY `perm_id` (`perm_id`),
  KEY `fk_menu_item_role` (`role_id`),
  CONSTRAINT `fk_menu_item_role` FOREIGN KEY (`role_id`) REFERENCES `sp_roles` (`role_id`) ON DELETE SET NULL,
  CONSTRAINT `sp_menu_items_ibfk_1` FOREIGN KEY (`perm_id`) REFERENCES `sp_permissions` (`perm_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table: sp_menus
-- ----------------------------
CREATE TABLE `sp_menus` (
  `menu_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `active` tinyint(4) DEFAULT 1,
  PRIMARY KEY (`menu_id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table: sp_orgs
-- ----------------------------
CREATE TABLE `sp_orgs` (
  `orgid` int(11) NOT NULL AUTO_INCREMENT,
  `catid` int(11) DEFAULT 1,
  `name` varchar(255) DEFAULT '0',
  `address` varchar(255) DEFAULT '0',
  `address2` varchar(255) DEFAULT '0',
  `city` varchar(255) DEFAULT '0',
  `state` varchar(255) DEFAULT '0',
  `zip` int(11) DEFAULT 0,
  `country` varchar(255) DEFAULT NULL,
  `countrycode` varchar(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT '0',
  `email` varchar(255) DEFAULT '0',
  `logo` varchar(255) DEFAULT '0',
  `website` varchar(255) DEFAULT '0',
  `json_social_media` text DEFAULT NULL,
  `summary` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `updated_date` datetime DEFAULT '0000-00-00 00:00:00',
  `taxid` varchar(255) DEFAULT '0',
  `defaultorg` int(11) DEFAULT 0,
  `creditlimit` varchar(20) DEFAULT '0',
  `active` int(11) DEFAULT 1,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`orgid`)
) ENGINE=MyISAM AUTO_INCREMENT=199 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_orgs_category
-- ----------------------------
CREATE TABLE `sp_orgs_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'https://developer.bigcommerce.com/api-reference/9cc3a53863922-get-all-categories',
  `name` varchar(255) DEFAULT '0',
  `parentid` int(11) DEFAULT 0,
  `description` text DEFAULT NULL,
  `pagetitle` varchar(255) DEFAULT '0',
  `image` varchar(255) DEFAULT '0',
  `searchkeywords` text DEFAULT NULL,
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_orgs_meta
-- ----------------------------
CREATE TABLE `sp_orgs_meta` (
  `orgmetaid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) DEFAULT 0,
  `keyval` varchar(255) DEFAULT '0',
  `metval` text DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  `active` int(1) DEFAULT 1,
  PRIMARY KEY (`orgmetaid`)
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_orgs_users_join
-- ----------------------------
CREATE TABLE `sp_orgs_users_join` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT 0,
  `orgid` int(11) DEFAULT 0,
  `orgrole` int(11) DEFAULT 0 COMMENT 'orgrole is temp.. Not sure how i want to handle this.. i don''t want multiple role tables.',
  `orgadmin` int(11) DEFAULT 0 COMMENT 'orgadmin are account that have access to modifying the org information.',
  `guest` int(11) DEFAULT 0 COMMENT 'if user is a guest, or invited into the org. OrgAdmin can''t delete or modify the account. Another way of thinking about it is ownership. Org Doesn''t have Ownership of the account. Can''t modify it.',
  `created_date` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=37 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_permissions
-- ----------------------------
CREATE TABLE `sp_permissions` (
  `perm_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `perm_controller` varchar(50) NOT NULL DEFAULT '0',
  `perm_action` varchar(50) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT '0',
  `viewroleid` varchar(255) DEFAULT '0' COMMENT 'role that can see these options',
  `menu` int(11) DEFAULT 0,
  `menuorder` int(11) DEFAULT 0,
  PRIMARY KEY (`perm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_plugins
-- ----------------------------
CREATE TABLE `sp_plugins` (
  `plugin_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `slug` varchar(64) NOT NULL,
  `name` varchar(128) NOT NULL,
  `version` varchar(20) NOT NULL DEFAULT '1.0.0',
  `description` text DEFAULT NULL,
  `author` varchar(128) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 0,
  `installed_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`plugin_id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table: sp_referral
-- ----------------------------
CREATE TABLE `sp_referral` (
  `refid` int(11) NOT NULL AUTO_INCREMENT,
  `refcode` varchar(255) DEFAULT '0',
  `refurl` varchar(255) DEFAULT '0',
  `ipaddress` varchar(255) DEFAULT '0',
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`refid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_role_perm
-- ----------------------------
CREATE TABLE `sp_role_perm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(10) unsigned NOT NULL DEFAULT 0,
  `perm_id` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `role_id` (`role_id`),
  KEY `perm_id` (`perm_id`),
  CONSTRAINT `sp_role_perm_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `sp_roles` (`role_id`),
  CONSTRAINT `sp_role_perm_ibfk_2` FOREIGN KEY (`perm_id`) REFERENCES `sp_permissions` (`perm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_roles
-- ----------------------------
CREATE TABLE `sp_roles` (
  `role_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `view` varchar(100) DEFAULT NULL,
  `admin` int(11) DEFAULT NULL,
  `onteam` int(11) DEFAULT NULL,
  `menu` text DEFAULT NULL,
  `defaultrole` int(11) DEFAULT NULL COMMENT 'default roleid is for the system to know which id to default the user to.',
  `hidemenu` int(11) DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_saas_invoice_activity
-- ----------------------------
CREATE TABLE `sp_saas_invoice_activity` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invid` int(11) NOT NULL,
  `orgid` int(11) NOT NULL DEFAULT 0,
  `event_type` varchar(64) NOT NULL COMMENT 'created, payment_attempted, payment_succeeded, payment_failed, viewed, etc.',
  `label` varchar(255) NOT NULL,
  `note` varchar(512) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_invid` (`invid`),
  KEY `idx_orgid` (`orgid`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Table: sp_saas_invoice_items
-- ----------------------------
CREATE TABLE `sp_saas_invoice_items` (
  `itemid` int(11) NOT NULL AUTO_INCREMENT,
  `invid` int(11) DEFAULT 0,
  `priceid` varchar(255) DEFAULT '0',
  `description` varchar(255) DEFAULT '0',
  `amount` decimal(10,2) DEFAULT 0.00,
  `quantity` int(11) DEFAULT 0,
  `void` decimal(10,2) DEFAULT 0.00,
  `total` decimal(10,2) DEFAULT 0.00,
  `createdate` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`itemid`),
  KEY `idx_invid` (`invid`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_saas_invoices
-- ----------------------------
CREATE TABLE `sp_saas_invoices` (
  `invid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) NOT NULL DEFAULT 0,
  `subid` varchar(255) DEFAULT '0',
  `sinvid` varchar(255) DEFAULT '0',
  `stripe_pmtid` varchar(255) NOT NULL DEFAULT '' COMMENT 'pm_xxx that was charged',
  `description` varchar(255) DEFAULT '0',
  `amountdue` decimal(10,2) DEFAULT 0.00,
  `paid` decimal(10,2) DEFAULT 0.00,
  `paidlast4` varchar(255) DEFAULT '0',
  `paidtype` varchar(255) DEFAULT '0',
  `balance` decimal(10,2) DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  `hostedinv` varchar(255) DEFAULT '0',
  `effective_at` datetime DEFAULT '0000-00-00 00:00:00',
  `due_date` datetime DEFAULT NULL,
  `paid_at` datetime DEFAULT NULL,
  `canceled_at` datetime DEFAULT '0000-00-00 00:00:00',
  `createdate` datetime DEFAULT current_timestamp(),
  `status` varchar(255) DEFAULT 'draft' COMMENT 'this will be status like Pending, Paid, Refunded etc..',
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`invid`),
  KEY `idx_orgid` (`orgid`),
  KEY `idx_subid` (`subid`(191)),
  KEY `idx_sinvid` (`sinvid`(191)),
  KEY `idx_status` (`status`(30))
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_saas_plans
-- ----------------------------
CREATE TABLE `sp_saas_plans` (
  `planid` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) DEFAULT '0',
  `codemonth` varchar(255) DEFAULT NULL,
  `codeyear` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `html` mediumtext DEFAULT NULL,
  `monthly` decimal(6,2) DEFAULT 0.00,
  `yearly` decimal(6,2) DEFAULT 0.00,
  `price` decimal(6,2) DEFAULT 0.00,
  `limit_minute` int(11) DEFAULT 0,
  `credits` int(11) DEFAULT 0,
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`planid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_saas_pmtmethods
-- ----------------------------
CREATE TABLE `sp_saas_pmtmethods` (
  `pmtid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) NOT NULL DEFAULT 0,
  `stripe_cus_id` varchar(255) NOT NULL DEFAULT '',
  `userid` int(11) DEFAULT 0,
  `spmid` varchar(255) DEFAULT '0' COMMENT 'Stripe Payment Method ID',
  `cardname` varchar(255) DEFAULT '0',
  `cardtype` varchar(255) DEFAULT '0',
  `last4` varchar(255) DEFAULT '0',
  `exp_month` tinyint(2) NOT NULL DEFAULT 0,
  `exp_year` smallint(4) NOT NULL DEFAULT 0,
  `billingzip` varchar(20) NOT NULL DEFAULT '',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `createdate` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`pmtid`),
  UNIQUE KEY `idx_spmid` (`spmid`(191)),
  KEY `idx_orgid` (`orgid`),
  KEY `idx_userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_saas_stripe_response
-- ----------------------------
CREATE TABLE `sp_saas_stripe_response` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) NOT NULL DEFAULT 0,
  `event_type` varchar(100) NOT NULL DEFAULT '' COMMENT 'e.g. charge.succeeded, invoice.paid',
  `userid` int(11) DEFAULT 0,
  `name` varchar(255) DEFAULT '0',
  `email` varchar(255) DEFAULT '0',
  `strid` varchar(255) DEFAULT '0',
  `requtype` varchar(255) DEFAULT '0',
  `amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `chargeid` varchar(255) DEFAULT '0',
  `chargecode` varchar(255) DEFAULT '0',
  `chargereason` varchar(255) DEFAULT '0',
  `chargemsg` varchar(255) DEFAULT '0',
  `cardtype` varchar(255) DEFAULT '0',
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_orgid` (`orgid`),
  KEY `idx_event_type` (`event_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_saas_subscription
-- ----------------------------
CREATE TABLE `sp_saas_subscription` (
  `subid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) NOT NULL DEFAULT 0,
  `userid` int(11) DEFAULT 0,
  `planid` int(11) DEFAULT 0,
  `stsubid` varchar(255) DEFAULT '0',
  `status` varchar(30) NOT NULL DEFAULT 'active' COMMENT 'active | canceled | past_due | trialing | paused',
  `trial_ends_at` datetime DEFAULT NULL,
  `current_period_start` datetime DEFAULT NULL,
  `current_period_end` datetime DEFAULT NULL,
  `cancels_at` date DEFAULT '0000-00-00',
  `cancelled_at` datetime DEFAULT '0000-00-00 00:00:00',
  `reasoncancelled` varchar(255) DEFAULT '0',
  `credits_usage` int(11) DEFAULT 0,
  `bonus_credits` int(11) DEFAULT 0,
  `nextbilling` datetime DEFAULT '0000-00-00 00:00:00',
  `createdate` datetime DEFAULT current_timestamp(),
  `updatedate` datetime DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `active` int(11) DEFAULT 0,
  PRIMARY KEY (`subid`),
  KEY `idx_orgid` (`orgid`),
  KEY `idx_userid` (`userid`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=487 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- ----------------------------
-- Table: sp_settings
-- ----------------------------
CREATE TABLE `sp_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyval` varchar(255) DEFAULT '0',
  `group` varchar(100) NOT NULL DEFAULT 'general',
  `metval` text DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 0,
  `encrypted` tinyint(1) NOT NULL DEFAULT 0,
  `createdate` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_keyval` (`keyval`),
  KEY `idx_group` (`group`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_states
-- ----------------------------
CREATE TABLE `sp_states` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` char(2) NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=60 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_users
-- ----------------------------
CREATE TABLE `sp_users` (
  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT '0',
  `password` varchar(64) DEFAULT '0',
  `email` varchar(255) DEFAULT '0',
  `avatar` varchar(255) DEFAULT '0',
  `autho` varchar(255) DEFAULT '0' COMMENT 'this is for google authorization',
  `mobile` varchar(255) DEFAULT '0' COMMENT 'otp authorization or 2step',
  `address` varchar(255) NOT NULL DEFAULT '',
  `city` varchar(255) NOT NULL DEFAULT '',
  `state` varchar(255) NOT NULL DEFAULT '',
  `zip` varchar(20) NOT NULL DEFAULT '',
  `country` varchar(255) NOT NULL DEFAULT '',
  `fname` varchar(255) DEFAULT '0',
  `lname` varchar(255) DEFAULT '0',
  `role_id` int(10) unsigned DEFAULT 2,
  `sysadmin` int(11) DEFAULT 0,
  `lastlogin` datetime DEFAULT '0000-00-00 00:00:00',
  `created_date` datetime DEFAULT current_timestamp(),
  `updated_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `ipaddress` varchar(255) DEFAULT '000.000.000.000',
  `pwcodedate` datetime DEFAULT '0000-00-00 00:00:00',
  `pwcode` varchar(255) DEFAULT '0',
  `verify` varchar(255) DEFAULT '0',
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `username` (`username`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `sp_users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `sp_roles` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2566 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_users_api
-- ----------------------------
CREATE TABLE `sp_users_api` (
  `apiid` int(11) NOT NULL AUTO_INCREMENT,
  `orgid` int(11) DEFAULT 0,
  `userid` int(11) DEFAULT 0,
  `projectid` int(11) DEFAULT 0,
  `apikey` varchar(100) DEFAULT NULL,
  `apiname` varchar(255) DEFAULT '0',
  `website` varchar(255) DEFAULT '0',
  `description` text DEFAULT NULL,
  `whitelist` text DEFAULT NULL,
  `lockdomain` int(11) DEFAULT 0,
  `createdate` datetime DEFAULT current_timestamp(),
  `active` int(11) DEFAULT 1,
  PRIMARY KEY (`apiid`),
  UNIQUE KEY `ApiDups` (`apikey`)
) ENGINE=InnoDB AUTO_INCREMENT=5500 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: sp_users_meta
-- ----------------------------
CREATE TABLE `sp_users_meta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) DEFAULT 0,
  `keyval` varchar(255) DEFAULT '0',
  `metval` text DEFAULT NULL,
  `createdate` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- ----------------------------
-- Table: tmp_session
-- ----------------------------
CREATE TABLE `tmp_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(255) DEFAULT '000.000.000.000',
  `lastcall` datetime DEFAULT current_timestamp(),
  `created_date` datetime DEFAULT current_timestamp(),
  `count` int(11) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

SET FOREIGN_KEY_CHECKS=1;