-- ALTER queries to simplify the qp_setter_claims table to match the provided form

-- First, let's drop the existing table and recreate with simplified structure
-- (You can also use ALTER statements if you want to preserve existing data)

DROP TABLE IF EXISTS qp_setter_claims;

CREATE TABLE qp_setter_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assignment_id INT NOT NULL,
    staff_id INT NOT NULL,
    
    -- Basic Information (matching the form exactly)
    name VARCHAR(255) NULL,
    designation VARCHAR(255) NULL,
    department VARCHAR(255) NULL,
    institution_name_address TEXT NULL,
    mobile_no VARCHAR(20) NULL,
    email_id VARCHAR(255) NULL,
    bank_name VARCHAR(255) NULL,
    branch_name VARCHAR(255) NULL,
    bank_account_number VARCHAR(50) NULL,
    ifsc_code VARCHAR(20) NULL,
    pan_number VARCHAR(20) NULL,
    
    -- Course details will be stored as JSON for multiple courses
    courses_json TEXT NULL,
    
    -- Total amount and amount in words
    total_amount DECIMAL(10,2) NULL,
    amount_in_words TEXT NULL,
    
    -- Signature details
    signature_date DATE NULL,
    
    -- System fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Ensure one claim per assignment per staff member
    UNIQUE KEY uq_claim (assignment_id, staff_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- If you want to ALTER existing table instead of dropping:
/*
ALTER TABLE qp_setter_claims 
DROP COLUMN setter_name,
DROP COLUMN employee_id,
DROP COLUMN semester,
DROP COLUMN academic_year,
DROP COLUMN exam_date,
DROP COLUMN exam_time,
DROP COLUMN question_type,
DROP COLUMN total_questions,
DROP COLUMN marks_per_question,
DROP COLUMN total_marks,
DROP COLUMN remuneration_amount,
DROP COLUMN account_number,
DROP COLUMN account_holder_name,
DROP COLUMN declaration_text;

ALTER TABLE qp_setter_claims 
ADD COLUMN name VARCHAR(255) NULL,
ADD COLUMN institution_name_address TEXT NULL,
ADD COLUMN mobile_no VARCHAR(20) NULL,
ADD COLUMN email_id VARCHAR(255) NULL,
ADD COLUMN branch_name VARCHAR(255) NULL,
ADD COLUMN bank_account_number VARCHAR(50) NULL,
ADD COLUMN courses_json TEXT NULL,
ADD COLUMN total_amount DECIMAL(10,2) NULL,
ADD COLUMN amount_in_words TEXT NULL;

ALTER TABLE qp_setter_claims 
CHANGE COLUMN mobile_number mobile_no VARCHAR(20) NULL,
CHANGE COLUMN email email_id VARCHAR(255) NULL,
CHANGE COLUMN account_number bank_account_number VARCHAR(50) NULL;
*/