
CREATE TABLE registration_form (
    id INT AUTO_INCREMENT PRIMARY KEY,
    profile_picture VARCHAR(255), -- Path or URL to the profile picture
    membership_id VARCHAR(50), -- OPF/OBRF Member Id
    salutation VARCHAR(50),
    full_name VARCHAR(100) NOT NULL, -- Full name
    email VARCHAR(100) NOT NULL, -- Email address
    whatsapp_number VARCHAR(15), -- WhatsApp number, could be a string to accommodate different formats
    gender VARCHAR(50),  -- Gender options
    date_of_birth DATE, -- Date of birth in the format yyyy-mm-dd
    aadhar_number VARCHAR(12), -- Aadhar number (12 digits)
    affiliation VARCHAR(255), -- Affiliation or organization
    designation VARCHAR(100), -- Designation
    address VARCHAR(255), -- Address
    city VARCHAR(100), -- City
    state VARCHAR(50), -- State
    pincode VARCHAR(10), -- Pincode, could be a string to accommodate different formats
    country VARCHAR(50), -- Country
    payment_status VARCHAR(50),
    password VARCHAR(250),
    registration_plan VARCHAR(250),
    plan_fee double(100,2),
    registration_code VARCHAR(100),
    submitted_abstract BOOLEAN DEFAULT FALSE, -- Boolean to indicate if the abstract is submitted
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Timestamp of record creation
);


/*   */

CREATE TABLE ConferenceAbstracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    whatsapp_number VARCHAR(15) NOT NULL,
    author_full_name VARCHAR(255) NOT NULL,
    designation VARCHAR(100),
    affiliation VARCHAR(255),
    co_author_name VARCHAR(300),
    presentation_subject VARCHAR(100),
    article_type VARCHAR(100),
    abstract_title VARCHAR(255) NOT NULL,
    abstract_file LONGBLOB,  -- To store the uploaded file in binary format
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    pincode VARCHAR(10),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    registration_code VARCHAR(100)
);


CREATE TABLE counter_table (
    counter_table_id INT(100) NOT NULL AUTO_INCREMENT,
    counter_name VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
    counter_value VARCHAR(250) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
    PRIMARY KEY (counter_table_id)
);


INSERT INTO counter_table (counter_table_id, counter_name, counter_value) VALUES
(1, 'registration', '1'),
(2, 'abstract', '1');


ALTER TABLE `registration_form` ADD `role` VARCHAR(20) NOT NULL AFTER `created_at`;


CREATE TABLE users (
    id INT(11) NOT NULL AUTO_INCREMENT,
    email VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    password VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    registration_id int(11),
    role VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active tinyint(1),
    PRIMARY KEY (id)
);

-- piyush  admin user details 09/09/24
INSERT INTO `users` (`id`, `email`, `password`, `registration_id`, `role`, `created_at`, `is_active`) VALUES (NULL, 'conference@abhinavpharmacycollege.org', '$2a$10$thwsozf4ZEoFVGgM6MnyAuC1aGhKz4LSSnVOK/W8p0ryDkBwjWegu', '0', 'admin', '2024-09-06 16:56:06', '1')

ALTER TABLE `conferenceabstracts` ADD `status` VARCHAR(30) NULL AFTER `registration_code`;
ALTER TABLE `conferenceabstracts` CHANGE `abstract_file` `abstract_file` VARCHAR(200) NULL DEFAULT NULL;
ALTER TABLE `conferenceabstracts` ADD `abstract_code` VARCHAR(40) NULL DEFAULT NULL AFTER `status`;

ALTER TABLE `registration_form` ADD `transaction_number` VARCHAR(50) NULL AFTER `created_at`;

CREATE TABLE `abstract_mgt`.`rejected_abstract` (`rejected_id` INT NOT NULL AUTO_INCREMENT , `rejected_reason` VARCHAR(200) NULL , `abstract_id` INT NULL , `registration_id` INT NULL , PRIMARY KEY (`rejected_id`)) ENGINE = InnoDB;

-- piyush 16/09/2024
ALTER TABLE `conferenceabstracts` ADD `country` VARCHAR(50) NULL DEFAULT NULL AFTER `abstract_code`;

-- piyush 23/09/2024
CREATE TABLE payment_details (
    payment_details_id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id VARCHAR(100) NOT NULL,
    order_id VARCHAR(100) NOT NULL,
    signature VARCHAR(300) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE `registration_form` ADD `payment_details_id` INT NULL DEFAULT NULL AFTER `transaction_number`;

ALTER TABLE `payment_details` ADD `email` VARCHAR(100) NULL DEFAULT NULL AFTER `created_at`;

-- piyush
ALTER TABLE `tokens` ADD `resubmit_abstract_count` INT NULL DEFAULT NULL AFTER `expires_at`;

ALTER TABLE `payment_details` ADD `payment_mode` VARCHAR(100) NULL DEFAULT NULL AFTER `email`;