-- Membuat Table Baru Peternakan
CREATE TABLE peternakan (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    broadcast_active SMALLINT NOT NULL DEFAULT 0,
    location_address VARCHAR(255),
    location_latitude VARCHAR(100),
    location_longitude VARCHAR(100),
    location_marker TEXT,
    location_distance INT(11),
    created_by VARCHAR(100),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(100),
    updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Update Tabel Users Menambahkan Kolom Register
ALTER TABLE users
ADD COLUMN register_step INT(11) DEFAULT 0 NOT NULL COMMENT '0=EmptyUser, 1=Identity, 2=Verify, 3=Farm' AFTER access_id;

-- Update Tabel Users Menambahkan Kolom Pertenakan_id
ALTER TABLE users
ADD COLUMN peternakan_id INT(11) NOT NULL AFTER access_id;

-- Update Tabel Users Menambahkan Kolom verified default 1
ALTER TABLE users 
ADD COLUMN verified SMALLINT(6) NOT NULL DEFAULT 0 AFTER image ;

-- Update Tabel Users Edit access_id dijadiin nullable
ALTER TABLE users 
CHANGE access_id access_id SMALLINT(6) NULL DEFAULT NULL;

-- Tambah kolom peternakan_id di semua master
ALTER TABLE master_kandang ADD COLUMN peternakan_id INT AFTER id;
ALTER TABLE master_ternak ADD COLUMN peternakan_id INT AFTER id;

-- Set Semua User yg sekarang aktif
UPDATE users SET register_step = 3;
UPDATE users SET verified = 1;

-- Tambah data Super Admin / Pemilik
INSERT INTO access (name)
VALUES ('Pemilik');

-- Masukan peternakan default
INSERT INTO peternakan(name)
VALUES ('Peternakan bunikasih');

-- Set master peternakan_id untuk default peternakan
UPDATE users SET peternakan_id = 1;
UPDATE master_ternak SET peternakan_id = 1;
UPDATE master_kandang SET peternakan_id = 1;