-- Company Table
CREATE TABLE company (
    company_code VARCHAR(20) PRIMARY KEY,
    company_cin VARCHAR(30),
    company_headoffice VARCHAR(255),
    company_gstin VARCHAR(30),
    company_name VARCHAR(100),
    company_desc TEXT
);
-- Master Table
CREATE TABLE master (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    company_code VARCHAR(20),
    first_name VARCHAR(50),
    middle_name VARCHAR(50),
    last_name VARCHAR(50),
    preferred_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    contact_number VARCHAR(20) UNIQUE,
    date_of_birth DATE,
    hire_date DATE,
    salary DECIMAL(15,2),
    is_admin TINYINT(1),
    is_active TINYINT(1),
    FOREIGN KEY (company_code) REFERENCES company(company_code)
);
-- Users Table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255),
    reset_password_token VARCHAR(255),
    reset_password_expires DATETIME,
    isactive TINYINT(1) DEFAULT 1,
    FOREIGN KEY (employee_id) REFERENCES master(employee_id)
);
-- Attendance Table
CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    date DATE,
    checkin_time DATETIME,
    checkin_lat DECIMAL(10,7),
    checkin_lng DECIMAL(10,7),
    checkin_address VARCHAR(255),
    checkout_time DATETIME,
    checkout_lat DECIMAL(10,7),
    checkout_lng DECIMAL(10,7),
    checkout_address VARCHAR(255),
    PO_id INT,
    task_id INT,
    remarks TEXT,
    work_pointers TEXT,
    FOREIGN KEY (employee_id) REFERENCES master(employee_id),
    FOREIGN KEY (PO_id) REFERENCES PO(PO_id),
    FOREIGN KEY (task_id) REFERENCES PO_Task(task_id)
);
-- Employee Removal Log Table
CREATE TABLE employee_removal_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    reason VARCHAR(255),
    removed_at DATETIME,
    FOREIGN KEY (employee_id) REFERENCES master(employee_id)
);
-- PO Table
CREATE TABLE PO (
    PO_id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE,
    expiry_date DATE,
    value DECIMAL(15,2),
    active TINYINT(1)
);
-- Emp_PO Table
CREATE TABLE Emp_PO (
    employee_id INT,
    PO_id INT,
    date DATE,
    PRIMARY KEY (employee_id, PO_id),
    FOREIGN KEY (employee_id) REFERENCES master(employee_id),
    FOREIGN KEY (PO_id) REFERENCES PO(PO_id)
);
-- PO_Task Table
CREATE TABLE PO_Task (
    task_id INT PRIMARY KEY AUTO_INCREMENT,
    task_name VARCHAR(100),
    task_desc TEXT,
    task_hrs DECIMAL(6,2),
    active TINYINT(1)
);
-- Location Table
CREATE TABLE Location (
    loc_id INT PRIMARY KEY AUTO_INCREMENT,
    loc_name VARCHAR(100)
);
-- PO_loc Table
CREATE TABLE PO_loc (
    PO_loc_id INT PRIMARY KEY AUTO_INCREMENT,
    PO_id INT,
    loc_id INT,
    FOREIGN KEY (PO_id) REFERENCES PO(PO_id),
    FOREIGN KEY (loc_id) REFERENCES Location(loc_id)
);
-- Emp_PO_Task_Loc Table
CREATE TABLE Emp_PO_Task_Loc (
    employee_id INT,
    PO_id INT,
    task_id INT,
    PO_loc_id INT,
    assigned_at DATETIME,
    PRIMARY KEY (employee_id, PO_id, task_id, PO_loc_id),
    FOREIGN KEY (employee_id) REFERENCES master(employee_id),
    FOREIGN KEY (PO_id) REFERENCES PO(PO_id),
    FOREIGN KEY (task_id) REFERENCES PO_Task(task_id),
    FOREIGN KEY (PO_loc_id) REFERENCES PO_loc(PO_loc_id)
);