CREATE TABLE account_status (
    status_id INT PRIMARY KEY,
    status_name VARCHAR(255)
);
CREATE TABLE roles (
    role_id INT PRIMARY KEY,
    role_name VARCHAR(255),
    description VARCHAR(255)
);
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(255)
);
CREATE TABLE country (
    country_id INT PRIMARY KEY,
    country_name VARCHAR(255)
);
CREATE TABLE audit_status (
    audit_status_id INT PRIMARY KEY,
    audit_status_name VARCHAR(255)
);
CREATE TABLE tickets_status (
    status_id INT PRIMARY KEY,
    status_type VARCHAR(255)
);
CREATE TABLE tickets_type (
    type_id INT PRIMARY KEY,
    type_name VARCHAR(255)
);
CREATE TABLE forms_type (
    form_type_id INT PRIMARY KEY,
    form_type VARCHAR(255)
);
CREATE TABLE question_type (
    question_type_id INT PRIMARY KEY,
    question_type VARCHAR(255)
);
CREATE TABLE rpn_likelihood_of_harm (
    likelihood_of_harm VARCHAR(255),
    value INT
);
CREATE TABLE rpn_severity_of_harm (
    severity_of_harm VARCHAR(255),
    value INT
);
CREATE TABLE locations (
    location_id INT PRIMARY KEY,
    country_id INT,
    location_name VARCHAR(255),
    location_type VARCHAR(255),
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);
CREATE TABLE location_departments (
    department_id INT,
    location_id INT,
    PRIMARY KEY (department_id, location_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (location_id) REFERENCES locations(location_id)
);
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id VARCHAR(255) UNIQUE,
    status_id INT,
    employee_name VARCHAR(255),
    email_id VARCHAR(255),
    phonenumber VARCHAR(255),
    department_id INT,
    location_id INT,
    photo BLOB,
    password VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (status_id) REFERENCES account_status(status_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (location_id) REFERENCES locations(location_id)
);
CREATE TABLE user_role (
    user_role_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    role_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);
CREATE TABLE form_details (
    form_id INT AUTO_INCREMENT PRIMARY KEY,
    form_type_id INT,
    form_parent_id INT NULL,
    title VARCHAR(255),
    description TEXT,
    created_by INT,
    created_date DATETIME,
    end_date DATETIME,
    FOREIGN KEY (form_type_id) REFERENCES forms_type(form_type_id),
    FOREIGN KEY (created_by) REFERENCES users(user_id),
    FOREIGN KEY (form_parent_id) REFERENCES form_details(form_id)
);
CREATE TABLE archive_form (
    form_id INT PRIMARY KEY,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES form_details(form_id) ON DELETE CASCADE
);
CREATE TABLE questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT NOT NULL,
    question_type_id INT,
    allows_media BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (question_type_id) REFERENCES question_type(question_type_id)
);
CREATE TABLE combined_question (
    main_question_id INT NOT NULL,
    child_question_id INT NOT NULL,
    PRIMARY KEY (main_question_id, child_question_id),
    FOREIGN KEY (main_question_id) REFERENCES questions(question_id) ON DELETE CASCADE,
    FOREIGN KEY (child_question_id) REFERENCES questions(question_id) ON DELETE CASCADE
);
CREATE TABLE question_options (
    question_options_id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    _options VARCHAR(500) NOT NULL,
    FOREIGN KEY (question_id) REFERENCES questions(question_id) ON DELETE CASCADE
);
CREATE TABLE form_question_map (
    form_id INT,
    question_id INT,
    PRIMARY KEY (form_id, question_id),
    FOREIGN KEY (form_id) REFERENCES form_details(form_id),
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);
CREATE TABLE location_forms_details (
    form_id INT,
    location_id INT,
    PRIMARY KEY (form_id, location_id),
    FOREIGN KEY (form_id) REFERENCES form_details(form_id),
    FOREIGN KEY (location_id) REFERENCES locations(location_id)
);
CREATE TABLE audits (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    auditor_id INT,
    audit_name VARCHAR(255),
    location INT,
    audit_status_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    observed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (auditor_id) REFERENCES users(user_id),
    FOREIGN KEY (location) REFERENCES locations(location_id),
    FOREIGN KEY (audit_status_id) REFERENCES audit_status(audit_status_id)
);
CREATE TABLE coauditors (
    audit_id INT,
    co_auditor_name VARCHAR(255),
    co_auditor_type BOOLEAN,
    PRIMARY KEY (audit_id, co_auditor_name),
    FOREIGN KEY (audit_id) REFERENCES audits(audit_id)
);
CREATE TABLE auditees (
    audit_id INT,
    auditee_id INT,
    PRIMARY KEY (audit_id, auditee_id),
    FOREIGN KEY (audit_id) REFERENCES audits(audit_id),
    FOREIGN KEY (auditee_id) REFERENCES users(user_id)
);
CREATE TABLE submissions (
    submission_id INT AUTO_INCREMENT PRIMARY KEY,
    audit_id INT,
    submitter_id INT,
    form_id INT,
    opening_form_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (audit_id) REFERENCES audits(audit_id),
    FOREIGN KEY (submitter_id) REFERENCES users(user_id),
    FOREIGN KEY (form_id) REFERENCES form_details(form_id),
    FOREIGN KEY (opening_form_id) REFERENCES form_details(form_id)
);
CREATE TABLE answers (
    answers_id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT,
    question_id INT,
    is_media BOOLEAN,
    answer_value TEXT,
    FOREIGN KEY (submission_id) REFERENCES submissions(submission_id),
    FOREIGN KEY (question_id) REFERENCES questions(question_id)
);
CREATE TABLE answers_media (
    answers_id INT,
    occurrence INT,
    media LONGBLOB,
    PRIMARY KEY (answers_id, occurrence),
    FOREIGN KEY (answers_id) REFERENCES answers(answers_id)
);
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    ticket_type_id INT,
    status_id INT,
    description TEXT,
    subject VARCHAR(255) NULL,
    recipient_email VARCHAR(255) NULL,
    created_at TIMESTAMP,
    closed_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (ticket_type_id) REFERENCES tickets_type(type_id),
    FOREIGN KEY (status_id) REFERENCES tickets_status(status_id)
);
CREATE TABLE tickets_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT,
    user_id INT,
    status_id INT,
    ticket_type_id INT,
    description TEXT,
    created_at TIMESTAMP,
    closed_at TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (status_id) REFERENCES tickets_status(status_id),
    FOREIGN KEY (ticket_type_id) REFERENCES tickets_type(type_id)
);
CREATE TABLE users_logs (
    user_id INT,
    created_by INT,
    employee_id VARCHAR(255),
    role VARCHAR(255),
    status VARCHAR(255),
    employee_name VARCHAR(255),
    phonenumber VARCHAR(255),
    department VARCHAR(255),
    location VARCHAR(255),
    photo BLOB,
    password VARCHAR(255),
    action VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (created_by) REFERENCES users(user_id)
);
CREATE TABLE form_logs (
    user_id INT,
    form_id INT,
    form_type VARCHAR(255),
    form_parent_id INT,
    title VARCHAR(255),
    description TEXT,
    created_by INT,
    created_date DATETIME,
    location_name VARCHAR(255),
    action VARCHAR(255),
    created_at TIMESTAMP
);


CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,

    audit_id INT NOT NULL,
    user_id INT NOT NULL,

    action VARCHAR(50) NOT NULL,

    audit_status_id INT NULL,

    submission_id INT NULL,      -- if observation relates to submission
    answers_id INT NULL,         -- if observation tied to answer

    description TEXT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (audit_id) REFERENCES audits(audit_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (audit_status_id) REFERENCES audit_status(audit_status_id),
    FOREIGN KEY (submission_id) REFERENCES submissions(submission_id),
    FOREIGN KEY (answers_id) REFERENCES answers(answers_id)
);

------------------------------------
INSERT INTO account_status
VALUES (1, 'Active'),
    (2, 'Inactive');

INSERT INTO roles
VALUES (1, 'Admin', 'System Administrator'),
    (2, 'Auditor', 'Audit User'),
    (3, 'User', 'Normal User');

INSERT INTO departments
VALUES (1, 'IT'),
    (2, 'HR'),
    (3, 'Operations');

INSERT INTO country
VALUES (1, 'India'),
    (2, 'USA');

INSERT INTO audit_status
VALUES (1, 'Open'),
    (2, 'Closed'),
    (3, 'In Progress');

INSERT INTO tickets_status
VALUES (1, 'Open'),
    (2, 'Resolved'),
    (3, 'Closed');

INSERT INTO tickets_type
VALUES (1, 'Bug'),
    (2, 'Request'),
    (3, 'Incident');

INSERT INTO forms_type
VALUES (1, 'Opening Form'),
    (2, 'Closing Form');

