
CREATE TABLE IF NOT EXISTS tbllogo_designs (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(100),
  DESCRIPTION VARCHAR(250),
  order_no INT,
  logo_img_src VARCHAR(200),
  is_active TINYINT DEFAULT 1,
  created_on DATETIME NOT NULL,
  created_by INT(11) DEFAULT NULL,
  modified_on DATETIME DEFAULT NULL,
  modified_by INT(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS tbltenant_logo_orders  (
  order_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(100),
  tenant_id BIGINT(20) UNSIGNED NOT NULL,
  order_total DECIMAL(10,2),
  order_data LONGTEXT,
  order_status ENUM('payment_pending','processing','payment_failed','completed','draft_submitted','disputed','cancelled'),
  stripe_transaction_id VARCHAR(100),
  stripe_data LONGTEXT,
  next_revision_no INT(11) DEFAULT 1,
  logo_design_approved_src VARCHAR(200),
  business_card_design_approved_src VARCHAR(200),
  open_files_src  VARCHAR(200),
  added_on DATETIME NOT NULL,
  modified_on DATETIME,
  delivered_on DATETIME,
  PRIMARY KEY (order_id),
  UNIQUE KEY unique_order (order_no)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS tbltenant_logo_order_status_history  (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT(20) UNSIGNED NOT NULL,
  order_status ENUM('payment_pending','processing','payment_failed','completed','draft_submitted','disputed','cancelled'),
  status_comment VARCHAR(200),
  added_by BIGINT(20) UNSIGNED,
  added_on DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


CREATE TABLE IF NOT EXISTS tbltenant_logo_order_files   (
  id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT(20) UNSIGNED NOT NULL,
  revision_no TINYINT(1),
  title VARCHAR(100),
  DESCRIPTION VARCHAR(200),
  mime_type VARCHAR(100),
  design_draft_files LONGTEXT,
  added_by BIGINT(20) UNSIGNED,
  added_on DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO tblapad_options (TYPE,NAME,VALUE,autoload)
VALUES('logo_designing_service','extra_revisions_price','1',1),
('logo_designing_service','extra_logo_concept_price','5',1),
('logo_designing_service','business_card_design_price','49',1),
('logo_designing_service','base_price','49',1),
('logo_designing_service','lds_order_number_format','1',1) ,
('logo_designing_service','next_lds_order_number','1',1),
('logo_designing_service','lds_order_prefix','LDS-',1),
('logo_designing_service','allowed_file_extention','["jpeg","jpg","png","gif","pdf","doc","docx"]',1) ,
('logo_designing_service','allowed_file_size','5120',1),
('logo_designing_service','allowed_extentions','.jpeg,.jpg,.png,.gif,.pdf,.doc,.docx',1) 


INSERT INTO tblapad_emailtemplates (TYPE,slug,LANGUAGE,NAME,SUBJECT,message,ACTIVE, fromname, `order`)
VALUES('logo_designing_service','logo-designing-order-received','english','Logo Designing Order Received',"Order #{order_number} for Custom Logo for {companyname}",'<span style="font-size: 12pt;">Hi {staff_firstname},</span><br/><br/><span style="font-size: 12pt;">This is just a quick email to let you know that our design team has received your order for a custom logo for {companyname}.</span><br/><span style="font-size: 12pt;">Your Order Summary:</span><br/><br/><span style="font-size: 12pt;">{list_all_service_purchased}</span><br/><span style="font-size: 12pt;">Order Total: ${order_total}</span><br/><br/><span style="font-size: 12pt;font-weight:bold;">What Happens Next?</span><br/><span style="font-size: 12pt;">Our team now has your provided information and will begin working on your design. If we have any questions we will reach out from this email account. Be on the lookout for an email in the next 24-48 hours with your first draft.
</span><br/><br/><span style="font-size: 12pt;">Once your draft is approved, we will send you another email with all of your high resolution vector project files. We will also go ahead and upload your new logo directly to your Contractor+ account. </span><br/><br/><span style="font-size: 12pt;">Thanks for your business, we’re grateful that we get to work together to help you SHINE.</span><br/><br/><br/><span style="font-size: 12pt;">Regards,</span><br/><br/><span style="font-size: 12pt;">Pete Hon</span><br/><span style="font-size: 12pt;">Senior Graphic Designer</span><br/><span style="font-size: 12pt;">Contractor+</span><br/><span style="font-size: 12pt;">www.contractorplus.app</span><br/><span style="font-size: 12pt;">(855) 392-8803</span>',1, '', 0);


INSERT INTO tblapad_emailtemplates (TYPE,slug,LANGUAGE,NAME,SUBJECT,message,ACTIVE, fromname, `order`)
VALUES('logo_designing_service','logo-designing-draft-submitted','english','Logo Designing Draft Submitted',"Your design draft for {companyname}",'<span style="font-size: 12pt;">Hi {staff_firstname},</span><br/><br/><span style="font-size: 12pt;">Hope you’re having a great day. It’s been a longggg day here in the office. I have completed your design draft, and attached to this email. Please check it out. If you’re satisfied, I will go ahead and send you all of your high resolution files and project files, and upload your final logo to your Contractor+ account for you. If any revisions are required, please let me know and I will get them made ASAP.</span><br/><br/><span style="font-size: 12pt;">{description}</span></span><br/><br/><br/><span style="font-size: 12pt;">Regards,</span><br/><br/><span style="font-size: 12pt;">Pete Hon</span><br/><span style="font-size: 12pt;">Senior Graphic Designer</span><br/><span style="font-size: 12pt;">Contractor+</span><br/><span style="font-size: 12pt;">www.contractorplus.app</span><br/><span style="font-size: 12pt;">(855) 392-8803</span>',1, '', 0);


INSERT INTO tblapad_emailtemplates (TYPE,slug,LANGUAGE,NAME,SUBJECT,message,ACTIVE, fromname, `order`)
VALUES('logo_designing_service','logo-designing-order-completed','english','Logo Designing Order Completed',"Order #{order_number} is Completed. Your final design files are attached",'<span style="font-size: 12pt;">Hi {staff_firstname},</span><br/><br/><span style="font-size: 12pt;"> I have completed your design and have attached all of your files to this email for you. I also went ahead and uploaded your logo to your Contractor+ account for you.</span><br/><br/><span style="font-size: 12pt;">It has been a pleasure working with you! Thanks for the opportunity {staff_firstname}.<br/><br/><span style="font-size: 12pt;">If you ever need anything else designed, just reach out and we will be happy to assist you.</span><br/><span style="font-size: 12pt;">You can find your logo files at {logo_related_files_link}</span><br/><br/><br/><span style="font-size: 12pt;">Regards,</span><br/><br/><span style="font-size: 12pt;">Pete Hon</span><br/><span style="font-size: 12pt;">Senior Graphic Designer</span><br/><span style="font-size: 12pt;">Contractor+</span><br/><span style="font-size: 12pt;">www.contractorplus.app</span><br/><span style="font-size: 12pt;">(855) 392-8803</span>',1, '', 0);

INSERT INTO tblmobile_push_notification_config (codename,is_active)
VALUES('logo_designing_draft_submitted',1),('logo_designing_order_completed',1);


ALTER TABLE tbllogo_designs CHANGE DESCRIPTION description VARCHAR(250)
ALTER TABLE tbltenant_logo_order_files CHANGE DESCRIPTION description VARCHAR(200)

UPDATE tblapad_emailtemplates SET message ='<span style="font-size: 12pt;">Hi {staff_firstname},</span><br/><br/><span style="font-size: 12pt;">Hope you’re having a great day. It’s been a longggg day here in the office. I have completed your design draft, and attached to this email. Please check it out. If you’re satisfied, I will go ahead and send you all of your high resolution files and project files, and upload your final logo to your Contractor+ account for you. If any revisions are required, please let me know and I will get them made ASAP.</span><span style="font-size: 12pt;">{description}</span></span><br/><span style="font-size: 12pt;">Regards,</span><br/><br/><span style="font-size: 12pt;">Pete Hon</span><br/><span style="font-size: 12pt;">Senior Graphic Designer</span><br/><span style="font-size: 12pt;">Contractor+</span><br/><span style="font-size: 12pt;">www.contractorplus.app</span><br/><span style="font-size: 12pt;">(855) 392-8803</span>' WHERE slug='logo-designing-draft-submitted';

UPDATE tblapad_emailtemplates SET message ='<span style="font-size: 12pt;">Hi {staff_firstname},</span><br/><br/><span style="font-size: 12pt;">This is just a quick email to let you know that our design team has received your order for a custom logo for {companyname}.</span><br/><span style="font-size: 12pt;">Your Order Summary:</span><br/><br/><span style="font-size: 12pt;">{list_all_service_purchased}</span><br/><span style="font-size: 12pt;font-weight:bold;">What Happens Next?</span><br/><span style="font-size: 12pt;">Our team now has your provided information and will begin working on your design. If we have any questions we will reach out from this email account. Be on the lookout for an email in the next 24-48 hours with your first draft.</span><br/><br/><span style="font-size: 12pt;">Once your draft is approved, we will send you another email with all of your high resolution vector project files. We will also go ahead and upload your new logo directly to your Contractor+ account. </span><br/><br/><span style="font-size: 12pt;">Thanks for your business, we’re grateful that we get to work together to help you SHINE.</span><br/><br/><br/><span style="font-size: 12pt;">Regards,</span><br/><br/><span style="font-size: 12pt;">Pete Hon</span><br/><span style="font-size: 12pt;">Senior Graphic Designer</span><br/><span style="font-size: 12pt;">Contractor+</span><br/><span style="font-size: 12pt;">www.contractorplus.app</span><br/><span style="font-size: 12pt;">(855) 392-8803</span>' WHERE slug='logo-designing-order-received';