-- CreateTable
CREATE TABLE `site_vacancy` (
    `sv_id` INTEGER NOT NULL AUTO_INCREMENT,
    `sv_svc_id` INTEGER NOT NULL,
    `sv_svt_id` INTEGER NOT NULL,
    `sv_sl_id` INTEGER NOT NULL,
    `sv_name` VARCHAR(255) NOT NULL,
    `sv_details` MEDIUMTEXT NOT NULL,
    `sv_status` INTEGER NOT NULL DEFAULT 0,
    `sv_date` INTEGER NULL DEFAULT 0,

    PRIMARY KEY (`sv_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `site_vacancy_category` (
    `svc_id` INTEGER NOT NULL AUTO_INCREMENT,
    `svc_name` VARCHAR(255) NOT NULL,

    UNIQUE INDEX `site_vacancy_category_svc_name_key`(`svc_name`),
    PRIMARY KEY (`svc_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `site_vacancy_type` (
    `svt_id` INTEGER NOT NULL AUTO_INCREMENT,
    `svt_name` VARCHAR(255) NOT NULL,

    UNIQUE INDEX `site_vacancy_type_svt_name_key`(`svt_name`),
    PRIMARY KEY (`svt_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `site_location` (
    `sl_id` INTEGER NOT NULL AUTO_INCREMENT,
    `sl_name` VARCHAR(255) NOT NULL,
    `sl_address` VARCHAR(255) NOT NULL,
    `sl_city` VARCHAR(255) NOT NULL,

    UNIQUE INDEX `site_location_sl_city_sl_address_key`(`sl_city`, `sl_address`),
    PRIMARY KEY (`sl_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `site_vacancy` ADD CONSTRAINT `site_vacancy_sv_svc_id_fkey` FOREIGN KEY (`sv_svc_id`) REFERENCES `site_vacancy_category`(`svc_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `site_vacancy` ADD CONSTRAINT `site_vacancy_sv_svt_id_fkey` FOREIGN KEY (`sv_svt_id`) REFERENCES `site_vacancy_type`(`svt_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `site_vacancy` ADD CONSTRAINT `site_vacancy_sv_sl_id_fkey` FOREIGN KEY (`sv_sl_id`) REFERENCES `site_location`(`sl_id`) ON DELETE RESTRICT ON UPDATE CASCADE;