-- Your SQL goes here

CREATE TABLE unit
(
    id                   INT            NOT NULL,
    name                 VARCHAR(100)   NOT NULL,
    base_id              VARCHAR(100)   NOT NULL,
    activate_shard_count INT DEFAULT 50 NOT NULL,
    CONSTRAINT unit_pk
        PRIMARY KEY (id)
);

CREATE UNIQUE INDEX unit_base_id_uindex
    ON unit (base_id);

INSERT INTO unit (id, name, base_id, activate_shard_count)
VALUES (23, 'Asajj Ventress', 'ASAJVENTRESS', 50),
       (80, 'Aayla Secura', 'AAYLASECURA', 50),
       (82, 'Darth Maul', 'MAUL', 80),
       (200, 'Darth Vader', 'VADER', 80),
       (305, 'Bastila Shan (Fallen)', 'BASTILASHANDARK', 25),
       (307, 'Canderous Ordo', 'CANDEROUSORDO', 25)
;

# Table for storing farm of user
CREATE TABLE user_farm
(
    id         INT AUTO_INCREMENT,
    unit_id    INT               NOT NULL,
    user_id    INT               NOT NULL,
    gears_goal SMALLINT UNSIGNED NULL,
    stars_goal SMALLINT UNSIGNED NULL,
    CONSTRAINT user_farm_pk
        PRIMARY KEY (id),
    CONSTRAINT user_farm_unit_id_fk
        FOREIGN KEY (unit_id) REFERENCES unit (id),
    CONSTRAINT user_farm_user_id_fk
        FOREIGN KEY (user_id) REFERENCES user (id)
);

CREATE UNIQUE INDEX user_farm_user_id_unit_id_uindex
    ON user_farm (user_id, unit_id);


# Table for storing detail of farming
CREATE TABLE farm_entry
(
    id             INT AUTO_INCREMENT,
    user_farm_id   INT                         NOT NULL,
    comments       VARCHAR(255)                NULL,
    current_shards SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
    current_stars  SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
    date           DATETIME                    NOT NULL,
    CONSTRAINT farm_entry_pk
        PRIMARY KEY (id),
    CONSTRAINT farm_entry_user_farm_id_fk
        FOREIGN KEY (user_farm_id) REFERENCES user_farm (id)
);