schema.sql

-- CheapLocalDeals.com database schema
-- Reverse-engineered from includes/classes/tables/ and SQL usage across the codebase.
-- No original SQL dump existed in the repository; verify column types against a production
-- backup before deploying to production.
--
-- Usage:
--   mysql -u cheaploc_cld -p cheaploc_cld < sql/schema.sql

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- Geography and content
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS states (
  id INT AUTO_INCREMENT PRIMARY KEY,
  state VARCHAR(255),
  acn VARCHAR(10),
  page_header TEXT,
  page_footer TEXT,
  url_name INT,
  page_title VARCHAR(255),
  meta_description TEXT,
  meta_keywords TEXT,
  updated DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS cities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  state VARCHAR(10),
  city VARCHAR(255),
  type VARCHAR(50),
  page_header TEXT,
  page_footer TEXT,
  url_name INT,
  page_title VARCHAR(255),
  meta_description TEXT,
  meta_keywords TEXT,
  updated DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS zip_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  zip VARCHAR(20),
  latitude DECIMAL(10,6),
  longitude DECIMAL(10,6),
  url_name INT,
  views INT DEFAULT 0,
  updated DATETIME,
  city_id INT,
  INDEX idx_zip (zip),
  INDEX idx_city_id (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_name VARCHAR(255),
  sort_order INT,
  header TEXT,
  footer TEXT,
  header_title VARCHAR(255),
  meta_description TEXT,
  meta_keywords TEXT,
  zip_id INT,
  url_name INT,
  parent_category_id INT,
  image VARCHAR(255),
  map_marker VARCHAR(255),
  views INT DEFAULT 0,
  last_modified DATETIME,
  disabled TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS state_city_category (
  id INT AUTO_INCREMENT PRIMARY KEY,
  state INT,
  city INT,
  category INT,
  page_header TEXT,
  page_footer TEXT,
  url_name INT,
  page_title VARCHAR(255),
  meta_description TEXT,
  meta_keywords TEXT,
  updated DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS url_names (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(50),
  parent_id INT,
  url_name VARCHAR(255),
  INDEX idx_url_name (url_name),
  INDEX idx_type_parent (type, parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS pages (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  display_name VARCHAR(255),
  header_content TEXT,
  footer_content TEXT,
  header_title VARCHAR(255),
  meta_description TEXT,
  meta_keywords TEXT,
  dont_cache TINYINT(1) DEFAULT 0,
  url_name INT,
  updated DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS businesses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(10),
  zip VARCHAR(20),
  phone VARCHAR(50),
  email VARCHAR(255),
  url VARCHAR(255),
  description TEXT,
  latitude DECIMAL(10,6),
  longitude DECIMAL(10,6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS category_counts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  zip_code VARCHAR(20),
  count INT DEFAULT 0,
  UNIQUE KEY uk_category_zip (category_id, zip_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- Users and authentication
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255),
  password VARCHAR(255),
  created DATETIME,
  allowed_access TEXT,
  current_session VARCHAR(255),
  last_login DATE,
  login_ip VARCHAR(45)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS customer_info (
  id INT AUTO_INCREMENT PRIMARY KEY,
  longitude DECIMAL(10,6),
  latitude DECIMAL(10,6),
  password VARCHAR(255),
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  address_1 VARCHAR(255),
  address_2 VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(10),
  zip VARCHAR(20),
  phone_number VARCHAR(50),
  fax_number VARCHAR(50),
  email_address VARCHAR(255),
  account_enabled TINYINT(1) DEFAULT 1,
  date_created DATETIME,
  last_ip VARCHAR(45),
  last_login DATE,
  last_session_id VARCHAR(255),
  balance DECIMAL(10,2) DEFAULT 0,
  ref_code VARCHAR(255),
  api_id INT,
  usr_ref_code VARCHAR(255),
  header_data TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS customer_payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  credit_card_type VARCHAR(50),
  cc_number VARCHAR(255),
  cvv VARCHAR(10),
  cc_exp VARCHAR(20),
  check_routing_num VARCHAR(50),
  check_account_num VARCHAR(50),
  bank_name VARCHAR(255),
  bank_state VARCHAR(10),
  drivers_license_num VARCHAR(50),
  drivers_license_state VARCHAR(10),
  INDEX idx_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id VARCHAR(255),
  time INT,
  session_vars TEXT,
  cur_page TEXT,
  geo_loc VARCHAR(255),
  ip_address VARCHAR(45),
  referrer TEXT,
  INDEX idx_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- Advertisers
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS advertiser_levels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  level_name VARCHAR(255),
  level_weight INT,
  level_description TEXT,
  level_duration INT,
  level_upfront_cost DECIMAL(10,2),
  level_renewal_cost DECIMAL(10,2),
  upfront_level_link_back DECIMAL(10,2),
  upfront_bbb_member_price DECIMAL(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_info (
  id INT AUTO_INCREMENT PRIMARY KEY,
  approved TINYINT(1) DEFAULT 0,
  approval_date DATE,
  update_approval TINYINT(1) DEFAULT 0,
  company_name VARCHAR(255),
  customer_description TEXT,
  longitude DECIMAL(10,6),
  latitude DECIMAL(10,6),
  username VARCHAR(255),
  password VARCHAR(255),
  customer_level INT,
  customer_level_exp VARCHAR(255),
  customer_level_renewal_date DATE,
  customer_level_renew TINYINT(1),
  website VARCHAR(255),
  category INT,
  bbb_member TINYINT(1) DEFAULT 0,
  link_partner TINYINT(1) DEFAULT 0,
  affiliate_code VARCHAR(255),
  link_affiliate_code VARCHAR(255),
  hours_operation TEXT,
  products_services TEXT,
  payment_options TEXT,
  payment_method VARCHAR(255),
  certificate_levels TEXT,
  certificate_requirements TEXT,
  credit_card_type VARCHAR(50),
  cc_number VARCHAR(255),
  cvv VARCHAR(10),
  cc_exp VARCHAR(20),
  check_account_type VARCHAR(50),
  check_routing_num VARCHAR(50),
  check_account_num VARCHAR(50),
  bank_name VARCHAR(255),
  bank_state VARCHAR(10),
  drivers_license_num VARCHAR(50),
  drivers_license_state VARCHAR(10),
  hide_address TINYINT(1) DEFAULT 0,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  address_1 VARCHAR(255),
  address_2 VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(10),
  zip VARCHAR(20),
  phone_number VARCHAR(50),
  fax_number VARCHAR(50),
  email_address VARCHAR(255),
  account_enabled TINYINT(1) DEFAULT 1,
  image VARCHAR(255),
  date_created DATETIME,
  last_ip VARCHAR(45),
  last_login DATE,
  last_session_id VARCHAR(255),
  allow_multiple_logins TINYINT(1) DEFAULT 0,
  authorization_code VARCHAR(255),
  email_authorized TINYINT(1) DEFAULT 0,
  date_updated DATETIME,
  promo_code VARCHAR(255),
  allow_mult_loc TINYINT(1) DEFAULT 0,
  pick TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_info_backup (
  id INT AUTO_INCREMENT PRIMARY KEY,
  approved TINYINT(1) DEFAULT 0,
  approval_date DATE,
  update_approval TINYINT(1) DEFAULT 0,
  company_name VARCHAR(255),
  customer_description TEXT,
  longitude DECIMAL(10,6),
  latitude DECIMAL(10,6),
  username VARCHAR(255),
  password VARCHAR(255),
  customer_level INT,
  customer_level_exp VARCHAR(255),
  customer_level_renewal_date DATE,
  customer_level_renew TINYINT(1),
  website VARCHAR(255),
  category INT,
  bbb_member TINYINT(1) DEFAULT 0,
  link_partner TINYINT(1) DEFAULT 0,
  affiliate_code VARCHAR(255),
  link_affiliate_code VARCHAR(255),
  hours_operation TEXT,
  products_services TEXT,
  payment_options TEXT,
  payment_method VARCHAR(255),
  certificate_levels TEXT,
  certificate_requirements TEXT,
  credit_card_type VARCHAR(50),
  cc_number VARCHAR(255),
  cvv VARCHAR(10),
  cc_exp VARCHAR(20),
  check_account_type VARCHAR(50),
  check_routing_num VARCHAR(50),
  check_account_num VARCHAR(50),
  bank_name VARCHAR(255),
  bank_state VARCHAR(10),
  drivers_license_num VARCHAR(50),
  drivers_license_state VARCHAR(10),
  hide_address TINYINT(1) DEFAULT 0,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  address_1 VARCHAR(255),
  address_2 VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(10),
  zip VARCHAR(20),
  phone_number VARCHAR(50),
  fax_number VARCHAR(50),
  email_address VARCHAR(255),
  account_enabled TINYINT(1) DEFAULT 1,
  image VARCHAR(255),
  date_created DATETIME,
  last_ip VARCHAR(45),
  last_login DATE,
  last_session_id VARCHAR(255),
  allow_multiple_logins TINYINT(1) DEFAULT 0,
  authorization_code VARCHAR(255),
  email_authorized TINYINT(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  advertiser_id INT,
  INDEX idx_advertiser_id (advertiser_id),
  INDEX idx_category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_categories_backup (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  advertiser_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_alt_locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  advertiser_id INT,
  enabled TINYINT(1) DEFAULT 1,
  location_name VARCHAR(255),
  hours_of_operation TEXT,
  products_services TEXT,
  description TEXT,
  hide_address TINYINT(1) DEFAULT 0,
  payment_options TEXT,
  website VARCHAR(255),
  address_1 VARCHAR(255),
  address_2 VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(10),
  zip VARCHAR(20),
  phone_number VARCHAR(50),
  fax_number VARCHAR(50),
  email_address VARCHAR(255),
  longitude DECIMAL(10,6),
  latitude DECIMAL(10,6),
  INDEX idx_advertiser_id (advertiser_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  advertiser_id INT,
  advertiser_alt_id INT,
  rating TEXT,
  review TEXT,
  approved TINYINT(1) DEFAULT 0,
  added DATETIME,
  INDEX idx_advertiser_id (advertiser_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advertiser_payment_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  method VARCHAR(255),
  image VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS membership_process (
  id INT AUTO_INCREMENT PRIMARY KEY,
  advertiser_id INT,
  advertiser_level INT,
  date DATETIME,
  payment DECIMAL(10,2),
  payment_method VARCHAR(255),
  cc_type VARCHAR(50),
  payment_approved TINYINT(1),
  other_info TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS membership_process_failed (
  id INT AUTO_INCREMENT PRIMARY KEY,
  advertiser_id INT,
  advertiser_level INT,
  date DATETIME,
  payment DECIMAL(10,2),
  payment_method VARCHAR(255),
  cc_type VARCHAR(50),
  payment_approved TINYINT(1),
  other_info TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- Commerce
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS certificate_amount (
  id INT AUTO_INCREMENT PRIMARY KEY,
  discount_amount DECIMAL(10,2),
  cost DECIMAL(10,2),
  min_spend_amts DECIMAL(10,2),
  crtamt_sort INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  applicable_taxes DECIMAL(10,2),
  order_total DECIMAL(10,2),
  payment_method VARCHAR(255),
  credit_card_type VARCHAR(50),
  credit_card_number VARCHAR(255),
  cvv VARCHAR(10),
  expiration_date VARCHAR(20),
  date_added DATETIME,
  payment_approved TINYINT(1),
  order_notes TEXT,
  promo_code VARCHAR(255),
  api_id INT,
  INDEX idx_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  item_id INT,
  certificate_value_id INT,
  item_type VARCHAR(50),
  item_value DECIMAL(10,2),
  item_quantity INT,
  item_start_date DATE,
  item_expiration_date DATE,
  INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS certificate_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  customer_id INT,
  advertiser_id INT,
  requirements TEXT,
  certificate_amount_id INT,
  certificate_code VARCHAR(20),
  enabled TINYINT(1) DEFAULT 1,
  date_added DATETIME,
  cert_id VARCHAR(255),
  session_id VARCHAR(255),
  token VARCHAR(255),
  excludes TEXT,
  INDEX idx_cert_id (cert_id),
  INDEX idx_customer_id (customer_id),
  INDEX idx_advertiser_id (advertiser_id),
  INDEX idx_certificate_code (certificate_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS paypal_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id VARCHAR(255),
  token VARCHAR(255),
  amount DECIMAL(10,2),
  date DATETIME,
  approved TINYINT(1),
  api_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS customer_promo_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  promo_code VARCHAR(255),
  percentage DECIMAL(10,2),
  updated DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS advert_promo_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  promo_code VARCHAR(255),
  percentage DECIMAL(10,2),
  updated DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS customer_coupons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(255),
  value DECIMAL(10,2),
  expires DATE,
  used TINYINT(1) DEFAULT 0,
  used_by_cust_id INT,
  used_date DATETIME,
  added DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS customer_referel_info (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  ref_code VARCHAR(255),
  credit_amt DECIMAL(10,2),
  date_added DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- Marketing and API
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS affiliate_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  company VARCHAR(255),
  affiliate_code VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS api_access (
  id INT AUTO_INCREMENT PRIMARY KEY,
  apikey VARCHAR(255),
  name VARCHAR(255),
  image VARCHAR(255),
  website VARCHAR(255),
  show_address TINYINT(1) DEFAULT 0,
  address VARCHAR(255),
  address1 VARCHAR(255),
  city VARCHAR(255),
  state VARCHAR(10),
  zip VARCHAR(20),
  enabled TINYINT(1) DEFAULT 1,
  hide_header TINYINT(1) DEFAULT 0,
  hide_footer TINYINT(1) DEFAULT 0,
  show_all TINYINT(1) DEFAULT 0,
  password VARCHAR(255),
  ptpassword VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS api_ref_track (
  id INT AUTO_INCREMENT PRIMARY KEY,
  api_id INT,
  sess_id VARCHAR(255),
  customer_id INT,
  ref_page VARCHAR(255),
  page VARCHAR(255),
  cart TEXT,
  cart_total DECIMAL(10,2),
  longit DECIMAL(10,6),
  latt DECIMAL(10,6),
  date_time DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ---------------------------------------------------------------------------
-- Site configuration and analytics
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS settings_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description TEXT,
  sort_order INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  group_id INT,
  title VARCHAR(255),
  description TEXT,
  type VARCHAR(50),
  value TEXT,
  constant VARCHAR(255),
  INDEX idx_group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS fof_errors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  link TEXT,
  added DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS page_hits (
  id INT AUTO_INCREMENT PRIMARY KEY,
  link TEXT,
  added DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;