-- Create qf_categories table

CREATE TABLE `qf_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) DEFAULT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `status` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- Create qf_manufacturers table

CREATE TABLE `qf_manufacturers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `man_name` varchar(200) DEFAULT NULL,
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


-- Create qf_products table
CREATE TABLE `qf_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `display_name` varchar(200) DEFAULT NULL,
  `slug` varchar(250) DEFAULT NULL,
  `description` longtext,
  `model` varchar(2000) DEFAULT NULL,
  `manufacturer_id` int(20) DEFAULT NULL,
  `meta_title` text,
  `meta_description` text,
  `meta_keywords` text,
  `status` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create qf_product_data table

CREATE TABLE `qf_product_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL DEFAULT '0',
  `min_qty_in_order` int(11) NOT NULL DEFAULT '1',
  `max_qty_in_order` int(11) NOT NULL DEFAULT '10000',
  `mrp` varchar(20) DEFAULT NULL,
  `selling_price` varchar(20) DEFAULT NULL,
  `in_stock` enum('0','1') NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create qf_prod_category_mapping table

CREATE TABLE `qf_prod_category_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create qf_content_repository table

 CREATE TABLE `qf_content_repository` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) NOT NULL,
  `content_type` varchar(255) DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `unique_name` varchar(255) NOT NULL,
  `file_extension` varchar(255) NOT NULL,
  `mime_type` varchar(2048) DEFAULT NULL,
  `file_path` text NOT NULL,
  `upload_status` varchar(255) DEFAULT 'n',
  `size` int(11) NOT NULL COMMENT 'Size in bytes go here',
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL,
  `in_use` varchar(255) DEFAULT 'y',
  `is_deleted` varchar(255) DEFAULT 'n',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_name` (`unique_name`),
  KEY `qfcontentrepocontent_id` (`content_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create qf_domains table for custom qfree domains

CREATE TABLE `qf_domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `domain_name` varchar(1000) NOT NULL,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` enum('a','i') DEFAULT 'a',
  PRIMARY KEY (`id`),
  UNIQUE KEY `merchant_id` (`merchant_id`),
  UNIQUE KEY `domain_name` (`domain_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Drop table qf_manufacturers
drop table qf_manufacturers;


-- Add merchant_id in qf_prod_category_mapping
alter table qf_prod_category_mapping add column merchant_id int not null after category_id;

-- Table for saving user details like address, etc.

CREATE TABLE `qf_user_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address_1` text NOT NULL,
  `address_2` text,
  `city` varchar(255) DEFAULT NULL,
  `pincode` varchar(12) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `alt_mob` varchar(20) DEFAULT NULL,
  `remarks` text,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table for qfree user addresses';

-- Create orders table
CREATE TABLE `qf_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `order_slug` varchar(50) NOT NULL,
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create qf_order_items table
CREATE TABLE `qf_order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `prod_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `amt` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Multiple status flags in qf_orders; p:pending, d:delivered, od:out for delivery, a:accepted, r:rejected
alter table qf_orders modify column status enum ('p','d','od','a','r');


-- Create table qf_attributes
CREATE TABLE `qf_attributes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `attr_name` varchar(255) DEFAULT NULL,
  `priority` int(11) NOT NULL,
  `inner_filter` enum('y','n') NOT NULL DEFAULT 'n',
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create qf_prod_attr_mapping
CREATE TABLE `qf_prod_attr_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` int(11) NOT NULL,
  `attr_id` int(11) NOT NULL,
  `attr_value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

-- Add merchant id in attributes table
alter table qf_prod_attr_mapping add column merchant_id int default null;

-- Grouping title table
CREATE TABLE `qf_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_title` varchar(255) NOT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Grouping of products
CREATE TABLE `qf_prod_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `prod_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add option for restaurant (r) or other(o) in qf_domains
alter table qf_domains add column qf_type enum ('r','o') default 'o' after domain_name;

-- add bulk_csv_file column to qf_products table
alter table qf_products add column bulk_csv_file text default null after meta_keywords;

-- qf_coupons table
CREATE TABLE `qf_coupons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `title` VARCHAR(1024) NOT NULL,
  `desc` text DEFAULT NULL ,
  `start_date` datetime DEFAULT NULL,
  `validity_date` datetime DEFAULT NULL,
  `coupon_type` enum('fd', 'cd', 'bd', 'ua') NOT NULL,
  `coupon_code` VARCHAR(255) UNIQUE DEFAULT NULL,
  `disc_type` enum('a', 'p') NOT NULL DEFAULT 'A',
  `disc_value` int(11) NOT NULL,
  `min_order_value` int(11) DEFAULT NULL,
  `type_id_map` VARCHAR(255) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- coupon and login based catalogue feature column added in qf_domains
alter table qf_domains add column qfree_features_access varchar(2048) default null;
