-- --------------------------------------------------------
 
--
-- Table structure for table ci_sessions
--
 
CREATE TABLE ci_sessions (
  session_id VARCHAR(40)  NOT NULL DEFAULT '0',
  ip_address VARCHAR(16)  NOT NULL DEFAULT '0',
  user_agent VARCHAR(150)  NOT NULL,
  last_activity INTEGER NOT NULL DEFAULT '0',
  user_data text  NOT NULL,
  PRIMARY KEY (session_id)
);
 
-- --------------------------------------------------------
 
--
-- Table structure for table login_attempts
--
 
CREATE TABLE login_attempts (
  id serial,
  ip_address VARCHAR(40)  NOT NULL,
  login VARCHAR(50)  NOT NULL,
  TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- --------------------------------------------------------
 
--
-- Table structure for table user_autologin
--
 
CREATE TABLE user_autologin (
  key_id CHAR(32)  NOT NULL,
  user_id INTEGER NOT NULL DEFAULT '0',
  user_agent VARCHAR(150)  NOT NULL,
  last_ip VARCHAR(40)  NOT NULL,
  last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (key_id,user_id)
);
 
-- --------------------------------------------------------
 
--
-- Table structure for table user_profiles
--
 
CREATE TABLE user_profiles (
  id serial,
  user_id INTEGER NOT NULL,
  country VARCHAR(20)  DEFAULT NULL,
  website VARCHAR(255)  DEFAULT NULL
);
 
-- --------------------------------------------------------
 
--
-- Table structure for table users
--
 
CREATE TABLE users (
  id serial,
  username VARCHAR(50)  NOT NULL,
  password VARCHAR(255)  NOT NULL,
  email VARCHAR(100)  NOT NULL,
  activated SMALLINT NOT NULL DEFAULT '1',
  banned SMALLINT NOT NULL DEFAULT '0',
  ban_reason VARCHAR(255)  DEFAULT NULL,
  new_password_key VARCHAR(50)  DEFAULT NULL,
  new_password_requested TIMESTAMP DEFAULT NULL,
  new_email VARCHAR(100)  DEFAULT NULL,
  new_email_key VARCHAR(50)  DEFAULT NULL,
  last_ip VARCHAR(40)  NOT NULL,
  last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- --------------------------------------------------------
 
--
-- Update datetime columns on update
--
 
CREATE OR REPLACE FUNCTION update_modified_column_time()
	RETURNS TRIGGER AS $$
	BEGIN
	   NEW.time = now(); 
	   RETURN NEW;
	END;
	$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER update_login_attempts_time BEFORE UPDATE
	ON login_attempts FOR EACH ROW EXECUTE PROCEDURE 
	update_modified_column_time();
 
CREATE OR REPLACE FUNCTION update_modified_column_user_autologin()
	RETURNS TRIGGER AS $$
	BEGIN
	   NEW.last_login = now(); 
	   RETURN NEW;
	END;
	$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER update_login_attempts_user_autologin BEFORE UPDATE
	ON user_autologin FOR EACH ROW EXECUTE PROCEDURE 
	update_modified_column_user_autologin();
 
 
CREATE OR REPLACE FUNCTION update_modified_column_users()
	RETURNS TRIGGER AS $$
	BEGIN
	   NEW.modified = now(); 
	   RETURN NEW;
	END;
	$$ LANGUAGE 'plpgsql';
 
CREATE TRIGGER update_login_attempts_users BEFORE UPDATE
	ON users FOR EACH ROW EXECUTE PROCEDURE 
	update_modified_column_users();