02-Dec-2024==================Srinivas=======================
Low credits warning

[APP_CONFIG]
lowcredits_warning = 300
reg_stop_warning = 100

============================================================


06-Dec-2024==================Srinivas=======================

credits notification flag 

ALTER TABLE `hirelines`.`account` 
ADD COLUMN `lowcreditsnotification` VARCHAR(1) NULL DEFAULT NULL AFTER `balance`;


============================================================


USE hirelines;
ALTER TABLE brules
ADD COLUMN hold CHAR(1) NULL,
ADD COLUMN holdpercentage INT NULL;


10-Jan-2025=====================Srikanth=========================

alter table hirelines.jobdesc add column dashboardflag varchar(1) null ;

============================================================


======================== PRATAP =================================

ALTER TABLE `hirelines`.`workflow` 
ADD COLUMN `teststatus` VARCHAR(1) NULL DEFAULT NULL AFTER `paperlibraryid`;


============================Mounika========================================


CREATE TABLE IF NOT EXISTS `resume` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `datentime` DATETIME NULL,
  `sourceid` INT NULL,
  `filename` VARCHAR(100) NULL,
  `status` VARCHAR(1) NULL COMMENT 'P - Pending Review, D - Soft Deleted, A - Added to Profiles',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;



CREATE TABLE IF NOT EXISTS `profile` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `sourceid` INT NULL,
  `resumeid` INT NULL,
  `dateofcreation` DATETIME NULL,
  `title` VARCHAR(100) NULL,
  `firstname` VARCHAR(100) NULL,
  `middlename` VARCHAR(40) NULL,
  `lastname` VARCHAR(100) NULL,
  `email` VARCHAR(100) NULL,
  `mobile` VARCHAR(15) NULL,
  `linkedin` VARCHAR(100) NULL,
  `facebook` VARCHAR(100) NULL,
  `passportnum` VARCHAR(40) NULL,
  `dateofbirth` DATETIME NULL,
  `fathername` VARCHAR(100) NULL,
  `nativeof` VARCHAR(100) NULL,
  `status` VARCHAR(1) NULL COMMENT 'D - Draft, R - Rejected, O - Offered, E - Employee',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `profileeducation` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `sequence` INT NULL,
  `course` VARCHAR(100) NULL,
  `institute` VARCHAR(100) NULL,
  `yearfrom` INT NULL,
  `yearto` INT NULL,
  `grade` VARCHAR(100) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;



CREATE TABLE IF NOT EXISTS `profileexperience` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `sequence` INT NULL,
  `jobtitle` VARCHAR(100) NULL,
  `company` VARCHAR(100) NULL,
  `yearfrom` INT NULL,
  `yearto` INT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;



