-- 1. Create new tables
CREATE TABLE admin_login (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE dme_login (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'dme',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employee_login (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'employee',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employer_login (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'employer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Migrate data
INSERT INTO admin_login (id, username, password, role, created_at)
SELECT id, username, password, role, created_at FROM users WHERE role = 'admin';

INSERT INTO dme_login (id, username, password, role, created_at)
SELECT id, username, password, role, created_at FROM users WHERE role = 'dme';

INSERT INTO employee_login (id, username, password, role, created_at)
SELECT id, username, password, role, created_at FROM users WHERE role = 'employee';

INSERT INTO employer_login (id, username, password, role, created_at)
SELECT id, username, password, role, created_at FROM users WHERE role = 'employer';

-- 3. Drop Foreign Keys
ALTER TABLE applications DROP FOREIGN KEY applications_ibfk_2;
ALTER TABLE employee_details DROP FOREIGN KEY employee_details_ibfk_1;
ALTER TABLE employer_details DROP FOREIGN KEY employer_details_ibfk_1;
ALTER TABLE jobs DROP FOREIGN KEY jobs_ibfk_1;
ALTER TABLE reviews DROP FOREIGN KEY reviews_ibfk_2;
ALTER TABLE reviews DROP FOREIGN KEY reviews_ibfk_3;

-- 4. Drop users table
DROP TABLE users;

-- 5. Re-add Foreign Keys
ALTER TABLE applications ADD CONSTRAINT fk_app_employee FOREIGN KEY (employee_id) REFERENCES employee_login(id) ON DELETE CASCADE;
ALTER TABLE employee_details ADD CONSTRAINT fk_emp_det_user FOREIGN KEY (user_id) REFERENCES employee_login(id) ON DELETE CASCADE;
ALTER TABLE employer_details ADD CONSTRAINT fk_empr_det_user FOREIGN KEY (user_id) REFERENCES employer_login(id) ON DELETE CASCADE;
ALTER TABLE jobs ADD CONSTRAINT fk_job_employer FOREIGN KEY (employer_id) REFERENCES employer_login(id) ON DELETE CASCADE;
ALTER TABLE reviews ADD CONSTRAINT fk_review_employee FOREIGN KEY (employee_id) REFERENCES employee_login(id) ON DELETE CASCADE;
ALTER TABLE reviews ADD CONSTRAINT fk_review_employer FOREIGN KEY (employer_id) REFERENCES employer_login(id) ON DELETE CASCADE;
