DROP TABLE IF EXISTS ais_companies;
CREATE TABLE ais_companies (
	c_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	c_number CHAR(7) NOT NULL,
	c_role TINYINT UNSIGNED NOT NULL,
	c_name VARCHAR(255) NOT NULL,
	c_address TEXT,
	c_time_created DATETIME NOT NULL,
	PRIMARY KEY(c_id)
);

DROP TABLE IF EXISTS ais_vessels;
CREATE TABLE ais_vessels (
	s_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	c_registered_owner INT UNSIGNED NOT NULL,
	c_ship_manager INT UNSIGNED NOT NULL,
	c_ism_manager INT UNSIGNED NOT NULL,
	s_name VARCHAR(20) NOT NULL,
	s_mmsi CHAR(9) NOT NULL,
	s_imo CHAR(7) NOT NULL,
	s_callsign VARCHAR(255) NOT NULL,
	s_length DECIMAL(5,1),
	s_width DECIMAL(5,1),
	s_draft DECIMAL(5,1),
	s_equasis_type VARCHAR(255),
	s_time_created DATETIME NOT NULL,
	PRIMARY KEY(s_id)
);
 
DROP TABLE IF EXISTS ais_areas;
CREATE TABLE ais_areas (
	a_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	a_type VARCHAR(255) NOT NULL,
	a_name VARCHAR(255) NOT NULL,
	a_points TEXT NOT NULL,
	a_center VARCHAR(255) NOT NULL,
	a_image TEXT,
	a_url TEXT,
	a_time_created DATETIME NOT NULL,
	PRIMARY KEY(a_id)
);

INSERT INTO ais_areas(a_type, a_name, a_points, a_center) values(0, 'test1', '22.240,-97.800|22.500,-97.800|22.500,-97.200|22.200,-97.200', '22.300,-97.700'), (0, 'test2', '22.840,-97.800|22.500,-97.800|22.500,-97.200|22.600,-97.200', '22.700,-97.700');

DROP TABLE IF EXISTS ais_plain_dat;
CREATE TABLE ais_plain_dat (
 row TEXT NOT NULL
);


DROP TABLE IF EXISTS ais_events;
CREATE TABLE ais_events (
	e_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        s_id INT UNSIGNED NOT NULL,
	a_id INT UNSIGNED NOT NULL,
	e_enter_lat TEXT,
	e_enter_lon TEXT,
	e_enter_dx TEXT,
	e_enter_dy TEXT,
	e_enter_type TINYINT UNSIGNED ,
	e_enter_destination VARCHAR(20),
	e_enter_eta VARCHAR(255),
	e_enter_speed TEXT,
	e_enter_course TEXT,
	e_enter_status TINYINT UNSIGNED ,
	e_enter_class VARCHAR(255),
	e_exit_lat TEXT,
	e_exit_lon TEXT,
	e_exit_dx TEXT,
	e_exit_dy TEXT,
	e_exit_type TINYINT UNSIGNED ,
	e_exit_destination VARCHAR(20),
	e_exit_eta VARCHAR(255),
	e_exit_speed TEXT,
	e_exit_course TEXT,
	e_exit_status TINYINT UNSIGNED ,
	e_exit_class VARCHAR(255),
	e_time_enter DATETIME NOT NULL,
	e_time_exit DATETIME,
  PRIMARY KEY(e_id)
);