-- UNSIGNED means the column cannot be negative; only zero and positive numbers
-- AUTO_INCREMENT automatically assigns a unique value to each new row; typically used for primary keys
-- Column constraint appears after the column name and data type in a CREATE TABLE statement. Govern values in a single column 
-- NOT NULL is a column constraint 

CREATE TABLE address ( 
    address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    address VARCHAR(12) NOT NULL, 
    address2 VARCHAR(12) DEFAULT NULL,  
    district VARCHAR(20) NOT NULL,      
    city_id SMALLINT UNSIGNED NOT NULL, 
    postal_code VARCHAR(10) DEFAULT NULL, 
    phone VARCHAR(20) NOT NULL, 
    location GEOMETRY NOT NULL,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (address_id)
);


CREATE TABLE customer ( 
    customer_id smallint unsigned NOT NULL AUTO_INCREMENT, 
    store_id tinyint unsigned NOT NULL, 
    first_name VARCHAR(45) NOT NULL, 
    last_name VARCHAR (45) NOT NULL, 
    email VARCHAR(50) NOT NULL, 
    address_id smallint unsigned NOT NULL, 
    active tinyint(1) NOT NULL DEFAULT '1', 
    create_date datetime NOT NULL, 
    last_update timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (customer_id)
    ); 

CREATE TABLE staff ( 
    staff_id tinyint unsigned NOT NULL AUTO_INCREMENT, 
    first_name VARCHAR(45) NOT NULL, 
    last_name VARCHAR(45) NOT NULL, 
    address_id smallint unsigned NOT NULL, 
    picture blob, 
    email VARCHAR(50) NOT NULL, 
    store_id tinyint unsigned NOT NULL,
    active tinyint(1) NOT NULL DEFAULT '1',
    username VARCHAR(16) NOT NULL, 
    password VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, 
    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (staff_id)
    ); 

CREATE TABLE store ( 
    store_id tinyint unsigned NOT NULL AUTO_INCREMENT,
    manager_staff_id tinyint unsigned NOT NULL,  
    address_id smallint unsigned NOT NULL, 
    last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (store_id)
    );

ALTER TABLE address DROP COLUMN phone;

CREATE TABLE phone( 
    phone_id INT UNSIGNED PRIMARY KEY,
    country_code INT UNSIGNED NOT NULL, 
    phone_number INT UNSIGNED NOT NULL, 
    phone_type VARCHAR(12) NOT NULL
);


ALTER TABLE customer 
ADD COLUMN phone_id INT UNSIGNED, 
ADD CONSTRAINT fk_customer_phone 
    FOREIGN KEY (phone_id) REFERENCES phone(phone_id)
    ON DELETE SET NULL 
    ON UPDATE CASCADE; 

ALTER TABLE customer 
ADD CONSTRAINT unique_customer_phone_id UNIQUE(phone_id); 

-- Update store table 
ALTER TABLE store 
ADD COLUMN phone_id INT UNSIGNED, 
ADD CONSTRAINT fk_store_phone 
    FOREIGN KEY (phone_id) REFERENCES phone(phone_id)
    ON DELETE SET NULL 
    ON UPDATE CASCADE; 

ALTER TABLE store 
ADD CONSTRAINT unique_store_phone_id UNIQUE (phone_id); 

ALTER TABLE staff 
ADD COLUMN phone_id INT UNSIGNED, 
ADD CONSTRAINT fk_staff_phone 
    FOREIGN KEY(phone_id) REFERENCES phone(phone_id)
    ON DELETE SET NULL 
    ON UPDATE CASCADE; 

ALTER TABLE staff 
ADD CONSTRAINT unique_staff_phone_id UNIQUE(phone_id);