create database CLINIHR;

create table users ( id int Primary Key Auto_Increment not null, first_name varchar(100), middle_name varchar(100), last_name varchar(100), DOB date, gender varchar(50), address varchar(500), city varchar(100), state varchar(100), country varchar(100), email varchar(300), personal_email varchar(300), phone_number varchar(50), bank_name varchar(200), bank_account_number varchar(100), bank_account_type varchar(100), IFSC_code varchar(100), PAN_card varchar(50), emp_code varchar(50), joining_date Date, emp_type varchar(100), designation varchar(300), department varchar(300), line_manager varchar(200), varchar(200), qualification varchar(300), experience varchar(50), password varchar(100), security_question varchar(100), answer varchar(50), role varchar(100), status varchar(50), imagename varchar(200),filename varchar(300));

create table leave_app ( req_id int Primary Key Auto_Increment not null, emp_code varchar(50), LMemail varchar(200), nature_of_leave varchar(500), days_applied varchar(50), date_from date, date_to date, half_day varchar(100), reason varchar(1000), contact_number varchar(15), contact_address varchar(500), allocated_person varchar(100), LM_approved varchar(50), HR_approved varchar(50));

// Old
create table emp_timesheet ( timesheet_id int Primary Key Auto_Increment not null, worked_hrs_week time, total_worked_hrs time, regular_hrs time, overtime time, dates date, time_in time, lunch_start time, lunch_end time, time_out time, worked_hrs_day time, work_performed varchar(5000), project_number varchar(100), status varchar(10);

// New
CREATE TABLE employee_timesheet (
    id INT AUTO_INCREMENT PRIMARY KEY,
    emp_code varchar(50),
    line_manager varchar(200),
    total_work_week_hours TIME,
    total_hours_worked TIME,
    regular_hours TIME,
    overtime_hours TIME,
    date DATE,
    time_in TIME,
    lunch_start TIME,
    lunch_end TIME,
    time_out TIME,
    hours_worked TIME,
    details varchar(500)
    );

//old
create table emp_payroll ( payroll_id int Primary Key Auto_Increment not null, UAN varchar(50), ESI_reg varchar(50), total_days float(10), present_days float(10), basic_salary float(50), rent_allowance float(50), total_earnings float(50), providant_fund float(50), ESI_deduction float(50), professional_tax float(50), income_tax float(50), total_deduction float(50), paid_status varchar(10));

//new
create table emp_payroll (payroll_id int Primary Key Auto_Increment not null, emp_code varchar(50), date DATE DEFAULT CURRENT_DATE, month varchar(50), year varchar(50), file_name varchar(200), file_path varchar(300), status varchar(10));


CREATE TABLE departments (department_id INT AUTO_INCREMENT PRIMARY KEY, department_name VARCHAR(255));

Syntax: show tables;
Example:

Syntax: show databases;
Example:

Syntax: DESC table_name;
Example:

Syntax: ALTER TABLE table ADD [COLUMN] col_name col_definition [FIRST|AFTER existing_column];
Example: ALTER TABLE employee_registration ADD COLUMN Gender VARCHAR(100) AFTER Department;

Syntax: CREATE TABLE table_name ( id INT() PRIMARY KEY AUTO_INCREMENT, name VARCHAR(225) NOT NULL, start_at TIME, end_at TIME);

-- Create the TimeSheet table with foreign key references
CREATE TABLE emp_timesheet (
    EmployeeName VARCHAR(255),
    EmployeeEmail VARCHAR(255),
    EmployeePhone VARCHAR(50),
    EmployeeID VARCHAR(50),
    ManagerName VARCHAR(255),
    TotalWorkWeekHours TIME,
    TotalHoursWorked TIME,
    RegularHours TIME,
    OvertimeHours TIME, 
    Date DATE,
    TimeIn TIME,
    LunchStart TIME,
    LunchEnd TIME,
    TimeOut TIME,
    HoursWorked TIME,
    WorkPerformed TEXT,
    FOREIGN KEY (EmployeeName) REFERENCES users(first_name),
    FOREIGN KEY (EmployeeEmail) REFERENCES users(email),
    FOREIGN KEY (EmployeePhone) REFERENCES users(phone_number),
    CONSTRAINT fk_emp_id FOREIGN KEY (emp_id) REFERENCES users(id);
    FOREIGN KEY (ManagerName) REFERENCES users(line_manager)   
);