CREATE TABLE IF NOT EXISTS `profileskills` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `primaryskills` VARCHAR(256) NULL,
  `secondaryskills` VARCHAR(256) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `profileprojects` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `sequence` INT NULL,
  `projectname` VARCHAR(100) NULL,
  `clientname` VARCHAR(100) NULL,
  `roleplayed` VARCHAR(100) NULL,
  `skillsused` VARCHAR(100) NULL,
  `yearsfrom` INT NULL,
  `yearsto` INT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `resumefile` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `resumeid` INT NULL,
  `filename` VARCHAR(100) NULL,
  `filecontent` BLOB NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `profileactivity` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `datentime` DATETIME NULL,
  `sequence` INT NULL,
  `activitycode` VARCHAR(2) NULL COMMENT 'PC - Profile Created | PS - Profile Screened | P1 - Profile Screening - Promoted | PC - Profile Coding Test | P2 - Profile Coding Test - Promoted',
  `acvityuserid` INT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS `profileawards` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `sequence` INT NULL,
  `awardname` VARCHAR(100) NULL,
  `year` INT NULL,
  PRIMARY KEY (`ID`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `profilecertificates` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `certname` VARCHAR(100) NULL,
  `sequence` INT NULL,
  `year` INT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `profileaddress` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileid` INT NULL,
  `addline1` VARCHAR(100) NULL,
  `addline2` VARCHAR(100) NULL,
  `city` VARCHAR(100) NULL,
  `state` VARCHAR(40) NULL,
  `country` VARCHAR(40) NULL,
  `zipcode` VARCHAR(20) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
======================== SRIKANTH =================================

INSERT INTO `role` (`Name`, `status`) VALUES ('Recruiter', 'A');
INSERT INTO `rolespermissions` (`function`, `function_category`, `function_link`, `enable`, `function_icon`, `orderby`) VALUES ('Resume Inbox', 'Resume Inbox', 'resume-inbox', 'HR-Admin, Recruiter', 'fas fa-envelope', '3');

==================ProfileActivity==================================

ALTER TABLE profileactivity
ADD activityname VARCHAR(100) NULL,
ADD activityremarks VARCHAR(200) NULL,
ADD activitystatus VARCHAR(100) NULL;


=====manikanta===============================================================

ALTER TABLE workcal 
CHANGE workhours hours VARCHAR(4) NULL;

ALTER TABLE workcal 
CHANGE empid userid INT NULL;

INSERT INTO `rolespermissions` (`id`, `function`, `function_category`, `function_link`, `enable`, `function_icon`, `orderby`) VALUES ('14', 'Work Calender', 'Work Calender', 'work-calender', 'Interviewer', 'fas fa-calendar-day', '12');


ALTER TABLE `workcal` 
CHANGE COLUMN `startday` `startday` VARCHAR(10) NULL DEFAULT NULL ,
CHANGE COLUMN `weekoff1` `weekoff1` VARCHAR(10) NULL DEFAULT NULL ,
CHANGE COLUMN `weekoff2` `weekoff2` VARCHAR(10) NULL DEFAULT NULL ;


======Srikanth=========

alter table profile add column strength int null;
alter table source add column userid int null;
alter table jobdesc add column recruiterids varchar(100) NULL;

=================


ALTER TABLE jobdesc ADD COLUMN approval_status VARCHAR(1) NULL;
ALTER TABLE jobdesc ADD COLUMN comments VARCHAR(100) NULL;

=================

ALTER TABLE jobdesc ADD COLUMN secondaryskills VARCHAR(2048) NULL;
INSERT INTO `rolespermissions` (`function`, `function_category`, `function_link`, `enable`, `function_icon`, `orderby`) VALUES ('Sources', 'Sources', 'sources', 'HR-Admin', 'fas fa-location-arrow', '12');






==============MANIKANTA==============================================

INSERT INTO lookupmaster
(lookupid,lookupmasterid,lookupname,lookupparam1,lookupparam2,status,comments)
VALUES
(1,0,'Activitycodes','Activitycodes',NULL,'A',NULL);

INSERT INTO lookupmaster
(lookupid,lookupmasterid,lookupname,lookupparam1,lookupparam2,status,comments)
VALUES
(1,1,'Rejected','RJ',NULL,'A',NULL),
(1,2,'Selected','SL',NULL,'A',NULL),
(1,3,'Submitted To Client','CL',NULL,'A',NULL),
(1,4,'Waiting Client Feedback','CF',NULL,'A',NULL),
(1,5,'Client-Rejected','CR',NULL,'A',NULL),
(1,6,'Client-Selected','CS',NULL,'A',NULL),
(1,7,'Client-Interview','CI',NULL,'A',NULL);
=================Sathwik================

ALTER TABLE 'resume'
ADD COLUMN docparserid INT NULL;


===================Mounika=============

CREATE TABLE profileanalysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    companyid INT NULL,
    month INT  NULL,
    year INT NULL,
    userid INT NULL,
    activitycode VARCHAR(3) NULL,
    profilescount INT NULL
);


======== Srikanth
INSERT INTO `rolespermissions` (`function`, `function_category`, `function_link`, `enable`, `function_icon`, `orderby`) VALUES ('Work Space', 'Work Space', 'workspace', 'Recruiter', 'fas fa-chalkboard-teacher', '2');

CREATE TABLE client (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NULL,
  `companyid` INT NULL,
  `createdat` DATETIME NULL,
  `createdby` INT NULL,
  `status` CHAR(1) NULL
);

ALTER TABLE candidate ADD COLUMN profileid int null;

CREATE TABLE workspace (
  id INT AUTO_INCREMENT PRIMARY KEY,
  clientid INT NULL,
  project VARCHAR(100) NULL,
  startdate DATETIME NULL,
  notes VARCHAR(100) NULL,
  createdby INT NULL,
  createdat DATETIME NULL,
  status VARCHAR(1) NULL
);


==============================Mounika

ALTER TABLE 'profileanalysis'
ADD COLUMN day INT NULL;

==============================Sathwik
ALTER TABLE jobdesc
ADD COLUMN hiringmanagerid INTEGER NULL;


============manikanta =======
ALTER TABLE profileactivity
MODIFY activitycode VARCHAR(3);

ALTER TABLE jobdesc
ADD COLUMN hiringmanagerid INTEGER NULL;

ALTER TABLE profileactivity ADD COLUMN companyid INT NULL

============srikanth =======

ALTER TABLE workspace
ADD COLUMN jd_ids JSON DEFAULT NULL;

==============================Sathwik
ALTER TABLE profile
ADD COLUMN profilecode VARCHAR(20) DEFAULT NULL;
ALTER TABLE profile
MODIFY COLUMN mobile VARCHAR(50) NULL;
============Mounika =======
ALTER TABLE jobdesc
ADD COLUMN jobcode VARCHAR(20) UNIQUE;
ALTER TABLE profile
ADD COLUMN  completion int DEFAULT NULL;

==================Mounika==================


CREATE TABLE jdlibrary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(350) NULL,
    description VARCHAR(3024) NULL,
    role VARCHAR(50) NOT NULL,
    min_exp INT NOT NULL,
    max_exp INT NOT NULL,
    primary_skills VARCHAR(2048) NULL,
    secondary_skills VARCHAR(2048) NULL,
    work_location VARCHAR(512) NULL
);


====================Sample library data===========================


INSERT INTO jdlibrary
(title, description, role, min_exp, max_exp, work_location, primary_skills, secondary_skills)
VALUES
(
 'Java Backend Developer',
 'Responsible for developing and maintaining backend services using Java and Spring. Strong knowledge of REST APIs, databases, and scalable backend architecture.',
 'Development',
 3,
 5,
 'Bangalore',
 '["java","spring","rest api","hibernate","mysql"]',
 '["docker","kafka"]'
);




INSERT INTO jdlibrary
(title, description, role, min_exp, max_exp, work_location, primary_skills, secondary_skills)
VALUES
(
 'Java Fullstack Developer',
 'Develop end-to-end applications using Java for backend and modern frontend frameworks. Experience with REST APIs and UI development is required.',
 'Development',
 3,
 5,
 'Hyderabad',
 '["java","spring","react","html","css","js","mysql"]',
 '["redux","bootstrap"]'
 );


================Srikanth ==============
CREATE TABLE jobboard (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NULL,
  logo_path VARCHAR(100) NULL,
  status CHAR(1) NULL
);


CREATE TABLE jobboardcredential (
  id INT AUTO_INCREMENT PRIMARY KEY,
  companyid INT NULL,
  jobboardid INT NULL,
  apikey VARCHAR(512) NULL,
  endpoint VARCHAR(512) NULL,
  username VARCHAR(100) NULL,
  password VARCHAR(100) NULL,
  status CHAR(1) NULL
);

CREATE TABLE jdjobboard (
  id INT AUTO_INCREMENT PRIMARY KEY,
  companyid INT NULL,
  jobdescid INT NULL,
  jobboardid INT NULL,
  publishedon DATETIME NULL,
  publishedby INT NULL,
  status CHAR(1) NULL
);

INSERT INTO `rolespermissions` (`function`, `function_category`, `function_link`, `enable`, `function_icon`, `orderby`) VALUES ('Job Boards', 'Job Boards', 'job-boards', 'HR-Admin', 'fas fa-clipboard-list', '8');

INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Linkedin','/static/lib/portal/img/jobboards/linkedin.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Indeed','/static/lib/portal/img/jobboards/indeed.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Naukri','/static/lib/portal/img/jobboards/naukri.png','A');

INSERT INTO `rolespermissions` (`function`, `function_category`, `function_link`, `enable`, `function_icon`, `orderby`) VALUES ('Overall Dashboard', 'Overall Dashboard', 'overall-dashboard', 'HR-Admin', 'fas fa-chart-line', '1');

===============manikanta======

UPDATE rolespermissions
SET 
    `function` = 'Interviewer Availability',
    `function_category` = 'Interviewer Availability'
WHERE function_link = 'work-calender';


============Srikanth 27/01/2026 ===========
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('ZipRecruiter','/static/lib/portal/img/jobboards/ziprecruiter.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Glassdoor','/static/lib/portal/img/jobboards/glassdoor.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Careers24','/static/lib/portal/img/jobboards/c24.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Monster','/static/lib/portal/img/jobboards/monster.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Careerjet','/static/lib/portal/img/jobboards/careerjet.png','A');
INSERT INTO `jobboard` (`name`,`logo_path`,`status`) VALUES ('Jooble','/static/lib/portal/img/jobboards/jooble.png','A');


======================== Audio Score(Balu) 03-02-2025 ===============

ALTER TABLE `callschedule` 
ADD COLUMN `audioscore` INT NULL DEFAULT NULL AFTER `companyid`,
ADD COLUMN `audiolevel` VARCHAR(1) NULL DEFAULT NULL AFTER `audioscore`;



========================= Resume Template ---- Srikanth 10-02-2025 ============================

CREATE TABLE hirelines.resumetemplate (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(40) NULL,
  thumbnail VARCHAR(100) NULL,
  filename VARCHAR(100) NULL,
  default_template VARCHAR(1) NULL,
  status VARCHAR(1) NULL
);

INSERT INTO resumetemplate (`name`, `filename`, `status`, `default_template`, `thumbnail`) VALUES ('Classic Resume', 'classic_template.html', 'A', 'Y', 'classic.png');
INSERT INTO resumetemplate (`name`, `filename`, `status`, `default_template`, `thumbnail`) VALUES ('Two Column Resume', 'two_column_template.html', 'A', NULL, 'two_column.png');

ALTER TABLE company add column resumetemplateid int null;

ALTER TABLE resume add column jobid int null;

===============manikanta=============16-02-25========

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tag VARCHAR(40) NOT NULL,
    resumeid INT NULL,
    profileid INT NULL
);

===============Srikanth=============17-02-25========
ALTER TABLE jobdesc add column webpublish varchar(1) null;


===============Srikanth=============20-03-25========
ALTER TABLE companydata add column package int null;

=============================Balu=================08-04-2026

ALTER TABLE callschedule
ADD COLUMN `rescheduleflag` VARCHAR(1) NULL DEFAULT NULL,
ADD COLUMN `reschedulereason` VARCHAR(250) NULL DEFAULT NULL;


INSERT INTO lookupmaster (`lookupid`, `lookupmasterid`, `lookupname`, `lookupparam1`, `status`) VALUES ('1', '8', 'Interview Reschedule', 'IR', 'A');


INSERT INTO lookupmaster (`lookupid`, `lookupmasterid`, `lookupname`, `lookupparam1`, `status`) VALUES ('2', '0', 'Reson For Reschedule', 'Reson For Reschedule', 'A');
INSERT INTO lookupmaster (`lookupid`, `lookupmasterid`, `lookupname`, `lookupparam1`, `status`) VALUES ('2', '1', 'Call Disconected', 'CD', 'A');
INSERT INTO lookupmaster (`lookupid`, `lookupmasterid`, `lookupname`, `lookupparam1`, `status`) VALUES ('2', '2', 'Technical Issue', 'TI', 'A');
INSERT INTO lookupmaster (`lookupid`, `lookupmasterid`, `lookupname`, `lookupparam1`, `status`) VALUES ('2', '3', 'Candidate Not Joined', 'CN', 'A');
INSERT INTO lookupmaster (`lookupid`, `lookupmasterid`, `lookupname`, `lookupparam1`, `status`) VALUES ('2', '4', 'Interviewer Not Availbe', 'IN', 'A');