-- USERS TABLE
CREATE TABLE `users` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) DEFAULT NULL,
    `user_pass` VARCHAR(255) DEFAULT NULL,
    `email` VARCHAR(100) DEFAULT NULL,
    `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `is_admin` INT(10) DEFAULT 0,
    `phone` VARCHAR(15) DEFAULT NULL,
    `address` TEXT DEFAULT NULL,
    `full_name` VARCHAR(255) DEFAULT NULL,
    `account_status` INT(10) DEFAULT 1,
    PRIMARY KEY (`id`)
);

-- PACKAGES TABLE
CREATE TABLE `packages` (
    `package_id` INT(10) NOT NULL AUTO_INCREMENT,
    `package_name` VARCHAR(255) DEFAULT NULL,
    `package_rating` FLOAT DEFAULT NULL,
    `package_desc` TEXT DEFAULT NULL,
    `package_start` DATE DEFAULT NULL,
    `package_end` DATE DEFAULT NULL,
    `package_price` INT(10) DEFAULT NULL,
    `package_location` VARCHAR(255) DEFAULT NULL,
    `is_hotel` INT(10) DEFAULT 0,
    `is_transport` INT(10) DEFAULT 0,
    `is_food` INT(10) DEFAULT 0,
    `is_guide` INT(10) DEFAULT 0,
    `package_capacity` INT(10) DEFAULT 0,
    `package_booked` INT(10) UNSIGNED DEFAULT 0,
    `map_loc` TEXT DEFAULT NULL,
    `master_image` TEXT DEFAULT NULL,
    `extra_image_1` TEXT DEFAULT NULL,
    `extra_image_2` TEXT DEFAULT NULL,
    PRIMARY KEY (`package_id`)
);

-- TRANSACTIONS TABLE
CREATE TABLE `transactions` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `trans_id` VARCHAR(255) DEFAULT NULL,
    `user_id` INT(10) DEFAULT NULL,
    `package_id` INT(10) DEFAULT NULL,
    `trans_amount` INT(10) DEFAULT NULL,
    `trans_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `card_no` VARCHAR(255) DEFAULT NULL,
    `val_id` VARCHAR(255) DEFAULT NULL,
    `card_type` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_transactions_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE SET NULL,
    CONSTRAINT `fk_transactions_package`
        FOREIGN KEY (`package_id`) REFERENCES `packages` (`package_id`)
        ON DELETE SET NULL
);

-- TESTIMONIALS TABLE
CREATE TABLE `testimonials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `message` TEXT,
    `user_id` INT,
    `package_id` INT,
    `rating` FLOAT,
    `date_created` DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_testimonials_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
        ON DELETE SET NULL,
    CONSTRAINT `fk_testimonials_package`
        FOREIGN KEY (`package_id`) REFERENCES `packages` (`package_id`)
        ON DELETE SET NULL
);