INSERT INTO question_type
VALUES (1, 'Text'),
    (2, 'Number'),
    (3, 'Yes/No'),
    (4, 'Single Selection Dropdown'),
    (5, 'Multiple Dropdown Selection'),
    (6, 'Image Mark/Attach');

INSERT INTO locations
VALUES (1, 1, 'Kolkata Plant', 'Factory'),
    (2, 1, 'Delhi Office', 'Office');

INSERT INTO location_departments
VALUES (1, 1),
    (2, 1),
    (1, 2),
    (3, 2);

INSERT INTO users (
        user_id,
        employee_id,
        status_id,
        employee_name,
        email_id,
        phonenumber,
        department_id,
        location_id,
        password
    )
VALUES (
        1,
        'EMP001',
        1,
        'Admin User',
        'admin@test.com',
        '9999999999',
        1,
        1,
        'admin123'
    ),
    (
        2,
        'EMP002',
        1,
        'Auditor User',
        'auditor@test.com',
        '8888888888',
        2,
        1,
        'audit123'
    ),
    (
        3,
        'EMP003',
        1,
        'Normal User',
        'user@test.com',
        '7777777777',
        3,
        2,
        'user123'
    );

INSERT INTO user_role (user_id, role_id)
VALUES (1, 1),
    (2, 2),
    (3, 3);

INSERT INTO form_details (
        form_id,
        form_type_id,
        title,
        description,
        created_by,
        created_date
    )
VALUES (
        1,
        1,
        'Safety Opening Audit',
        'Opening safety audit form',
        1,
        NOW()
    ),
    (
        2,
        2,
        'Safety Closing Audit',
        'Closing safety audit form',
        1,
        NOW()
    );

INSERT INTO questions (question_id, question, question_type_id)
VALUES (1, 'Is PPE worn?', 3),
    (2, 'Number of unsafe acts observed?', 2),
    (3, 'Remarks', 1);

INSERT INTO form_question_map
VALUES (1, 1),
    (1, 2),
    (1, 3),
    (2, 3);

INSERT INTO location_forms_details
VALUES (1, 1),
    (2, 1),
    (1, 2);

INSERT INTO audits (
        audit_id,
        auditor_id,
        audit_name,
        location,
        audit_status_id
    )
VALUES (1, 2, 'Monthly Safety Audit', 1, 1);

INSERT INTO coauditors
VALUES (1, 'Co Auditor 1', TRUE);

INSERT INTO auditees
VALUES (1, 3);

INSERT INTO submissions (
        submission_id,
        audit_id,
        submitter_id,
        form_id
    )
VALUES (1, 1, 2, 1);

INSERT INTO answers (
        answers_id,
        submission_id,
        question_id,
        is_media,
        answer_value
    )
VALUES (1, 1, 1, FALSE, 'Yes'),
    (2, 1, 2, FALSE, '3'),
    (3, 1, 3, FALSE, 'Minor issues observed');

INSERT INTO answers_media
VALUES (3, 1, NULL);

INSERT INTO tickets (
        id,
        user_id,
        ticket_type_id,
        status_id,
        description,
        created_at
    )
VALUES (
        1,
        3,
        1,
        1,
        'Camera not working in plant area',
        NOW()
    );

INSERT INTO tickets_logs (
        ticket_id,
        user_id,
        status_id,
        ticket_type_id,
        description,
        created_at
    )
VALUES (1, 3, 1, 1, 'Ticket created', NOW());

INSERT INTO users_logs (
        user_id,
        created_by,
        employee_id,
        role,
        status,
        employee_name,
        phonenumber,
        department,
        location,
        action,
        created_at
    )
VALUES (
        1,
        1,
        'EMP001',
        'Admin',
        'Active',
        'Admin User',
        '9999999999',
        'IT',
        'Kolkata Plant',
        'CREATED',
        NOW()
    );
    
INSERT INTO form_logs (
        user_id,
        form_id,
        form_type,
        title,
        created_by,
        location_name,
        action,
        created_at
    )
VALUES (
        1,
        1,
        'Opening Form',
        'Safety Opening Audit',
        1,
        'Kolkata Plant',
        'CREATED',
        NOW()
    );



-- SQL View to get all logs
CREATE VIEW form_logs_view AS
SELECT 
    f.user_id,
    u.employee_name      AS user_name,
    f.title              AS form_name,
    f.form_type,
    f.description,
    u2.employee_name     AS created_by,
    f.location_name      AS location,
    f.created_at
FROM form_logs f
LEFT JOIN users u 
    ON f.user_id = u.user_id
LEFT JOIN users u2 
    ON f.created_by = u2.user_id;