SQL

CREATE TABLE IF NOT EXISTS `job_shops` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shop_id` INT(11) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `job` VARCHAR(50) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `balance` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Shop balance',
    `max_purchase_person` INT(11) DEFAULT NULL COMMENT 'Max items per person per 24h, 0 = unlimited',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `job_shop_items` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shop_id` INT(11) NOT NULL,
    `item_name` VARCHAR(100) NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `price` DECIMAL(10,2) NOT NULL,
    `stock` INT(11) DEFAULT -1 COMMENT '-1 means unlimited stock',
    `max_stock` INT(11) DEFAULT -1,
    `image` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_shop_item` (`shop_id`, `item_name`),
    KEY `shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `job_shop_purchases` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `shop_id` INT(11) NOT NULL,
    `citizenid` VARCHAR(50) NOT NULL,
    `item_name` VARCHAR(100) NOT NULL,
    `quantity` INT(11) NOT NULL,
    `total_price` DECIMAL(10,2) NOT NULL,
    `purchase_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `shop_citizen_item` (`shop_id`, `citizenid`, `item_name`, `purchase_date`),
    KEY `citizenid` (`citizenid`),
    KEY `shop_id` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;