import {
  BaseModel,
  Defaults,
  dso,
  Field,
  FieldType,
  CharsetType,
  Index,
  IndexType,
  Join,
  Model,
  Where
} from "https://deno.land/x/[email protected]/mod.ts";

// Define a database model
@Model("users")
class UserModel extends BaseModel {
  // Character set for whole table. UTF-8 is default and is not necessary to define.
  charset: CharsetType.utf8;

  // The ! operator is needed for primary key since it's never null 
  @Field({
    type: FieldType.INT,
    primary: true,
    length: 11,
    autoIncrement: true
  })
  id: number;

  @Field({ type: FieldType.STRING, length: 30 })
  name: string;

  @Field({ type: FieldType.STRING, length: 30 })
  password: string;

  // Charset can be explicitly defined for each column.
  @Field({ type: FieldType.STRING, length: 60, charset: CharsetType.utf8mb4 })
  namespace: string;

  // Index can be created [index/unique/spatial/fulltext] for each field
  @Field({ type: FieldType.STRING, length: 30, unique: true })
  email: string;

  // Multi column index can be defined by decorated @Index
  @Index({ type: IndexType.UNIQUE, columns: ["username","namespace"] })
  public myUniqueIndex!: Index;
}

const userModel = dso.define(UserModel);

async function main() {
  // The database must be created before linking
  await dso.connect({
    hostname: "127.0.0.1",
    port: 3306,
    username: "root",
    password: "",
    db: "dbname"
  });

  /*  
    When installing or initializing a database,
    you can use dso.sync to synchronize the database model to the database.
    Use false as the parameter if you want to sync only newly added models,
    Use true as the parameter if you want to sync the whole defined models
    to the database tables.
    
    == WARNING! ==
    Using true as the parameter will reset your whole database! Use with caution.
  */
  await dso.sync(false);

  // You can add records using insert method
  const insertId = await userModel.insert({
    name: "user1",
    password: "password"
  });

  // You can use the Model.findById method to get a record
  const user = await userModel.findById(1);

  // You can use Where clause too to get a record. For more information on
  // clauses such as join, order, etc. please consult:
  // https://github.com/manyuanrong/sql-builder

  // findOne + Where
  const userWhere = await userModel.findOne(Where.from({ name: "user1" }));
  
  // findAll + Where
  const userAll = await userModel.findAll(Where.from({ name: "user1" }));
  
  // findAll + expr like
  const userLike = await userModel.findAll(Where.expr("name like 'user%'"));

  console.log("Found user by id:", user);
  console.log("Found user by where eq clause:", userWhere);
  console.log("All users by where eq clause:", userAll);
  console.log("All users by where like clause:", userLike);
  console.log("User has these columns in index:", user.myUniqueIndex.columns);
}
main();

dso.showQueryLog = true;

await dso.connect({
  hostname: "127.0.0.1", // database hostname
  port: 3306, // database port
  username: "root", // database username
  password: "", // database password
  db: "dbname" // database name. (tips: The database must be created before linking)
});

@Model("users")
class UserModel extends BaseModel {
  @Field({
    type: FieldType.INT,
    primary: true,
    length: 11,
    autoIncrement: true
  })
  id!: number;

  @Field({ type: FieldType.STRING, length: 30 })
  name?: string;

  @Field({ type: FieldType.STRING, length: 30 })
  password: string;
  
  @Field({ 
    type: FieldType.STRING, 
    unique: true, 
    length: 20})
  phoneNumber?: string;
}
}

export default const userModel = dso.define(UserModel);

// userModel.findById(...)
// userModel.findAll(...)
// userModel.findOne(...)
// userModel.insert(...)
// userModel.update(...)
// userModel.delete(...)

// If set to FORCE, tables will be deleted before they are created,
// otherwise only new models will be synchronized.
const force = true; // force
await dso.sync(force);

const result = await dso.transaction<boolean>(async trans => {
  const userModel = trans.getModel(UserModel);
  const topicModel = trans.getModel(TopicModel);

  userId = await userModel.insert({ nickName: "foo", password: "bar", phoneNumber: "08135539123" });
  topicId = await topicModel.insert({ title: "zoo", userId });
  return true;
});